Reset Table

[JohnM]

I have two tables, A and B. The first one (A) I need to read one record at a time against the other table (B). I want to read the entire B Table, if I have to, until I get a match for each record from Table A.

My question is once I read thru Table B, and now I want to take the second record from Table A and start all over again from the beginning of Table B. Do I have to reset Table B somehow in order for it to start at the beginning again?

It looks like this:

Dim dr As DataRow
Dim dr1 As DataRow
For Each dr1 In Cars1.Tables("Cars").Rows
a1 = dr1.Item(0) 'Rid #
a2 = dr1.Item(1) ' Year of Car
cnt = 0

For Each dr In HistoryRepairs1.Tables("HistoryRepairs").Rows
all = all + 1
a9 = dr.Item(0) 'Id #
a0 = dr.Item(1) ' Year of History
If a1 = a9 And a2 = a0 Then
MessageBox.Show("Match for " & a1 & " " & a2, "Has a history record" & a9 & " " & a0)
cnt = cnt + 1
Exit For
Else : End If
MessageBox.Show("Records read so far " & all, "History records")
Next

HistoryRepairs1.Tables("HistoryRepairs").Reset()

If cnt = 0 Then MessageBox.Show("no match for " & a1 & a2, "Needs a history record")
Next

Thank you for your time.

John M

 

[Bob Langlade]

I am not sure it's going to work but you can try this. Instead of sequentially reading all your rows in both tables you build a relation between them using a unique ID for each car (Licence Plate, VIN, ...) and then you use the GetChildRows function to return all repairs for a specific car.

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]'Put your two tables in the same dataset
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ParentColumn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataColumn = Cars1.Tables([/SIZE][SIZE=2][COLOR=#800000]"Cars"[/COLOR][/SIZE][SIZE=2]).Columns([/SIZE][SIZE=2][COLOR=#800000]"ID"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ChildColumn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataColumn = Cars1.Tables([/SIZE][SIZE=2][COLOR=#800000]"HistoryRepairs"[/COLOR][/SIZE][SIZE=2]).Columns([/SIZE][SIZE=2][COLOR=#800000]"ID"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#008000]'Create DataRelation
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] CarRepairs [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataRelation([/SIZE][SIZE=2][COLOR=#800000]"CarRepairs"[/COLOR][/SIZE][SIZE=2], ParentColumn, ChildColumn)
[/SIZE][SIZE=2][COLOR=#008000]'Add the relation to the DataSet
[/COLOR][/SIZE][SIZE=2]Cars1.Relations.Add(CarRepairs)
 
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] FoundRows() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow
 

[/SIZE][SIZE=2][COLOR=#0000ff]For [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] dr1 [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] Cars1.Tables([/SIZE][SIZE=2][COLOR=#800000]"Cars"[/COLOR][/SIZE][SIZE=2]).Rows[INDENT]A1 = dr1.Item(0) [/INDENT][/SIZE][INDENT][SIZE=2][COLOR=#008000]' Id #
[/COLOR][/SIZE][SIZE=2]A2 = dr1.Item(1) [/SIZE][SIZE=2][COLOR=#008000]' Year of Car
[/COLOR][/SIZE][SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Match for {0}, Year: {1}"[/COLOR][/SIZE][SIZE=2], A1.ToString, A2)
Cnt = 0
FoundRows = dr1.GetChildRows([/SIZE][SIZE=2][COLOR=#800000]"CarRepairs"[/COLOR][/SIZE][SIZE=2])
 

Cnt = 0[/SIZE][INDENT][SIZE=2][COLOR=#0000ff]For [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] dr [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] FoundRows[INDENT]A9 = dr.Item(0) [/INDENT][/SIZE][INDENT][SIZE=2][COLOR=#008000]' Id #
[/COLOR][/SIZE][SIZE=2]A0 = dr.Item(1) [/SIZE][SIZE=2][COLOR=#008000]' Year of History
[/COLOR][/SIZE][SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]" Year of History: {0}"[/COLOR][/SIZE][SIZE=2], A0)
Cnt += 1
Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Records read so far: {0}"[/COLOR][/SIZE][SIZE=2], Cnt.ToString)
[/SIZE]

[/INDENT][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE]

[/INDENT][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] Cnt = 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][SIZE=2] Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"No match for this car, needs a history record"[/COLOR][/SIZE][SIZE=2])
[/SIZE]

[/INDENT][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE]

 

[JohnM]

Bob,

Thank you for this. There's so much I can see that I don't know about.
This will save processing time, I know that anyway.

I will try this concept.

Thank you again


John M