Find Equivalent

V

vul

In ADO in order to set the pointer to the particular position in a recordset
I use Find method. Something like:

RS.MoveFirst
RS.Find "CustomerID = " & lngCustomerID

Now I'm using DataTable containing Rows. I'd like to come to the row where
CustomerID is equal to the value I'm looking for. Then I'm going to use
DataBindings to populate controls with values from a found row.
Tried to search MSDN with no success.

What approach can I use?

Thank you
 
V

vul

Thank you Scott.
It doesn't work
It doesn't produce any error, but the position remains the same in datatable
and databindings objects.
Probably I do something wrong.
 
C

Cor Ligthert [MVP]

Vul,

To position a dataset according to the databinding you can use the
currencymanager.position.

To find a datarow there are plenty of methods. One of those is as Scott
showed.

dim dr() as datarow = yourDataTable.Select("CustomerID='" & lngCustomerID &
"'")

Which gives you as an array of references to the datarows which fulfils this
condition.

In combination with a currencymanager.position is the DataView.find a nice
method.

http://msdn2.microsoft.com/en-us/library/46d41xk2.aspx

As long of course as this is your source for the binding.

However can you explain more how you are using it. A dataset is surely no
recordset. A datatable reaches that more.

I hope this helps,

Cor
 
V

vul

Cor, thank you
I have a pretty big sets of values needed to be displayed on the form (30-60
depending on the form). I also have a lot of records in SQL Server tables.
My approach to work with data is the following (in ADO):
1. I'm getting all IDs from the main table for the particular screen by
using SP. Let's say it's Job table. This table has about 50 fields, most of
them are foreign keys.
2. I populate a recordset with those IDs. After I got all IDs (I use SP) I
find a record (with Recordset.Find method) I'm interested in (JobID from
previous session which was stored in a global variable).
3. Using JobID as parameter I get all values from many tables (SP again) and
put them in another Recordset. This recordset has only 1 row.
As you can see I do not use much data - one recordset with only 1 field and
another recordset with all required fields, but with 1 row only.
4. I populate all controls on the form with data from the second Recordset.
5. In case I need to move to the next record I'm using MoveNext method in
the first Recordset, I get JobID from a new record, I use it to request all
required values for the second recordset again.
6. All these steps I can use with ADO.Net without any problem using
datatables and databindings.
7. But I have a problem with Search I'm doing in ADO this way: I'm using SP
which returns me JobIDs for records following search criterias. I'm getting
the first JobID from the third recordset, using Find method in the first
recordset I put a pointer to the corresponding record. An now I can populate
a second recordset and controls. In this case I have the ability to go to
the next record in the first recordset, or to the next record in the third
recordset and to the corresponding record in a first recordset.
I have a full control with navigation in an original recordset containing
all JobIDs as well as in filtered by some criterias search recordset.
In ADO.NET I found the way to navigate in an original datatable (let's think
about it as of first recordset) only by moving next, previous etc. But how
to put a pointer to the particular datarow containing the same JobID as in a
search datatable?

Thank you
Al
 
C

Cor Ligthert [MVP]

Vul,

There are many ways to fulfil your problem. The first one to try is forever
the relation.

http://www.vb-tips.com/default.aspx?ID=3eafa9ea-a906-45ff-aece-e8335682ad3a

Be aware as showed in this sample, that the dataset is just a (very fine)
wrapper around datatabels and datarelations. The dataset has beside that a
lot of very handy methods.

If this is not the way you searching, reply because the possibilities in Net
seems endless.

I hope this helps,

Cor
 
V

vul

Thank you.
The sample you gave is not going to work for me by the following reason:
If let's say I create a datatable with JobIDs and the second table should
contain all related records. It's going to be a huge amount of data.
With my current approach the first datatable is used only as a set of
JobIDs where I can go to the next, previous etc record.
I'm looking for the way to set a pointer in a first datatable (JobIDs) to
the particular JobID, not to the 20th or 1235th record in the datatable.
For instance I have the following JobIDs (presented as datarows in a
datatable):
7, 26, 5, 10, 135, 1354, 18, 35, 35655, 123, 345, 12323 etc. (they are
sorted by some criteria in SP)
If I use databindings I can go from the JobID = 7 to JobID = 26 by using
Position +=1
After I came to the second position I can retrieve JobID which is equal to
26 in this particular case. Then using it as a parameter I can retrieve all
data for this particular Job by using another SP. It will give me a second
datatable with a single datarow. The minimal amount of data is passing from
DB. I don't need any datarelation here.
My question again:
For instance I'm doing a search. I need to find a Job with the name "Testing
Job". I'm retrieving a JobID for the Jobs having "Testing Job" in their
names.
I'm getting for instance 5 JobIDs (10, 18, 123)
My pointer in the first datatable is on Position 0, let's say now. How do I
set it to Position 3 which has JobID = 10?
If I was able to do that then I could go from position 3 to position 4 or
position 2 when I use Position +=1. For now I do not know how to do that.
I also could go to the Position 1 in search datatable by using Position +=1,
retrieve JobID, which is 18, set the pointer to the Position 6 in a first
datatable, having the ability to go to Position 7 or 5.
So it's the same if I work with ADO by using Recordset.MoveNex,
Recordset.MovePrevious, Recordset.Find.
I have this code in VB6:

