datalist - display info from 2 tables

[Joe Van Meer]

Hi,

Is the datalist meant for one table or can I use it to display information
from 2 different tables? Or should I be looking at a datagrid instead?

I began my code and kinda ran into a snag where my last two columns I am
displaying are ID fields, one for clientid and the other for empid.

What I would really like is to display their actual names instead of the
ids.

I have a dataset that is filled with 3 tables and I have created relations
between CLIENT and BOOKING (clientid) and EMPLOYEE and BOOKING (empid)
tables.

I have bound the dataset to my datalist and it is displaying correctly.

Thank you in advance! Joe

Here's my code:


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 strSQLa as String
Dim strSQLb as String
Dim strSQLc as String
Dim ds as DataSet = New DataSet()

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


'AGENT BOOKINGS QUERY
strSQLa = "SELECT bookingid, clientid, agencyid, empid, arn,
bookingdatetimestamp, status FROM BOOKING WHERE agencyid =" & curAgencyID &
" AND empid=" & curAgentID & " AND status = 'Draft'"
'CLIENT NAME QUERY
strSQLb = "SELECT clientid, fname, lname FROM CLIENT WHERE agencyid=" &
curAgencyID
'EMPLOYEE NAME QUERY
strSQLc = "SELECT empid, fname, lname FROM EMPLOYEE WHERE agencyid =" &
curAgencyID


'FILL BOOKINGS
Dim MyConn as New SQLConnection(ConfigurationSettings.AppSettings("dbConn"))
Dim myCmd as New SqlDataAdapter(strSQLa, MyConn)
myCmd.fill(ds, "BOOKING")

'FILL CLIENTS
Dim myCmdb as New SqlDataAdapter(strSQLb, MyConn)
myCmdb.fill(ds, "CLIENT")

'FILL EMPLOYEES
Dim myCmdc as New SqlDataAdapter(strSQLc, MyConn)
myCmdc.fill(ds, "EMPLOYEE")


'response.write (ds.Tables(0).Rows.Count)
'response.write (curAgencyID)

'IF THERE ARENT ANY CLIENTS WITH BOOKINGS DO NOT DISPLAY !!!! SIMPLE MESSAGE
TO USER HERE STATING NO BOOKINGS IE: PANEL
IF ds.Tables(1).Rows.Count > 0 THEN

'SET UP TABLE RELATIONS HERE
Dim datrela as New DataRelation("ClientBookings",
ds.Tables("CLIENT").Columns("clientid"),
ds.Tables("BOOKING").Columns("clientid"))
'add relation to collection
ds.Relations.Add(datrela)


Dim datrelb as New DataRelation("EmployeeBookings",
ds.Tables("EMPLOYEE").Columns("empid"),
ds.Tables("BOOKING").Columns("empid"))
'add relation to collection
ds.Relations.Add(datrelb)


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

ELSE


'SET LABEL MESSAGE HERE - NO BOOKINGS CURRENTLY, ETC...
mylabel.Text = "0"
mylabel.visible = true
bookingspanel.visible = false
nobookingspanel.visible = true

END IF

END SUB
</script>



<!--- START OF BOOKING PANEL --->
<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">*****</td>
<td align="left" class="textbox">*****</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">*****<td>
<td align="left" class="textbox">*****</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>

 

[Rick Spiewak]

Put code into your itemdatabound event, and in it pick up the id's from your
controls, retrieve the data you want, and substitute it. Or, more
efficiently, construct your SQL query with appropriate joins, etc, to
retrieve the columns you want and just bind to them.

 

[Joe Van Meer]

Hi Rick thanks for replying.

I had one query in the beginning that had table aliases and column aliases
but was told to use the relations collection and create my relationships
that way...so which way should I go? And how do I reference/bind data in
the second table to appear alongside info in the first table in my datalist

<%#Container.DataItem("SECOND TABLE COLUMN ")%>

Thanks, Joe

 

[Rick Spiewak]

Instead of binding declaratively, put code in the itemdatabound event.
Go directly into your tables with whatever querie(s) you need to
retrieve the data you want, and put it directly into the relevant
controls (which should be templated so you can find them).

 

[Lerp]

Thx Rick, I will give that a go. I am migrating from asp to .net. I have
experience in vb in past way back...so hopefully it won't be too bad.

I appreciate you help.

Anyways, I got started on the sub that will be called onItemdataBound but am
having problems getting it...I know I am close, but here goes:


'THIS SUB HANDLES GRABBING CLIENT NAMES
Sub dlBookings_OnItemDataBound(sender As Object, e As DataListItemEventArgs)

If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType =
ListItemType.AlternatingItem Then

' Retrieve the Label control in the current DataListItem.
Dim ClientNameLabel As Label =
e.Item.FindControl("clientnamelabel")
Dim curClientID as Integer = e.item.FindControl("clientid").Text
********************************************** ERROR HERE I AM TRYING TO
GET THE VALUE OF 'CLIENTID'

' GRAB client name using ID from db here
Dim clientlabel As String
Dim strSQL as String


'SQL STATEMENT
strSQL = "SELECT fullname FROM CLIENT WHERE clientid=" & curClientID
'NEW CONNECTION OBJECT
Dim MyConn as New
SQLConnection(ConfigurationSettings.AppSettings("dbConn"))

'NEW DATAREADER
Dim objDR as SQLDataReader

'NEW COMMAND OBJECT
Dim Cmd as New SQLCommand(strSQL, MyConn)

'OPEN CONNECTION
MyConn.Open()

'EXECUTE QUERY AND RETRIEVE DATA INTO READER
objDR = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)


WHILE objDR.Read
clientlabel = objDR("fullname")
END While

myConn.close

'redisplay it in the DataList.
ClientNameLabel.Text = clientlabel.ToString()

End If



End Sub