Help needed - wildcard searching

[ShiftySid]

I have an Access database, with a VB front-end. I have coded a button to search for records. The button works, but only if the record (in this case, a Surname) is typed in exactly. It does not work for wildcard searching (eg: *smi* to find all names containing those letters. I would have to type 'Smith' in full).

Does anyone have any ideas how my code (as follows) can be modified to enable this feature?

Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click

Dim FindName As String
Dim DataPosition As Integer
FindName = InputBox("Please enter surname to search for")
If FindName <> "" Then
Dim cm As CurrencyManager = CType(BindingContext _
(DataSet12, "tblData"), CurrencyManager)
Dim dv As DataView = CType(cm.List, DataView)
DataPosition = dv.Find(FindName)
If DataPosition > -1 Then
Me.BindingContext(DataSet12, _
"tblData").Position = DataPosition
End If
End If
End Sub



Many thanks for your help!

 

[Paszt]

You can set the RowFilter property of the dataView to a valid SQL WHERE clause .For example:
Name LIKE " & FindName.Replace("*"c, "%"c)
where Name is the name of the Field you want to 'search' in. The dataView will then only contain the rows with the text *FindName* in the Field 'Name'.

Also, there's an easier way to get a dataView from the dataTable. Every dataTable has a DefaultView property which is a dataView.

 

[ShiftySid]

Many thanks for your quick reply. Although I am quite new to this, and don't quite understand how to fit your suggestion into my code. Are you able to elaborate a little please?

Thanks.
S.

 

[jmcilhinney]

DataSet12.Tables("tblData").DefaultView.RowFilter = "Surname LIKE '%smi%'"

will cause any controls bound to that table to display only rows where the Surname field contains the substring "smi".

 

[ShiftySid]

Thanks, but that doesn't seem to work either. It doesn't 'jump' to the required record, but instead, just sits there on whatever record is currently displayed.

I should have perhaps explained I am not using a DataView or a DataGrid. My MS Access database fields are bound to, and displayed in Text Boxes.