Navigating datarelations

[Lerp]

Hi all,

I have a dataset made up of 3 tables that is bound to a datalist.

On the itemdataBound event I call a sub that grabs a value (an id value)
from the current row being outputted, queries the database using this id
value to return a string value that I replace the id with in the datalist.

My question is this: instead of re-querying the database can I use the
dataset to replace this value using the id?

And what would the syntax be for accessing such a relation something like:
mylabel = mydataset.Relation("myrelationname").Columns(6)....? Or is
there a method I can use to access this based on the id? getParentRows
maybe? Where can I can such syntax examples? I am assuming this has to be
called in the same Sub.

I know there must be a way to access this data via the relations collection,
I am just unsure of the syntax of how to do so.

Cheers, and thx for any help! Lerp



Entire code is below:













<%@ 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)

Dim curAgentID as Long
Dim curAgencyID as Long
Dim curAgencyName as String
Dim curAgentName as String
Dim PageTitle as String
Dim curSecLevel as String
Dim curGroupID as Long
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












'THIS SUB HANDLES GRABBING CLIENT AND EMPLOYEE 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")

'GRAB CURRENT ID IN LOOP
Dim drv As DataRowView = CType(e.Item.DataItem, DataRowView)
Dim curClientID As Long = Long.Parse(drv.Row("clientid").ToString())

' 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

End If



End Sub









</script>



<html>
<head>
<title>AgentShopper</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css/todd.css" rel="stylesheet" type="text/css">



<!-- #include file="topsec.aspx" -->


<!--- START OF BOOKINGS PANEL--->
<form runat="server">

<table width="790" border="0" cellpadding="0" cellspacing="0">
<tr valign="top">
<td align="left" colspan="5" class="title"><%= Session("sesempFname") & " "
& Session("sesempLname")%>'s Draft Bookings - <asp:Label ID="mylabel"
runat="server"/></td>
</tr>
<tr height="15">
<td align="left" colspan="5"></td>
</tr>
<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"
OnItemDataBound="dlBookings_OnItemDataBound">



<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"><asp:Label id="clientnamelabel"
runat="server"/></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"><asp:Label id="clientnamelabel"
runat="server"/></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>






<!--- START OF NOBOOKINGS PANEL--->
<aspanel ID="nobookingspanel" runat="server">
<tr valign="top">
<td align="left" class="bodycopy">

<table width="790" cellpadding="5" cellspacing="0" border="0"
bordercolor="#CCCCCC">
<tr valign="top">
<td align="left" class="textbox"><%= Session("sesempFname") & " " &
Session("sesempLname")%> has 0 draft bookings currently.</td>
</tr>
</table>

</td>

</tr>
<!--- END OF NOBOOKINGS PANEL --->
</aspanel>


</form>













</table>



<br>








<!-- #include file="footer.aspx" //-->




</div>
</body>
</html>

 

[Natty Gur]

Hi,

It's ADO.NET topic, but I hope the following link will help you :

http://www.c-sharpcorner.com/database/DataRelationVK.asp

Natty Gur[MVP]

blog : http://weblogs.asp.net/ngur
Mobile: +972-(0)58-888377