Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 9th, 2010, 05:29 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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>
  #2 (permalink)  
Old April 10th, 2010, 10:30 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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:

vb Code:
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."
===============================================
  #3 (permalink)  
Old April 10th, 2010, 03:05 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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 
  #4 (permalink)  
Old April 10th, 2010, 03:37 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Yes, it does exist, in the second table of your dataset. See what happens is that when you do this:

vb Code:
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."
===============================================
  #5 (permalink)  
Old April 10th, 2010, 03:53 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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
  #6 (permalink)  
Old April 10th, 2010, 04:12 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
===============================================
  #7 (permalink)  
Old April 10th, 2010, 04:15 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
gridview binding vamshidhar ASP.NET 2.0 Professional 1 August 25th, 2009 09:52 AM
Binding a menu with dataset zaghmout ASP.NET 2.0 Professional 1 August 14th, 2007 05:24 AM
Binding a DataSet to Cystal Report snufse Crystal Reports 0 March 30th, 2007 07:06 AM
binding the grid to dataset?? thas123 ASP.NET 2.0 Basics 1 December 27th, 2006 02:15 PM
Binding a Dataset to a Report ChrisLane Crystal Reports 4 March 9th, 2004 02:04 AM



All times are GMT -4. The time now is 01:56 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.