can anyone answer this for me?

Discussion in 'ASP .Net Web Controls' started by Joe Van Meer, May 7, 2004.

  1. Joe Van Meer

    Joe Van Meer Guest

    Hi all,

    Coming from ASP background to .NET

    I am trying to get my datalist to display information from # different
    tables; BOOKING, EMPLOYEE and CLIENT

    I am having problems binding to the second table's fields in my
    ItemTemplate.

    Is this at all possible, or should I be using a datagrid?


    At first I had 2 queries grabbing the data, filled a dataset with these and
    created realtions between the tables. Then had prblems with syntax of
    displaying the data from the second table next to the data from the first.


    The latest attempt is below: I now have one query with aliases. And when I
    got to fill my dataset it is now giving me an error. Code is below:


    Cheers, Joe



    <%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1"
    debug="true" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SQLClient" %>


    <script language="vb" runat="server">

    SUB Page_Load(Sender As Object, E As EventArgs)



    IF NOT Page.IsPostBack THEN
    getdata()
    END IF

    END SUB



    SUB getdata()


    Dim curAgentID as Integer
    Dim curAgencyID as Integer
    Dim curAgencyName as String
    Dim curAgentName as String
    Dim PageTitle as String
    Dim curSecLevel as String
    Dim curGroupID as Integer
    Dim curStatus as String
    Dim strSQL as String


    curAgentID = Session("sesempId")
    curAgencyID = Session("sesempAgencyid")
    curAgencyName = Session("sesempAgencyname")
    PageTitle = "Agency Bookings"
    curSecLevel = Session("sesempSecurity")
    curGroupID = Session("sesempGroupid")



    Dim ds as DataSet = New DataSet()

    'QUERY
    strSQL = "b.bookingid, b.arn, b.bookingdatetimestamp, b.status, c.fullname,
    e.fname, e.lname FROM BOOKING b, EMPLOYEE e, CLIENT c " & _
    "WHERE e.empid = b.empid " & _
    "AND c.clientid = b.clientid " & _
    "AND e.empid =" & curAgentID & " " & _
    "AND e.agencyid =" & curAgencyID & " " & _
    "AND b.status = 'Draft'"


    'FILL BOOKINGS
    Dim MyConn as New SQLConnection(ConfigurationSettings.AppSettings("dbConn"))
    Dim myCmd as New SqlDataAdapter(strSQL, MyConn)


    myCmd.fill(ds,"BOOKING") ************* ERROR HAPPENS HERE WHAT AM I DOING
    WRONG? error (Incorrect syntax near ','.)
    myCmd.fill(ds,"EMPLOYEE")
    myCmd.fill(ds,"CLIENT")


    bookingspanel.visible = true
    nobookingspanel.visible = false


    'BIND DATA TO DATALIST
    dlBookings.DataSource = ds
    dlBookings.DataBind()
    myConn.close


    mylabel.Text = ds.Tables(0).Rows.Count
    mylabel.visible = true


    END SUB

    </script>



    <asp:panel ID="bookingspanel" runat="server">


    <asp:DataList id="dlBookings"
    runat="server"
    cellpadding="3"
    cellspacing="3"
    GridLines="Both"
    borderstyle="none"
    backcolor="#FFFFFF"
    width="790px"
    headerstyle-font-name="Verdana"
    headerstyle-font-size="13pt"
    headerstyle-horizontalalign="left"
    headerstyle-font-bold="TRUE"
    itemstyle-backcolor="#FFFFFF"
    itemstyle-forecolor="#000000"
    alternatingitemstyle-backcolor="#C6EFF7"
    alternatingitemstyle-forecolor="#FFFFFF"
    footerstyle-font-size="9pt"
    footerstyle-font-italic="true">



    <HeaderTemplate>
    <tr valign="top" bgcolor="#CCCCCC">
    <td align="left" class="textbox">Agent Reference Number</td>
    <td align="left" class="textbox">Status</td>
    <td align="left" class="textbox">Booking Date</td>
    <td align="left" class="textbox">Agent</td>
    <td align="left" class="textbox">Client</td>
    </tr>
    </HeaderTemplate>

    <ItemTemplate>
    <tr valign="top">
    <td align="left" class="textbox"><%#Container.DataItem("arn")%></td>
    <td align="left" class="textbox"><%#Container.DataItem("status")%></td>
    <td align="left"
    class="textbox"><%#Container.DataItem("bookingdatetimestamp")%></td>
    <td align="left" class="textbox"><%#Container.DataItem("SECOND TABLE DATA
    COLUMN")%></td>
    <td align="left" class="textbox"><%#Container.DataItem("SECOND TABLE DATA
    COLUMN")%></td>
    </tr>
    </ItemTemplate>
    <AlternatingItemTemplate>
    <tr valign="top">
    <td align="left" class="textbox"><%#Container.DataItem("arn")%></td>
    <td align="left" class="textbox"><%#Container.DataItem("status")%></td>
    <td align="left"
    class="textbox"><%#Container.DataItem("bookingdatetimestamp")%></td>
    <td align="left" class="textbox"><%#Container.DataItem("SECOND TABLE DATA
    COLUMN")%></td>
    <td align="left" class="textbox"><%#Container.DataItem("SECOND TABLE DATA
    COLUMN")%></td>
    </tr>
    </AlternatingItemTemplate>

    <FooterTemplate>
    <tr height="25">
    <td align="left" colspan="5"></td>
    </tr>
    <tr valign="top">
    <td align="left" colspan="5" class="textbox</td>
    </tr>
    </table>
    </FooterTemplate>

    </asp:DataList>





    <!--- END OF BOOKINGS PANEL --->
    </asp:panel>
     
    Joe Van Meer, May 7, 2004
    #1
    1. Advertising

  2. Joe Van Meer

    David Jessee Guest

    The Fill Method has a nuumber of overloads. You're using the one that takes
    a Dataset and a Table Name...which assumes there a table with that name,
    which there isn't.

    But more importantly, remember there is a 1-to-1 relationship between a
    datatable and a dataadapter. IF this was working as written, then your
    dataset would end up containing 3 DataTables, Booking, Employee and Client.
    Each of those datatables would have 7 fields (bookingid, arn,
    bookingdatetimestamp, status, fullname, fname, lname).


    "Joe Van Meer" <> wrote in message
    news:...
    > Hi all,
    >
    > Coming from ASP background to .NET
    >
    > I am trying to get my datalist to display information from # different
    > tables; BOOKING, EMPLOYEE and CLIENT
    >
    > I am having problems binding to the second table's fields in my
    > ItemTemplate.
    >
    > Is this at all possible, or should I be using a datagrid?
    >
    >
    > At first I had 2 queries grabbing the data, filled a dataset with these

    and
    > created realtions between the tables. Then had prblems with syntax of
    > displaying the data from the second table next to the data from the first.
    >
    >
    > The latest attempt is below: I now have one query with aliases. And when I
    > got to fill my dataset it is now giving me an error. Code is below:
    >
    >
    > Cheers, Joe
    >
    >
    >
    > <%@ Page Language="VB" ContentType="text/html"

    ResponseEncoding="iso-8859-1"
    > debug="true" %>
    > <%@ Import Namespace="System.Data" %>
    > <%@ Import Namespace="System.Data.SQLClient" %>
    >
    >
    > <script language="vb" runat="server">
    >
    > SUB Page_Load(Sender As Object, E As EventArgs)
    >
    >
    >
    > IF NOT Page.IsPostBack THEN
    > getdata()
    > END IF
    >
    > END SUB
    >
    >
    >
    > SUB getdata()
    >
    >
    > Dim curAgentID as Integer
    > Dim curAgencyID as Integer
    > Dim curAgencyName as String
    > Dim curAgentName as String
    > Dim PageTitle as String
    > Dim curSecLevel as String
    > Dim curGroupID as Integer
    > Dim curStatus as String
    > Dim strSQL as String
    >
    >
    > curAgentID = Session("sesempId")
    > curAgencyID = Session("sesempAgencyid")
    > curAgencyName = Session("sesempAgencyname")
    > PageTitle = "Agency Bookings"
    > curSecLevel = Session("sesempSecurity")
    > curGroupID = Session("sesempGroupid")
    >
    >
    >
    > Dim ds as DataSet = New DataSet()
    >
    > 'QUERY
    > strSQL = "b.bookingid, b.arn, b.bookingdatetimestamp, b.status,

    c.fullname,
    > e.fname, e.lname FROM BOOKING b, EMPLOYEE e, CLIENT c " & _
    > "WHERE e.empid = b.empid " & _
    > "AND c.clientid = b.clientid " & _
    > "AND e.empid =" & curAgentID & " " & _
    > "AND e.agencyid =" & curAgencyID & " " & _
    > "AND b.status = 'Draft'"
    >
    >
    > 'FILL BOOKINGS
    > Dim MyConn as New

    SQLConnection(ConfigurationSettings.AppSettings("dbConn"))
    > Dim myCmd as New SqlDataAdapter(strSQL, MyConn)
    >
    >
    > myCmd.fill(ds,"BOOKING") ************* ERROR HAPPENS HERE WHAT AM I

    DOING
    > WRONG? error (Incorrect syntax near ','.)
    > myCmd.fill(ds,"EMPLOYEE")
    > myCmd.fill(ds,"CLIENT")
    >
    >
    > bookingspanel.visible = true
    > nobookingspanel.visible = false
    >
    >
    > 'BIND DATA TO DATALIST
    > dlBookings.DataSource = ds
    > dlBookings.DataBind()
    > myConn.close
    >
    >
    > mylabel.Text = ds.Tables(0).Rows.Count
    > mylabel.visible = true
    >
    >
    > END SUB
    >
    > </script>
    >
    >
    >
    > <asp:panel ID="bookingspanel" runat="server">
    >
    >
    > <asp:DataList id="dlBookings"
    > runat="server"
    > cellpadding="3"
    > cellspacing="3"
    > GridLines="Both"
    > borderstyle="none"
    > backcolor="#FFFFFF"
    > width="790px"
    > headerstyle-font-name="Verdana"
    > headerstyle-font-size="13pt"
    > headerstyle-horizontalalign="left"
    > headerstyle-font-bold="TRUE"
    > itemstyle-backcolor="#FFFFFF"
    > itemstyle-forecolor="#000000"
    > alternatingitemstyle-backcolor="#C6EFF7"
    > alternatingitemstyle-forecolor="#FFFFFF"
    > footerstyle-font-size="9pt"
    > footerstyle-font-italic="true">
    >
    >
    >
    > <HeaderTemplate>
    > <tr valign="top" bgcolor="#CCCCCC">
    > <td align="left" class="textbox">Agent Reference Number</td>
    > <td align="left" class="textbox">Status</td>
    > <td align="left" class="textbox">Booking Date</td>
    > <td align="left" class="textbox">Agent</td>
    > <td align="left" class="textbox">Client</td>
    > </tr>
    > </HeaderTemplate>
    >
    > <ItemTemplate>
    > <tr valign="top">
    > <td align="left" class="textbox"><%#Container.DataItem("arn")%></td>
    > <td align="left" class="textbox"><%#Container.DataItem("status")%></td>
    > <td align="left"
    > class="textbox"><%#Container.DataItem("bookingdatetimestamp")%></td>
    > <td align="left" class="textbox"><%#Container.DataItem("SECOND TABLE DATA
    > COLUMN")%></td>
    > <td align="left" class="textbox"><%#Container.DataItem("SECOND TABLE DATA
    > COLUMN")%></td>
    > </tr>
    > </ItemTemplate>
    > <AlternatingItemTemplate>
    > <tr valign="top">
    > <td align="left" class="textbox"><%#Container.DataItem("arn")%></td>
    > <td align="left" class="textbox"><%#Container.DataItem("status")%></td>
    > <td align="left"
    > class="textbox"><%#Container.DataItem("bookingdatetimestamp")%></td>
    > <td align="left" class="textbox"><%#Container.DataItem("SECOND TABLE DATA
    > COLUMN")%></td>
    > <td align="left" class="textbox"><%#Container.DataItem("SECOND TABLE DATA
    > COLUMN")%></td>
    > </tr>
    > </AlternatingItemTemplate>
    >
    > <FooterTemplate>
    > <tr height="25">
    > <td align="left" colspan="5"></td>
    > </tr>
    > <tr valign="top">
    > <td align="left" colspan="5" class="textbox</td>
    > </tr>
    > </table>
    > </FooterTemplate>
    >
    > </asp:DataList>
    >
    >
    >
    >
    >
    > <!--- END OF BOOKINGS PANEL --->
    > </asp:panel>
    >
    >
    >
    >
    >
    >
    >
     
    David Jessee, May 8, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. PJ
    Replies:
    2
    Views:
    576
    KathyB
    Jul 19, 2003
  2. Mike

    can anyone answer this

    Mike, Jun 21, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    360
    Trevor Benedict R
    Jun 22, 2004
  3. =?Utf-8?B?RGlmZmlkZW50?=

    can anyone answer this tuff question?

    =?Utf-8?B?RGlmZmlkZW50?=, Jan 6, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    384
    Marina
    Jan 6, 2005
  4. Guest
    Replies:
    0
    Views:
    138
    Guest
    Dec 23, 2003
  5. James Radke
    Replies:
    1
    Views:
    150
Loading...

Share This Page