Selecting Data from one Datatable and Copying to another

[Guest]

Hi there (code below !)

I have 2 datatables in a Dataset

Table 1 contains unique reference numbers - the contents of this table are
dynamic and may change from minute to minute

Table 2 contains records that have a field (Category) which may or may not
correlate with a unique reference number in Table 1

I want to be able to loop through all the unique reference numbers in Table
1 and find all records from Table 2 that have a Category field that matches
the current records in Table 1

I thought the best way to do this would be to firstly create a new table
(Table 3) and then create a loop on Table 1 that would take the Unique
Reference number as a variable, then use a Select command on table 2 to find
all records that match this Unique Reference number to their Category field.
These records should then be put in to table 3. The next Unique reference
from Table 1 would then follow the same pattern and so on until all records
from Table 1 had been looped through - this should then leave me with Table 3
containing all records from Table 2 that match any one of the Unique
References from Table 1

I can't make this happen - the closest I've got is in the code below - all
that gets returned to Table 3 is a line for each record that matches, but it
does not contain valid data, it just shows as system.data.datarow when I bind
Table 3 to a Grid ???

As per the above
Table 1 = SubCatSearchDT
Table 2 = SubJobDT
Table 3 = FoundJobsDT

Dim foundJobsDT As DataTable = SubCatDS.Tables.Add("FoundJobsTable")
foundJobsDT.Columns.Add("Job_Reference",
Type.GetType("System.String"))
foundJobsDT.Columns.Add("Job_Short_Desc",
Type.GetType("System.String"))

Dim i As Integer
For i = 0 To SubCatSearchDT.Rows.Count - 1
Dim searchRef As Integer =
SubCatSearchDT.Rows.Item(i).Item("Category_Reference")
'find all rows that return a match against the
Category_Reference above
Dim foundrows As DataRow()
foundrows = SubJobDT.Select("Job_Category_Parent = " & searchRef
& "")
foundJobsDT.BeginLoadData()
foundJobsDT.LoadDataRow(foundrows, False)
foundJobsDT.EndLoadData()

This is starting to hurt.... help! - there is probably a completely
different and simple way to do this... I have looked, but can't find...

 

[Guest]

Hi Stu,
Is foundRows() returning the datarows you are looking for? If so, you could
loop through foundRows to insert your data into the third datatable. As the
datarows already belong to another datatable, you need to use ImportRow.

For i as int32 = 0 to foundRows.length -1
FoundJobsDt.ImportRow(foundRows(i))
Next i

 

[Guest]

Excellent job John.... thanks very much for your help !!!

 

[Jamie Bissett]

Where are you getting the original data from. If it is an SQL database you
can accomplish the same by creating a dataAdapter and setting the Select
Statement to

select * from Table1
inner join Table2 on Table1.Category = Table2.Category

then do the following code :
adapter.Fill(dataset.Table3);

Bingo !!!!

Jamie