multi table dataset and navigating between records

multi table dataset and navigating between records

Post by Ohad Weis » Tue, 27 Mar 2007 04:57:58


i all,

I've once asked about that topic. but didn't get an answer.

I have a dataset based on 4 tables, which have relation between them.
The main table presented to the user on textboxes placed on the form, and
the rest of the tables presented on textboxes placed on tabpages (tabpage
for each table).
Here is the code I use:


Code:
Function CraeteDataset(ByVal strConnection As String, ByVal stWhereClause As
String) As DataSet
Dim stMasterKeyColumns() As String = {"compid", "customer_id"}
Dim stDetailKeyColumns() As String = {"compid", "entity_id"}
Try
Dim dsData As New DataSet
Dim stWhereClauseAdd As String
Dim drDataRow As DataRow
dsData = clsDBGEN.FillDataSet(strConnection, "table1", _
"select * from table1", False, "table1", dsData)
dsData = clsDBGEN.FillDataSet(strConnection, "table2", _
"select * from table2", False, "table2", dsData)
dsData = clsDBGEN.FillDataSet(strConnection, "table3", _
"select * from table3", False, "table3", dsData)
dsData = clsDBGEN.FillDataSet(strConnection, "table4", _
"select * from table4", False, "table4", dsData)
dsData = clsDBGEN.CreateRelationship("table1", "table2", _
stMasterKeyColumns, stDetailKeyColumns, "table1table2", dsData)
dsData = clsDBGEN.CreateRelationship("table1", "table3", _
stMasterKeyColumns, stDetailKeyColumns, "table1table3", dsData)
dsData = clsDBGEN.CreateRelationship("table1", "table4", _
stMasterKeyColumns, stDetailKeyColumns, "table1table4", dsData)
Return dsData
Catch ex As Exception
HandleExceptions(ex)
End Try
End Function

Private Sub FillHeader(ByRef dsDataSet As DataSet)
Try
stDataSetTable = dsDataSet.Tables.Item("table1").ToString
txtId.DataBindings.Add(New Binding("text", dsDataSet,
stDataSetTable & ".id"))
txtName.DataBindings.Add(New Binding("text", dsDataSet,
stDataSetTable & ".name"))
Catch ex As Exception
HandleExceptions(ex)
MsgBox("Query caused no record to be retrieved",
MsgBoxStyle.Information)
End Try
End Sub

Private Sub FillLines(ByRef dsDataSet As DataSet)
Try
For Each drDataRow As DataRow In dsDataSet.Tables("table2").Rows
If drDataRow("compid") = dCompid And drDataRow("entity_id") = _
txtId.Text Then
txtStreet.DataBindings.Add("text", dsDataSet,
"table1.table1table2.street")
txtStrNo.DataBindings.Add("text", dsDataSet,
"table1.table1table2.street_no")
End If
Next
For Each drDataRow As DataRow In dsDataSet.Tables("table3").Rows
If drDataRow("compid") = dCompid And drDataRow("entity_id") = _
txtId.Text Then
txtShpStreet.DataBindings.Add("text", dsDataSet,
"table1.table1table3.street")
txtShpStrNo.DataBindings.Add("text", dsDataSet,
"table1.table1table3.street_no")
End If
Next
txt1.DataBindings.Add("text", dsDataSet,
"table1.table1table4.field1")
txt2.DataBindings.Add("text", dsDataSet,
"table1.table1table4.field2")
Catch ex As Exception
HandleExceptions(ex)
MsgBox("Query caused no record to be retrieved",
MsgBoxStyle.Information)
End Try
End Sub



When I query the dataset, each file