 |
| ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
April 9th, 2010, 05:29 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
Field not found when binding dataset to gridview
I get the eror message: A field or property with the name 'BuyersAgent' was not found on the selected data source. Any help or insight is appreciated.1. If I remove the Bound Column BuyersAgent the page runs without error.2. If I breakpoint at: BuyersAgent = DirectCast(dataSet.Tables(1).Rows(0).ItemArray(1), String), BuyersAgent does have the correct value.3. BuyersAgent is unique in that it is not in the undertlaying db Table but comes from the following storedprocedure snippet:SELECT A.FName +
P1.FName + ' ' + P1.LName AS [Purchaser1],' ' + A.LName AS [BuyersAgent]
FROM ...
Code:
<%@ Page Title="" Language="VB" MasterPageFile="~/secureds_deals.master" %><%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %><script runat="server"> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) If Not IsPostBack Then 'Session("DealID") was passed in Dim Conn As New SqlConnection(dsNegotiationGrid.ConnectionString) Dim dataSet As New DataSet() Dim adapter As SqlDataAdapter Dim CmdGeNegotiationData As New SqlCommand("procGetNegotiationsByIDSelect", Conn) Dim dealidparam As New SqlParameter("@dealid", SqlDbType.Int) Dim DealId As Integer = 0 Dim DealName As String Dim StartDate As Date Dim ContractDate As Date Dim ClosingDate As Date Dim SubmitRole As String Dim Status As String Dim OfferNumber As Integer Dim OfferType As String Dim OfferAmount As String Dim OfferDateTime As Date Dim PctDown As Integer Dim SuggestedClosingDate As Date Dim CompletePct As Integer Dim Guarantor As String Dim Contingencies As String Dim Notes As String Dim BuyersAgent As String Dim MostRecent As Boolean Dim OfferId As Integer = 0 Dim Purchaser1 As String Dim Seller1, DealShortName As String Dim StreetAddress1, StreetAddress2, City, State, Country, PostalCode As String If Session("DealID") <= 0 Or Session("DealName") = "" Then Server.Transfer("Deals.aspx") CmdGeNegotiationData.CommandType = Data.CommandType.StoredProcedure dealidparam.Value = CInt(Session("DealID")) CmdGeNegotiationData.Parameters.Add(dealidparam) dealidparam.Direction = Data.ParameterDirection.Input Conn.Open() adapter = New SqlDataAdapter(CmdGeNegotiationData) adapter.Fill(dataSet) DealId = CInt(dataSet.Tables(0).Rows(0).ItemArray(0)) DealName = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(1), String) StartDate = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(2), Date) ContractDate = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(3), Date) ClosingDate = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(4), Date) SubmitRole = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(5), String) Status = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(6), String) OfferNumber = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(7), Int16) OfferType = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(8), String) OfferDateTime = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(9), Date) PctDown = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(10), Int16) OfferAmount = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(11), Int32) SuggestedClosingDate = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(12), String) CompletePct = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(13), Int16) Guarantor = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(14), String) Contingencies = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(15), String) Notes = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(16), String) DealShortName = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(17), String) MostRecent = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(18), Boolean) OfferId = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(19), Int32) StreetAddress1 = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(20), String) StreetAddress2 = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(21), String) City = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(22), String) State = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(23), String) Country = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(24), String) PostalCode = DirectCast(dataSet.Tables(0).Rows(0).ItemArray(25), String) Purchaser1 = DirectCast(dataSet.Tables(1).Rows(0).ItemArray(0), String) BuyersAgent = DirectCast(dataSet.Tables(1).Rows(0).ItemArray(1), String) Seller1 = DirectCast(dataSet.Tables(2).Rows(0).ItemArray(0), String) lblDealID.Text = "Deal ID: " & DealId lblDealName.Text = "Seller: " & DealName gvNegotiationGrid.DataSource = dataSet gvNegotiationGrid.DataBind() End If End Sub Sub gvNegotiationGrid_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) 'Need Offer ID in grid to pass to next page in session variable Dim rowindex As Integer RowIndex = Convert.ToInt32(e.CommandArgument).ToString() If e.CommandName.Equals("Detail") Then Server.Transfer("deal.aspx") End If If e.CommandName="Respond" Then Server.Transfer("respond.aspx") End If End Sub Protected Sub gvNegotiationGrid_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) If e.Row.RowType = DataControlRowType.DataRow Then Dim btnDetail As Button = DirectCast(e.Row.FindControl("btnDetail"), Button) Dim btnRespond As Button = DirectCast(e.Row.FindControl("btnRespond"), Button) Dim ShowLink As Boolean = _ CType(DataBinder.Eval(e.Row.DataItem, "mostrecent"), Boolean) ' add to db If ShowLink=False Then btnDetail.Visible = "False" btnRespond.Visible="False" End If End If End Sub </script><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server" ><asp:Label ID="lblDealID" runat="server" class="label" Text=""></asp:Label><br /><asp:Label ID="lblDealName" runat="server" class="label" Text=""></asp:Label> <div style="margin-top:25px;" > <asp:Button ID="btnAddOffer" runat="server" Text="Add Offer" /> </div> <div > <asp:GridView ID="gvNegotiationGrid" runat="server" HorizontalAlign="Center" CellPadding="4" GridLines="None" Font-Names="Verdana,Arial" Font-Size="0.8em" AutoGenerateColumns="False" AutoGenerateSelectButton="false" OnRowCommand="gvNegotiationGrid_RowCommand" OnRowDataBound="gvNegotiationGrid_RowDataBound" ForeColor="#333333" > <Columns> <asp:BoundField DataField="offertype" HeaderText="Type"/> <asp:BoundField DataField="offernumber" HeaderText="#"/> <asp:BoundField DataField="status" HeaderText="Status"/> <asp:BoundField DataField="dealname" HeaderText="Buyer/Seller"/> <asp:BoundField HtmlEncode="false" DataFormatString="{0: $##,#}" DataField="offeramount" HeaderText="Offer Amount"/> <asp:BoundField DataField="pctdown" HeaderText="% Down"/> <asp:BoundField DataField="SuggestedClosingDate" HeaderText="Suggested Close"/> <asp:BoundField DataField="Guarantor" HeaderText="Guarantor"/> <asp:BoundField DataField="Notes" HeaderText="Notes" /> <asp:BoundField HtmlEncode="false" DataFormatString="{0:g}" DataField="offerdatetime" HeaderText="Made On"/> <asp:BoundField DataField="SubmitRole" HeaderText="Role"/> <asp:BoundField DataField="BuyersAgent" HeaderText="Agent"/> <asp:BoundField DataField="CompletePct" HeaderText="Complete %"/> <asp:BoundField DataField="mostrecent" HeaderText="" Visible="false"/> <asp:TemplateField HeaderText="More"> <ItemTemplate> <asp:Button ID="btnDetail" CommandName="Detail" CommandArgument="<%# CType(Container, GridViewRow).RowIndex %>" runat="server" Text="Detail" /> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Action"> <ItemTemplate> <asp:Button ID="btnRespond" CommandName="Respond" CommandArgument="<%# CType(Container, GridViewRow).RowIndex %>" runat="server" Text="Respond" /> </ItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" BorderColor="#336699" BorderWidth="1" BorderStyle="Solid"/> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <RowStyle HorizontalAlign="Center" /> <HeaderStyle BorderColor="#336699" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="Small" HorizontalAlign="Center" VerticalAlign="Top" BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BorderColor="#336699" BorderStyle="Solid" BorderWidth="1px" HorizontalAlign="Center" BackColor="White" ForeColor="#284775" /> </asp:GridView> <asp:SqlDataSource ID="dsNegotiationGrid" runat="server" ConnectionString="<%$ ConnectionStrings:DB_6056_securedeConnectionString %>"> </asp:SqlDataSource> </div> </asp:Content>
|
|
April 10th, 2010, 10:30 AM
|
|
Wrox Author
|
|
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
|
|
Hi SG,
Boo datasets >< Anyway the reason you are getting the error is because BuyersAgent DOESN'T exist...in Table 1 of your dataset as evident by this line:
BuyersAgent = DirectCast(dataSet.Tables(1).Rows(0).ItemArray(1), String)
And that is the problem, you are binding your gridview to Table1 although implictly.
Now your code is going to get messy on the UI side trying to bind data from two tables and off the top of my head I am not sure how you would do it but this post should set you in the right direction: http://forums.asp.net/t/1087827.aspx
IMHO I think you probably get this down to a single table by doing some sort of a JOIN, of course I am just making the assumption since I have no idea what you sproc looks like or your data model for that matter.
hth
-Doug
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click  on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
|
|
April 10th, 2010, 03:05 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
Not sure I agree
Thanks for your response.
When I watch BuyersAgent it has the correct value, so it seems that it does exist.
Here is my stored procedure below
What I am trying to accomplish is the fo0llowing:
I have a tblPerson with all the person data.
I have several other tables that are involved in the stored procedure. In the tblOffer I have several 'keys' that are pointers to the tblPerson,- ie BuyersAgentPersonKey, Purchaser1PersonKey.
I am trying not to put the person data into the tblOffer directly, only the keys.
Then I want the stored procedure to stuff all of the data including the person data into the GridView.
Code:
ALTER PROCEDURE dbo.procGetNegotiationsByIDSelect
/*
don't know how to handle multiple datatables
*/
(
@dealid int = '1'
)
AS
SELECT
tblDeal.DealID,
tblDeal.DealName,
tblDeal.StartDate,
tblDeal.ContractDate,
tblDeal.ClosingDate,
tblOffer.SubmitRole,
tblOffer.Status,
tblOffer.OfferNumber,
tblOffer.OfferType,
tblOffer.OfferDateTime, tblOffer.PctDown, tblOffer.OfferAmount,
tblOffer.SuggestedClosingDate,
tblOffer.CompletePct, tblOffer.Guarantor,
tblOffer.Contingencies, tblOffer.Notes,
tblDeal.DealShortName,
tblOffer.mostrecent, tblOffer.OfferID,
tblProperty.StreetAddress1,
tblProperty.StreetAddress2, tblProperty.City, tblProperty.State,
tblProperty.Country, tblProperty.PostalCode
FROM tblDeal INNER JOIN
tblNegotiation ON tblDeal.DealID = tblNegotiation.DealKey INNER JOIN
tblOffer ON tblDeal.DealID = tblOffer.DealKey INNER JOIN
tblProperty ON tblDeal.PropertyKey = tblProperty.PropertyID
WHERE (tblDeal.DealID = @dealid)
SELECT P1.FName + ' ' + P1.LName AS [Purchaser1],
A.FName + ' ' + A.LName AS [BuyersAgent]
FROM tblOffer AS OT INNER JOIN
tblPerson as P1 ON P1.PersonID = OT.Purchaser1PersonKey INNER JOIN
tblPerson AS A ON A.PersonID = OT.BuyersAgentPersonKey
WHERE OT.DealKey=@dealid
|
|
April 10th, 2010, 03:37 PM
|
|
Wrox Author
|
|
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
|
|
Yes, it does exist, in the second table of your dataset. See what happens is that when you do this:
GridView1.DataSource = dataset
GridView1.DataBind()
Your GridView is being bound to the first table in your dataset however the ByersAgent column is in the SECOND table of your dataset. Now the reason you have two tables in your dataset is because your sproc is doing two select statements and therefore returning two resultsets.
I think the easiest solution here would be to modify your SQL statement to be something like this:
sql Code:
SELECT
d.DealID,
d.DealName,
d.StartDate,
d.ContractDate,
d.ClosingDate,
d.DealShortName,
o.SubmitRole,
o.STATUS,
o.OfferNumber,
o.OfferType,
o.OfferDateTime,
o.PctDown,
o.OfferAmount,
o.SuggestedClosingDate,
o.CompletePct,
o.Guarantor,
o.Contingencies,
o.Notes,
o.mostrecent,
o.OfferID,
pt.StreetAddress1,
pt.StreetAddress2,
pt.City,
pt.State,
pt.Country,
pt.PostalCode,
p.FName + ' ' + p.LName AS [Purchaser1],
p2.FName + ' ' + p2.LName AS [BuyersAgent]
FROM tblDeal d
INNER JOIN tblOffer o ON d.DealID = o.DealKey
INNER JOIN tblPerson p ON p.PersonID = o.Purchaser1PersonKey
INNER JOIN tblPerson p2 ON p2.PersonID = o.BuyersAgentPersonKey
INNER JOIN tblProperty pt ON d.PropertyKey = pt.PropertyID
WHERE (d.DealID = @dealid)
This will return a single result and, therefore, one table of data.
hth
-Doug
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
|
|
April 10th, 2010, 03:53 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
I think your analysis is right on
I agree with you. The problem is that BuyerAgent is in the 2nd table. I wasn't sure how to bind to both tables although I'm sure it can be done.
Also, I knew that combining my SELECTS into one query would solve the problem but
I wasn't sure how to do it. What you just sent looks excellent. I will try it (it may take a while because I have gone past this point and want to make sure I don't mess everything up. Thank you for your insight. Your explanation was extremely clear and helpful. Thank you. - Steve
|
|
April 10th, 2010, 04:12 PM
|
|
Wrox Author
|
|
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
|
|
You are quite welcome. If you have any more questions please let me know!
-Doug
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
|
|
April 10th, 2010, 04:15 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
Your stored procedure
I tried it exactly as you wrote it and it worked without any changes. Thank you.
I wanted to do it this way because I did not want to put person data into the offer table.
|
|
|
