Hi Stefan,
What you need to do is find the index of the searched table. Here's some
code that provides an example of how to do that (which I provided for
another person a little while ago), with some notes at the end.
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")
oconn.Open()
Dim damaglist As New SqlDataAdapter("select * from maglist", oconn)
Dim dsmaglist As New DataSet("maglist")
damaglist.Fill(dsmaglist, "maglist")
Dim datransit As New SqlDataAdapter("select * from transit", oconn)
Dim dstransit As New DataSet("transit")
datransit.Fill(dstransit, "transit")
Dim irow As DataRow
Dim arrayseek(0) As Object
Dim ifind As Integer
Dim vue As New DataView(dstransit.Tables(0))
vue.Sort = "newbipad"
For Each irow In dsmaglist.Tables(0).Rows
arrayseek(0) = irow("bipad")
ifind = vue.Find(arrayseek)
If ifind <> -1 Then ' ie, found
MessageBox.Show(vue(ifind)("newbipad")) ' or any other col for that matter
' ifind is the index in the view - not the hard row number, but rather the
' number in the vue, sorted by newbipad
End If
Next
oconn.Close()
Note that I simply use the two tables as separate tables within their own
dataset - I find this easier, but I could probably do this even if both were
inside the same dataset (because I would be running a for loop on one table
while searching for the match on the other table, which is what I am doing
anyway).
By way of explanation, maglist (a list of magazines) has a unique # (but a
string) called 'bipad'; transit has a list of titles with a column called
'newbipad', which is also a 6 char unique string. Maglist has about 150
rows, but transit has only about 20 - these are replaced #'s in this
industry. So I can find every maglist title that has ever undergone a bipad
change. In fact, messagebox will be called approx 15 times, once for each
time it finds a match.
Note also that I sort the dataview (vue) at the outset in newbipad order.
Finally, note that I use an object - arrayseek - to search - I use an object
like this and name it 'array' because it could have more than one element -
if, say, I was searching for a match on issue # and publisher code at once,
then arrayseek(0) and arrayseek(1) would have to be loaded and the sort
order would have to be .sort = issuecode, pubcode.
Note especially that ifind is the index inside the dataview (ie, in the
table sort if ifind is 3 then the third row sorted thus would be found by
it); if you messagebox.show ifind you will see this.
Let me know if you have any questions.
HTH,
Bernie