rsAllIDs.MoveFirst
rsAllIDs.Find "JobID = " & lngJobID 'This value vas taken in some
other procedure, like search for instance

GetCurrentRecord lngJobID' This procedure will populate all controls
on the form with data belonging to the JobID sitting in lngJobID variable.
The pointer in rsAllIDs is on the record found by Find method of the
recordset.

How to translate this small block of code to ADO.NET?

Thank you
 
V

vul

You are right. There is a huge amount of data again. All Customers with all
records. I cannot afford this with my database. I need to retrieve the data
for a single entity (Job, Customer, whatever). In VB6 with ADO I believe I
found a good solution. With ADO.NET so far no success.
As I think it's a pretty common case - to set the pointer in a datatable to
the row with a particular value in one of the field.
Thank you
 
C

Cor Ligthert [MVP]

Vul,

I really don't understand you. You have three tables.

You set a relation in the first two.

In the retrieved second you get the key to let your sp for the thirth go.

I have showed you two methods to do that.

Much easier than doing that by trying to find first a datarow using those
two tables.

The datarow with the key is there automatic.

You only tell me to much data, what data are you talking about.

The first table. The second table. The thirth table, which is as small as
the rows comming from the key from the related datatable?

Cor
 
V

vul

In the sample you showed me:
Select * from Employees
Select * from Orders
Then there is relation is coming between them

So if I use this approach I will get all Jobs (100000 of them now with a big
number of fields)
Then I will get even bigger set of Orders. Then I'll set relation between
them.

The third datatable is coming later with a limited number of datarows in it.
But I already have a problem with retrieving data for the first 2
datatables. Too big volume of data should come from the server.

I'm trying to clear my problem again:
I need a way to find a particular value (JobID) in the first datatable and
set a pointer to that datarow. That's it. Nothing else. Just like in ADO
Recordset.Find "JobID = " & someJobID
This statement finds a record in a recordset and sets a bookmark. If I'll
use MoveNext method after that the pointer will move to the next record
after just found one in a recordset. If let's say I started with
Recordset.AbsolutePosition = 0, used Find method and came to
AbsolutePosition = 1234, after MoveNext I will have AbsolutePosition = 1235.
I'm looking for the same functionality in ADO.NET. In other words, if I'm on
the 10th datarow in a datatable and there is a datarow which contains a
value I'm interested in (let's say JobID=15676), I'd like to have some way
to find it and set a pointer (Databinding.Position) to that particular
datarow in a datatable.
Do you know any way to find some value in datatable and set a pointer to the
found datarow in a datatable. I'm not interested in getting data from that
row, I need to set a pointer. Later I will use Position +=1 to move to the
next datarow in a datatable.

Thank you
 
V

vul

Thank you very much Cor.
I don't know how could I miss the link you gave me in the first post.
I did not solve my problem yet, but I moved a little bit further.
Now I have a problem with requirement to sort view before Find method. Most
of my datatables are sorted by some values other than Primary key. For
instance I'm retrieving AccountIDs from Account Table sorted by AccountName.
Now my datatable with IDs has something like 123, 3245, 12, 4567, etc. The
same stuff is in BindingSource. I have only one field in Account datatable
and if I sort by AccountID it contains 12, 123, 3245, 4567. After assign
Sort = "AccountID" and run Find I get Position 1, but it's true for datview
only. If I set Position to 1 in BindingSource, I'm coming to the record
containing AccountID = 3245.

Anyway, thank you very much.
 
C

Cor Ligthert [MVP]

Vul,

Probably is this your most easiest solution.

Don't be afraid for a loop, it is the way as it is often done behind the
scene.

If you comes than really in trouble with this, you can first copy your
datatable to a hashtable (which has a tag search), but I would first try it
with this and use that.

\\\
'Make a test table
Dim dt As New DataTable
dt.Columns.Add("")
For i As Integer = 0 To 10
dt.LoadDataRow(New Object() {i.ToString}, True)
Next

'Find the row
Dim y As Integer
For y = 0 To dt.Rows.Count - 1
If dt.Rows(y)(0).ToString = "3" Then
Exit For
End If
Next
MessageBox.Show(dt.Rows(y + 1)(0).ToString)
///
I hope this helps,

Cor
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concurrency control in updating Access records? 4
How to navigate through dataset 2
FIND like old ADO method 2
Can someone explain wiring between datagrid and a Find method? 2
BindingSources, BindingNavigators and direct navigation... 5
Clearing row selected from DataGridView 7
DataRowView Equals does not work as documented 5
function that would lookup for null value 2

Top