473,598 Members | 3,018 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.Relat ion("myrelation name").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="te xt/html" ResponseEncodin g="iso-8859-1"
debug="true" %>
<%@ Import Namespace="Syst em.Data" %>
<%@ Import Namespace="Syst em.Data.SQLClie nt" %>
<script language="vb" runat="server">

SUB Page_Load(Sende r 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("sesemp Id")
curAgencyID = Session("sesemp Agencyid")
curAgencyName = Session("sesemp Agencyname")
PageTitle = "Agency Bookings"
curSecLevel = Session("sesemp Security")
curGroupID = Session("sesemp Groupid")
'AGENT BOOKINGS QUERY
strSQLa = "SELECT bookingid, clientid, agencyid, empid, arn,
bookingdatetime stamp, 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(C onfigurationSet tings.AppSettin gs("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).R ows.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).Ro ws.Count > 0 THEN
'SET UP TABLE RELATIONS HERE
Dim datrela as New DataRelation("C lientBookings",
ds.Tables("CLIE NT").Columns("c lientid"),
ds.Tables("BOOK ING").Columns(" clientid"))
'add relation to collection
ds.Relations.Ad d(datrela)

Dim datrelb as New DataRelation("E mployeeBookings ",
ds.Tables("EMPL OYEE").Columns( "empid"),
ds.Tables("BOOK ING").Columns(" empid"))
'add relation to collection
ds.Relations.Ad d(datrelb)
bookingspanel.v isible = true
nobookingspanel .visible = false
'BIND DATA TO DATALIST
dlBookings.Data Source = ds
dlBookings.Data Bind()
myConn.close
mylabel.Text = ds.Tables(0).Ro ws.Count
mylabel.visible = true

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

END IF

END SUB




'THIS SUB HANDLES GRABBING CLIENT AND EMPLOYEE NAMES
Sub dlBookings_OnIt emDataBound(sen der As Object, e As DataListItemEve ntArgs)

If e.Item.ItemType = ListItemType.It em Or e.Item.ItemType =
ListItemType.Al ternatingItem Then

' Retrieve the Label control in the current DataListItem.
Dim clientnamelabel As Label = e.Item.FindCont rol("clientname label")

'GRAB CURRENT ID IN LOOP
Dim drv As DataRowView = CType(e.Item.Da taItem, 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(C onfigurationSet tings.AppSettin gs("dbConn"))

'NEW DATAREADER
Dim objDR as SQLDataReader

'NEW COMMAND OBJECT
Dim Cmd as New SQLCommand(strS QL, MyConn)
'OPEN CONNECTION
MyConn.Open()
'EXECUTE QUERY AND RETRIEVE DATA INTO READER
objDR = Cmd.ExecuteRead er(system.data. CommandBehavior .CloseConnectio n)

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>AgentSho pper</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.as px" -->
<!--- 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("sesemp Fname") & " "
& Session("sesemp Lname")%>'s Draft Bookings - <asp:Label ID="mylabel"
runat="server"/></td>
</tr>
<tr height="15">
<td align="left" colspan="5"></td>
</tr>
<asp:panel ID="bookingspan el" runat="server">
<asp:DataList id="dlBookings "
runat="server"
cellpadding="3"
cellspacing="3"
GridLines="Both "
borderstyle="no ne"
backcolor="#FFF FFF"
width="790px"
headerstyle-font-name="Verdana"
headerstyle-font-size="13pt"
headerstyle-horizontalalign ="left"
headerstyle-font-bold="TRUE"
itemstyle-backcolor="#FFF FFF"
itemstyle-forecolor="#000 000"
alternatingitem style-backcolor="#C6E FF7"
alternatingitem style-forecolor="#FFF FFF"
footerstyle-font-size="9pt"
footerstyle-font-italic="true"
OnItemDataBound ="dlBookings_On ItemDataBound">

<HeaderTemplate >
<tr valign="top" bgcolor="#CCCCC C">
<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.D ataItem("arn")% ></td>
<td align="left" class="textbox" ><%#Container.D ataItem("status ")%></td>
<td align="left"
class="textbox" ><%#Container.D ataItem("bookin gdatetimestamp" )%></td>
<td align="left" class="textbox" ></td>
<td align="left" class="textbox" ><asp:Label id="clientnamel abel"
runat="server"/></td>
</tr>
</ItemTemplate>
<AlternatingIte mTemplate>
<tr valign="top">
<td align="left" class="textbox" ><%#Container.D ataItem("arn")% ></td>
<td align="left" class="textbox" ><%#Container.D ataItem("status ")%></td>
<td align="left"
class="textbox" ><%#Container.D ataItem("bookin gdatetimestamp" )%></td>
<td align="left" class="textbox" ></td>
<td align="left" class="textbox" ><asp:Label id="clientnamel abel"
runat="server"/></td>
</tr>
</AlternatingItem Template>

<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="nobookingsp anel" runat="server">
<tr valign="top">
<td align="left" class="bodycopy ">

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

</td>

</tr>
<!--- END OF NOBOOKINGS PANEL --->
</asp:panel>
</form>





</table>

<br>


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


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

Nov 18 '05 #1
1 1251
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
3476
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). Documentation says to use Dataset.Relations.Nested="true"; but this doesn't produce the nested xml as it should. I have even used a unit test, "MessageBox.Show(Dataset.Relations.Nested.ToString());". I get the "True" as an answer. So, the ReationsCollection has a relation and it is nested....
17
4193
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 ADP I'm working on. Dim rs As ADODB.Recordset Set rs = Me.RecordsetClone rs.Find "=" & lngContractID If Not rs.EOF Then Me.Bookmark = rs.Bookmark I must site the Heisenberb Uncertainty Principal here, as it
0
1175
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 expected. Unfortunately, the default behavior seems to be that the link (or links if there's multiple DataRelations) are all under the left-most column. Is there any way to put the link under the column being linked? Thanks... Randy
1
1901
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 development environment, i.e. when I preview the DataSet all the relationship appear correctly defined. However, when I create a new DataSet of this type within my project I only see two of the DataRelations in the DataSet.Relations collection. I don't know where else to look to trouble shoot the...
0
1443
by: George Durzi | last post by:
I have a DataSet with 3 tables, and two DataRelations dsSubs.Tables.TableName = "Subscriptions" dsSubs.Tables.TableName = "AccountManagers" dsSubs.Relations.Add "AccountManagers_Subscriptions", dsSubs.Tables.Columns dsSubs.Tables.Columns) dsSubs.Tables.TableName = "NewsFeeds"
5
7410
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 objects to do all this. My only problem now is navigating through the data. I can get the data into a datagrid without any problems, but I want the data to show up in textboxes and use some sort of move next, move previous, move last, etc (like in VB6) command to navigate the data (using...
3
4297
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 (depending from a column from the gridview). But I was wondering: is there a way to bring just one DataSet with two related tables and apply the "child" table to the datalist? I didn't find some examples of how to use DataRelations, so if anybody could
1
3441
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 Fields1-3 on Page 1, Fields 4-6 on Page 2, etc. In my database, however, I want to associate a particular numbered record of a field with each separate tab. In exploring my options, it looks like the best way to do this is to add a record-navigating “On Click” event to each separate page of the...
0
1902
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 event, cancelling it, and performing my own operations based on the Uri that is passed in the WebBrowserNavigatingEventArgs.Url property. If the page is requesting an external URL, I would like to open the page in a new browser window. When requesting an external page, the URL is of the form:...
0
977
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 using single screen. is it ppossible? please suggest how? Thanks inAdvance.
0
7981
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8284
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8392
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8046
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
6711
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5847
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
2410
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1500
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.