Dataset filtering problem

[Alex Ayzin]

Hi,

I have a problem that might be easy to solve(possibly, I've just overlooked
an easy solution). Here we go:

I have a dataset with 2 datatables in it. Now, I need to do the following:

if ds.table(0).rows(0).item("col1") = ds.table(1).rows(0).item("col2") then

txtResult.text = ds.table(1).rows(0).item("col3")

end if

The problem is, that possible match exists in the database, but not
nessasarily at Rows(0). It could be at different index. But how to actually
get to the correct index??

I looked into the database, and in my case first possible match is at index
Row(1) so the correct version'd be like this:

if ds.table(0).rows(1).item("col1") = ds.table(1).rows(1).item("col2") then

txtResult.text = ds.table(1).rows(1).item("col3")

end if

But of course, there is no way to know in advance, what index to use, so I'm
lost a bit.

Basically, I have a couple of ways of doing it. I could use Select or Find
methods of datatable object to apply filtering on the datatables. But, the
problem is that filtering needs to be applied on 2 different tables of the
dataset, but I know how to do it for 1 datatable only. I need to merge 2
datatables into dataview and apply a filter. But how to do it? Anyway, if
anyone has any ideas, so please share it with me. Thanks a lot in advance,

--Alex

 

[Bernie Yaeger]

Hi Alex,

You're making it more difficult than it is.

1. Set up a dataview for one of the tables.
2. do a for each loop of the other table and search the dataview for the
value - if found, etc.
3. after you'r done, you should have found each match, and the view's find
method will return the appropriate index.

HTH,

Bernie Yaeger

 

[Alex Ayzin]

Thank you for you reply, Bernie.

I just don't see how I'd actually get a match using this technique.
Could you elaborate a little bit?
Actually, I simply don't understand, how there is no filtering based on
multiple - datatables criterias.

Please, advise
--Alex

 

[Bernie Yaeger]

Hi Alex,

Here's some code of what I have in mind; some explanatory comments within
and some explanatory 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