data relations and datasets inquiry

[Joe Van Meer]

Hi all,

I have a question regarding data relations within a data set. Say I have 3
tables named CLIENT, BOOKING and EMPLOYEE and I wanted to relate the first
two tables (CLIENT AND BOOKING) via a clientid and relate the second set of
tables (BOOKING AND EMPLOYEE) using an employeeid. My problem arises when
there a re no records in one of the tables in either data relation. What is
the best way to handle this, check the number of returned rows (marked below
with *****) in the table first and then create a relation? My code is
below:


Thanks and cheers, Joe




SUB Page_Load(Sender As Object, E As EventArgs)


Dim curAgentID as Integer
Dim curAgencyID as Integer
Dim curAgencyName 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")

'INITIALIZE FIRST STATUS TO 'FINAL'
curStatus = "Final"

'AGENCY BOOKINGS QUERY
strSQLa = "SELECT bookingid, clientid, agencyid, empid, arn,
bookingdatetimestamp, status FROM BOOKING WHERE agencyid =" & curAgencyID
'CLIENT NAME QUERY
strSQLb = "SELECT fname, lname FROM CLIENT WHERE agencyid=" & curAgencyID
'EMPLOYEE NAME QUERY
strSQLc = "SELECT 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")


*****
'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("BOOKING").Columns("clientid"),
ds.Tables("CLIENT").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)


ELSE


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

END IF


myConn.close

END SUB