can anyone answer this for me?

[Joe Van Meer]

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>



<aspanel ID="bookingspanel" runat="server">


<aspataList 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>

</aspataList>





<!--- END OF BOOKINGS PANEL --->
</aspanel>

 

[David Jessee]

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).