"Stuart" wrote:

> 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...

"JT" wrote:

> 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
>
> --
> John
>
>
> "Stuart" wrote:
>
> > 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...

"Stuart" <Stu***@discussions.microsoft.com> wrote in message
news:B7C13EE2-4DF4-4ED0-97BB-813E619FE907@microsoft.com...
> 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...