Sort and Find Methods

[Steve Manley]

Hi there,

I need help

I wrote the code below to find a record specified by the
user. It only works if the fields being searched match
the sort criteria used to retreive the records from the
database. My problem is the users of the application
would like to to ability to perform searches using fields
of their own choosing.

It looks like the dataview (dvCustomer) is sorted and the
record is located in the dataview but this is not being
reflected in the dataset (dscustomer).

Can anyone give me sample code?

I don't need Datagrid control samples.

Thanks in advance for your help

Steve

=================Sample code below=====================

Private Sub btnFind_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnFind.Click
Dim strFirstName$
Dim strLastName$
Dim ArrayFind(1) As Object
Dim dvCustomer As DataView
Try
dvCustomer = New DataView(dsCustomers.Tables
("Customers"))
strFirstName = InputBox("Enter First Name to
find Customer record.", "Find Record", "John", 100, 100)
If Trim(strFirstName) <> "" Then
strLastName = InputBox("Enter Last Name to
find Customer record.", "Find Record", "Mark", 100, 100)
If Trim(strLastName) <> "" Then
ArrayFind(0) = strFirstName
ArrayFind(1) = strLastName
dvCustomer.Sort = "FirstName,LastName"
Dim intFind As Integer
intFind = dvCustomer.Find(ArrayFind)
If intFind <> -1 And intFind <=
Me.BindingContext(dsCustomers, "Customers").Count - 1 Then
Me.BindingContext
(dsCustomers, "Customers").Position = intFind
Else
MsgBox("Record not found.",
MsgBoxStyle.Exclamation, "Search Completed")
End If
Else
MessageBox.Show("Last Name is required
to do a search", "Last Name Missing",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
Else
MessageBox.Show("First Name is required to
do a search", "First Name Missing", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation)
End If
Catch
Err_Handler(Err.Number, Err.Description,
Me.Name, "btnFind_Click")
Finally
Err.Clear()
End Try
End Sub

 

[Bernie Yaeger]

Hi Steve,

I think one of the problems you're having is distinguishing between the
sorted dataview and the sorted or unsorted dataset. For example, 'select
fname, lname from custs order by fname' yields
Alan Young
Barbara Able
Harvey Brown

Now if you create a dataview and .sort the dataview by lname, the index is
the index as per the dataview, so when you sort by lname and find 'Young'
the index is 2, not 0, but in the datatable it's 0.

Here's some code I use to do what you're trying to do (note that I use 'i'
once found as the index of the view):

Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")

Dim ocmd As New SqlCommand("select * from histd_", oconn)

Dim oda As New SqlDataAdapter(ocmd)

Dim ods As New DataSet("History Details")

Try

oconn.Open()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

oda.Fill(ods, "History Details") ' this name must be same as 6 lines below

Dim vue As New DataView(ods.Tables(0))

vue.Sort = "title"

Dim i As Integer = vue.Find("natural health")

If i = -1 Then

MessageBox.Show("not found")

Else

MessageBox.Show(vue(i)("issuecode"))

End If

oconn.Close()

Now bringing this up to your next issue, you can make this dynamic by
setting .sort and the value of vue.find (or an object as you are using)
based on variables that change when you user selects a column and a value -
I'm sure you'll find this to be easy to develop.

HTH,

Bernie Yaeger