finding and going to a particular record in a dataset?

[shootsnlad]

I'm not sure I'm using the best way to do what I want, so I will provide an explanation and I was wondering if anyone can give me some input. Here is what I want to do:

I have an Access Database that is linked to an Informix database. I have 3 tables that I want to interact with in that database. I want a form for each table. On each form would be various fields designated for each field in the database. I want the person to load a particular form, and then be able to navigate through the database. The user should be able to do things like: next, previous, first, last. I want buttons for Adding, Changing, Deleting and Finding a particular record.

Here is what I have done:

I used the data from wizard in .NET. It built me everything above, except for the find button. I currently am able to navigate through the database, as well as add, change, and delete a record. I just need to figure out how to do a find on the dataset. I have been able to bring back a datarow, but unable to tell the dataset to goto that position (where the datarow is located). I also thought of just doing a FOR..NEXT loop to go through each record until I found the search criteria, grabbing that position, then moving the dataset to that position. I couldn't get that to work either, because I can't pull out the value of the field to compare the search criteria.

Any ideas? I appreciate the feedback.


shootsnlad

[Dozo_1st]

what I do in my apps is building a dsMyDataset as new Datatable
and to find something I use the following code:

i = 0
Do Until (i = dsMyDataset.Rows.Count)
If dsMyDataset.Rows(i).Item("MyItem") = Searchstring.Text Then
With dsMyDataset.Rows(i)
txtbox1.Text = .Item("Item1")
txtbox2.Text = .Item("Item2")
txtbox3.Text = .Item("Item3")
txtbox4.Text = .Item("Item4")
txtbox5.Text = .Item("Item5")
txtbox6.Text = .Item("Item6")
End With
End If
i = i + 1
Loop

Hope this helps....

Dozo

[shootsnlad]

Here is what I currently have, which is pretty similar, but does not work:

Dim reccount, i, whereiam As Integer
Dim s As String
s = InputBox("Enter in search criteriar:", "Search")
If s = "" Then

Else
       reccount = 0
   Do Until (reccount = Me.BindingContext(myobject, "mydataset").Count)
       If myobject.mydataset.Rows(reccount).Item("column number") = s Then
             whereiam = Me.BindingContext(myobject, "mydataset").Position
       End If
       reccount = reccount + 1
   Loop
   If whereiam > 0 Then
                Me.BindingContext(myobject, "mydataset").Position = whereiam
   Else
                MsgBox("Unable to find search.", MsgBoxStyle.OKOnly, "Not found")
            End If
        End If

I apologize for the poor formatting. I think one of the problems might be the ".item" statement. I think that your "MyItem" is referring to the column number, correct? That may be part of my problem.

[shootsnlad]

I figured it out. I had 1 minor problem and 1 major problem. In my code above I replaced "column number" with my column name (in quotes). The major one was my assignment of the 'whereiam'
variable. I set it equal to reccount if I found the search I was looking for. Apparently when I was moving through the dataset, the .position never changes. Thanks for the help!

[Dozo_1st]

Exactly...

[tliau]

Hi there,

After searching this forum for a good way to find a record in a dataset, I was not able to find a good and efficient way to do it, so I have coded my own search algorithm based on the binary search technique. Although it is not the most efficient way, but it is better than looping the entire dataset especially if you have over thousands of records. So I'm sharing my code here. Feel free to modify for your needs. Please also give me your feedback on this code and how I can improve on it. Thanks!

Thomas

Public Function BinarySearchRecord(ByVal ds As DataSet, ByVal intMinValue As Integer, ByVal intMaxValue As Integer, ByVal intMaxRecValue As Integer, ByVal strTableName As String, ByVal strFieldName As String, ByVal strSearchValue As String) As Integer

Dim intMiddle As Integer
Dim intReturnValue As Integer
If intMinValue > intMaxRecValue Then
BinarySearchRecord = -1
Else
If ds.Tables(strTableName).Rows(intMaxValue).Item(strFieldName) = strSearchValue Then
BinarySearchRecord = intMaxValue
ElseIf ds.Tables(strTableName).Rows(intMinValue).Item(strFieldName) = strSearchValue Then
BinarySearchRecord = intMinValue
ElseIf intMinValue = intMaxValue Then
If ds.Tables(strTableName).Rows(intMaxValue).Item(strFieldName) = strSearchValue Then
BinarySearchRecord = intMinValue
Else
BinarySearchRecord = -1
End If
ElseIf intMinValue < intMaxValue Then
intMiddle = (intMinValue + intMaxValue) / 2
intReturnValue = BinarySearchRecord(ds, (intMinValue + 1), intMiddle, intMaxRecValue, strTableName, strFieldName, strSearchValue)
If intReturnValue <> -1 Then
BinarySearchRecord = intReturnValue
Else
BinarySearchRecord = BinarySearchRecord(ds, (intMaxValue + 1), (intMaxRecValue - 1), intMaxRecValue, strTableName, strFieldName, strSearchValue)
End If
Else
BinarySearchRecord = -1
End If
End If
End Function

[tliau]

Here is an improved version of the previous code: -

Public Function BinarySearchRecord(ByVal ds As DataSet, ByVal intMinValue As Integer, ByVal intMaxValue As Integer, ByVal strTableName As String, ByVal strFieldName As String, ByVal strSearchValue As String) As Integer
Dim intMiddle As Integer
intMiddle = (intMinValue + intMaxValue) / 2
If intMinValue <= intMaxValue Then
If ds.Tables(strTableName).Rows(intMinValue).Item(strFieldName) = strSearchValue Then
BinarySearchRecord = intMinValue
ElseIf ds.Tables(strTableName).Rows(intMaxValue).Item(strFieldName) = strSearchValue Then
BinarySearchRecord = intMaxValue
ElseIf BinarySearchRecord(ds, (intMinValue + 1), intMiddle, strTableName, strFieldName, strSearchValue) <> -1 Then
BinarySearchRecord = BinarySearchRecord(ds, (intMinValue + 1), intMiddle, strTableName, strFieldName, strSearchValue)
ElseIf BinarySearchRecord(ds, (intMiddle + 1), (intMaxValue - 1), strTableName, strFieldName, strSearchValue) <> -1 Then
BinarySearchRecord = BinarySearchRecord(ds, (intMiddle + 1), (intMaxValue - 1), strTableName, strFieldName, strSearchValue)
Else
BinarySearchRecord = -1
End If
Else
BinarySearchRecord = -1
End If
End Function


Thomas