472,334 Members | 1,458 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,334 software developers and data experts.

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 1183
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Les | last post by:
I have a problem getting Dataset.WriteXml() to create a nested xml realtionship. All I get is an inline xsd (all nodes are children of the root). ...
17
by: Danny J. Lesandrini | last post by:
The following code works with a standard MDB to navigate to a particluar record (with a DAO recordset, of course) but it's giving me problems in an...
0
by: Randy | last post by:
I have two DataTables in a DataSet. I create a DataRelation between two like-typed columns in the first and second tables, and it works exactly as...
1
by: Fleckman | last post by:
I have a DataSet with 14 DataTables and 18 Relationships that were defined within Visual Studio .NET 2003. Everything looks OK in the visual...
0
by: George Durzi | last post by:
I have a DataSet with 3 tables, and two DataRelations dsSubs.Tables.TableName = "Subscriptions" dsSubs.Tables.TableName = "AccountManagers"...
5
by: Roy Lawson | last post by:
I am having no problems connecting to a DB, creating a DataAdapter, and creating a dataset...and connecting to the data. Using the builtin data...
3
by: Carlos Albert | last post by:
Hello everybody, I'm using some gridviews with datalists inside template columns. Now, for each datalist I'm calling a method returning a dataset...
1
by: JohnMOsborn | last post by:
I am designing an Access database that will use tab controls. Normally, you place different sets of fields on each page of the tab control like...
0
by: in10se | last post by:
I have a .NET 2.0 application that uses the WebBrowser control. Because all of my pages are generated dynamically, I am catching the Navigating...
0
by: Vajrala Narendra | last post by:
hi all, am working in asp.net i am using two data entry screens for parent and child tables. i heard that using datarelations we can achieve this...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.