469,160 Members | 1,877 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,160 developers. It's quick & easy.

Navigating datarelations

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("d bConn"))
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("d bConn"))

'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.Clos eConnection)

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>
<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"
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("bookingdate timestamp")%></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("bookingdate timestamp")%></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>

</asp:DataList>

<!--- END OF BOOKINGS PANEL --->
</asp:panel>


<!--- START OF NOBOOKINGS PANEL--->
<asp:panel 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 --->
</asp:panel>
</form>





</table>

<br>


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


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

Nov 18 '05 #1
1 1117
Hi,

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

http://www.c-sharpcorner.com/databas...RelationVK.asp

Natty Gur[MVP]

blog : http://weblogs.asp.net/ngur
Mobile: +972-(0)58-888377
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Les | last post: by
reply views Thread by Randy | last post: by
reply views Thread by George Durzi | last post: by
5 posts views Thread by Roy Lawson | last post: by
3 posts views Thread by Carlos Albert | last post: by
reply views Thread by Vajrala Narendra | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.