Relating two Datatables

[Gerry Viator]

Hi all,

Trying to relate copy's of two tables in another dataset so I can loop
through
MainDS

Basically i'm trying to copy the two datatables and then put them in a
global dataset.
I then need to loop through the dataset.

so how do I relate and copy two datatables into one datatable and then
filter through it?

something like this:

For icounter = 0 To .Rows.Count - 1
If .Rows(icounter).Item("ModuleOwner") =
GlobalModuleName Then
If .Rows(icounter).Item("EntryName") =
SelectedItem Then
If .Rows(icounter).Item("EntryType") =
"text" Then

ElseIf .Rows(icounter).Item("EntryType") =
"list" Then

ElseIf .Rows(icounter).Item("EntryType") =
"mtext" Then

ElseIf .Rows(icounter).Item("EntryType") =
"Date" Then


End If


End If
End If

Next



Dim StrEntryList As String = "Select * from EntryList order by
ModuleOwner"
Dim StrValueList As String = "Select * from ValueList order by EntryName"

EntryDataTable = (ReturnSQLlist(StrEntryList, True, "Running Entry SQL
Query.")).Tables(0)
ValueDataTable = (ReturnSQLlist(StrValueList, True, "Running Values SQL
Query for Entries.")).Tables(0)

Dim Tble1 As DataTable = EntryDataTable.Copy
Tble1 = MainDS.Tables.Add

Dim Tble2 As DataTable = ValueDataTable.Copy
Tble2 = MainDS.Tables.Add

Dim RelatedTables As New DataRelation("DsRelation",
Tble1.Columns("EntryName"), Tble2.Columns("EntryName"))

MainDS.Relations.Add(RelatedTables)

DataGrid1.DataSource = MainDS




Public Function ReturnSQLlist(ByVal StrQuery As String, ByVal Querytxt As
Boolean, ByVal Msg As String) As DataSet


Dim DS As New DataSet
Dim DA As New SqlDataAdapter
Dim Dt As New DataTable
Dim row As DataRow
Dim icounter As Integer

SQLConnection()

Dim SQLCmdlist As New SqlClient.SqlCommand

If Querytxt = True Then
SQLCmdlist.CommandType = CommandType.Text
SQLCmdlist.CommandText = StrQuery
Else
SQLCmdlist.CommandType = CommandType.StoredProcedure
End If

SQLCmdlist.Connection = SQLConn
SQLConn.Open()

DA.SelectCommand = SQLCmdlist
DA.Fill(DS)

SQLCmdlist.Connection.Close()
SQLCmdlist.Dispose()

ReturnSQLlist = DS



End Function

 

[William Ryan eMVP]

I'm not sure exactly what the end goal is...usually you can do everything
you want with one dataset. However, since datatables are reference types,
as are datacolumns, if you want to copy it, you'll need to create a new
datatable and set the new datacolumns to the value in the corresponding
original.

If I understand correclty, you could also just create one new table, and
then iteratively walk through each table, adding a new row to the 'new'
table and setting each column in the datarow's value to the respective value
in the two 'base' tables.

HTH,

Bill

 

[Kevin Yu [MSFT]]

Hi Gerry,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to copy two DataTables and add
them to a DataSet. Also, you want to create relationships between the two
copied DataTables. If there is any misunderstanding, please feel free to
let me know.

Based on the code you provided, most of them are right except the following:

Dim Tble1 As DataTable = EntryDataTable.Copy
Tble1 = MainDS.Tables.Add

Dim Tble2 As DataTable = ValueDataTable.Copy
Tble2 = MainDS.Tables.Add

The code will only create an empty DataTable and return the reference to
Tble1. I don't think this is your purpose. I think you need to add the
copied table with data to the MainDS, so I made some changes to the code.

Dim Tble1 As DataTable = EntryDataTable.Copy
MainDS.Tables.Add(Tble1)

Dim Tble2 As DataTable = ValueDataTable.Copy
MainDS.Tables.Add(Tble2)

Moreover, you needn't open and close the connection manually in the
ReturnSQLlist function. The SqlDataAdapter will open and close it
automatically. Hope this helps.

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Gerry Viator]

Thanks

yes that did help help me, can you confirm how to set the relation between
the two tables after that?

thanks again
Gerry

 

[Kevin Yu [MSFT]]

Hi Gerry,

I think your code of creating the DataRelation is correct. In the
constructor of DataRelation, please make sure that the second parameter is
the parent column while the third parameter is the child column. Parent
table record to child table record is 1:n.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Gerry Viator]

thanks

gerry