TonyScarpelli
Programmer
Here it is: Code for an Incremental Search Grid in VB .NET
I got so frustrated trying to find an Incremental Search Grid in VB .NET that it drove me to finally write the code myself. I got a hint of how to do it from Kenvin Goff in the Universal Thread Mere Mortals section, but it was in C, and I'm a VB programmer; but I finally figured it out, and with the help of the MS article Q308070, I finally got to do it in VB .NET. I'm not a real experienced VB .NET programmer so there may be a lot of redundant code in here, and I might not know completely what I'm talking about, but the thing does work.
First create a test form (frmFind) and put some labels, text boxes, buttons, and a grid on it:
lblFind, lblFind2
txtIDSearch, txtLNSearch
btnFind, btnQuitApp
grdFind
and put this stuff in at the top:
-----------------------------------------------------------
Imports System.Data.SqlClient
Public Class frmFind
'Declare global variables
Inherits System.Windows.Forms.Form
'Modify this string to correctly connect to your SQL Server.
Dim con As New SqlConnection("Server=(local);Database=pubs;Integrated Security=True;")
'Dim con As New SqlConnection("server=(local);uid=YourUserId;" & _
' "pwd=YourPassword;database=pubs")
Dim daAuthors As New SqlDataAdapter("Select * From Authors", con)
Dim ds As New DataSet
Dim WithEvents dv As DataView
Dim CM As CurrencyManager
Dim cFilterOld As String
Dim cFilter As String
Const MessageBox_Caption As String = "Notice"
-----------------------------------------------------------
Then all this stuff comes next:
-----------------------------------------------------------
Private Sub frmFind_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Retrieve data from the Authors table.
daAuthors.Fill(ds, "Authors")
dv = New DataView(ds.Tables("Authors"))
'Bind the data to the DataGrid.
grdFind.DataSource = dv
'Set the default sort order.
dv.Sort = "au_id"
'Initialize CurrencyManager to hold an instance of the form's CurrencyManager.
CM = grdFind.BindingContext(dv)
End Sub
Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
'Verify that the user typed test to search for.
If Len(Trim(txtIDSearch.Text)) = 0 Then
MessageBox.Show("Value required for search.", _
MessageBox_Caption, MessageBoxButtons.OK, MessageBoxIcon.Information)
txtIDSearch.Focus()
Else
'Search for the record in the DefaultView.
'If found, move the pointer to the correct record in the grid.
Dim i As Integer
i = dv.Find(txtIDSearch.Text) 'Locates record in DefaultView.
'Does not move grid pointer or CM.
If i > dv.Table.Rows.Count Or i < 0 Then
MsgBox("Record Not found", MsgBoxStyle.Information, "Record Not Found")
Else
CM.Position = i 'Synchronizes Cm and Grid Pointer.
End If
End If
End Sub
Private Sub dv_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles dv.ListChanged
'Strip the DESC off the dv.Sort string if the user has set the order to descending.
'This allows the lblFind.Text to be properly displayed.
If dv.Sort.Substring((Len(dv.Sort) - 4), 4) = "DESC" Then
lblFind.Text = "Enter Search Criteria: " & dv.Sort.Substring(0, Len(dv.Sort) - 5)
Else
lblFind.Text = "Enter Search Criteria: " & dv.Sort
End If
End Sub
Private Sub btnQuitApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuitApp.Click
Application.Exit()
End Sub
Private Sub txtIDSearch_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtIDSearch.GotFocus
dv.Sort = "au_id"
ClearControls()
End Sub
Private Sub txtIDSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtIDSearch.TextChanged
Dim lcStr As String
lcStr = UCase(Trim(Me.txtIDSearch.Text().ToString))
IncrSearch(lcStr, "au_id")
End Sub
Private Sub txtLNSearch_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtLNSearch.GotFocus
dv.Sort = "au_lname"
ClearControls()
End Sub
Private Sub txtLNSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtLNSearch.TextChanged
Dim lcStr As String
lcStr = UCase(Trim(Me.txtLNSearch.Text().ToString))
IncrSearch(lcStr, "au_lname")
End Sub
Private Sub IncrSearch(ByVal lcSearchValue, ByVal lcSortOrder)
'Get the Default View of the DataSet
ds.Tables(0).DefaultView.Sort = lcSortOrder
'Set up the local search variables
Dim i, lnRowCount, lnSize As Integer
Dim lcField, lcSrchTerm, lcReqID As String
Dim bMgr = Me.BindingContext(grdFind.DataSource, grdFind.DataMember)
lnRowCount = ds.Tables(0).DefaultView.Count
lnSize = Len(lcSearchValue)
'Start searching for the lcField value
For i = 0 To lnRowCount - 1
'Get the field value for the row
lcField = UCase(ds.Tables(0).DefaultView(i)(lcSortOrder))
lcSrchTerm = Microsoft.VisualBasic.Left(lcField, lnSize)
If lcSrchTerm = lcSearchValue Then
Dim Position As Integer = ds.Tables(0).DefaultView.Find(lcField)
Me.BindingContext(ds, ds.Tables(0).TableName).Position = Position
bMgr.position = Position
Exit For
End If
Next
End Sub
Private Sub ClearControls()
Me.txtIDSearch.Text = ""
Me.txtLNSearch.Text = ""
End Sub
End Class
-----------------------------------------------------------
Fix any errors.
Here's the way it works:
When a text box gets the focus it sets the sort of the view so that the grid column is displayed in the correct sort order, and then clears out the other text boxes:
dv.Sort = "au_id"
ClearControls()
Then when the text changes, it formats the search string and calls the IncrSearch function with the search string and to set the sort order for the search.
Dim lcStr As String
lcStr = UCase(Trim(Me.txtIDSearch.Text().ToString))
IncrSearch(lcStr, "au_id")
The incremental search function then makes sure the Default View is in the correct sort order. It then sets up the local search variables and binds the grid to the data set.
It then looks through the records, first to get the actual field value, then for the first letter of the search string we are looking for, and if it finds a match, will get it's position, makes sure the grid is pointing to it, and then jumps out of the loop, ready to get the next letter. (Warning, this code doesn't like Nulls in the fields, so for now I just make sure there isn't any in my tables.)
That's it. I hope this will help you all out.
CU
Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102
I got so frustrated trying to find an Incremental Search Grid in VB .NET that it drove me to finally write the code myself. I got a hint of how to do it from Kenvin Goff in the Universal Thread Mere Mortals section, but it was in C, and I'm a VB programmer; but I finally figured it out, and with the help of the MS article Q308070, I finally got to do it in VB .NET. I'm not a real experienced VB .NET programmer so there may be a lot of redundant code in here, and I might not know completely what I'm talking about, but the thing does work.
First create a test form (frmFind) and put some labels, text boxes, buttons, and a grid on it:
lblFind, lblFind2
txtIDSearch, txtLNSearch
btnFind, btnQuitApp
grdFind
and put this stuff in at the top:
-----------------------------------------------------------
Imports System.Data.SqlClient
Public Class frmFind
'Declare global variables
Inherits System.Windows.Forms.Form
'Modify this string to correctly connect to your SQL Server.
Dim con As New SqlConnection("Server=(local);Database=pubs;Integrated Security=True;")
'Dim con As New SqlConnection("server=(local);uid=YourUserId;" & _
' "pwd=YourPassword;database=pubs")
Dim daAuthors As New SqlDataAdapter("Select * From Authors", con)
Dim ds As New DataSet
Dim WithEvents dv As DataView
Dim CM As CurrencyManager
Dim cFilterOld As String
Dim cFilter As String
Const MessageBox_Caption As String = "Notice"
-----------------------------------------------------------
Then all this stuff comes next:
-----------------------------------------------------------
Private Sub frmFind_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Retrieve data from the Authors table.
daAuthors.Fill(ds, "Authors")
dv = New DataView(ds.Tables("Authors"))
'Bind the data to the DataGrid.
grdFind.DataSource = dv
'Set the default sort order.
dv.Sort = "au_id"
'Initialize CurrencyManager to hold an instance of the form's CurrencyManager.
CM = grdFind.BindingContext(dv)
End Sub
Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
'Verify that the user typed test to search for.
If Len(Trim(txtIDSearch.Text)) = 0 Then
MessageBox.Show("Value required for search.", _
MessageBox_Caption, MessageBoxButtons.OK, MessageBoxIcon.Information)
txtIDSearch.Focus()
Else
'Search for the record in the DefaultView.
'If found, move the pointer to the correct record in the grid.
Dim i As Integer
i = dv.Find(txtIDSearch.Text) 'Locates record in DefaultView.
'Does not move grid pointer or CM.
If i > dv.Table.Rows.Count Or i < 0 Then
MsgBox("Record Not found", MsgBoxStyle.Information, "Record Not Found")
Else
CM.Position = i 'Synchronizes Cm and Grid Pointer.
End If
End If
End Sub
Private Sub dv_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles dv.ListChanged
'Strip the DESC off the dv.Sort string if the user has set the order to descending.
'This allows the lblFind.Text to be properly displayed.
If dv.Sort.Substring((Len(dv.Sort) - 4), 4) = "DESC" Then
lblFind.Text = "Enter Search Criteria: " & dv.Sort.Substring(0, Len(dv.Sort) - 5)
Else
lblFind.Text = "Enter Search Criteria: " & dv.Sort
End If
End Sub
Private Sub btnQuitApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuitApp.Click
Application.Exit()
End Sub
Private Sub txtIDSearch_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtIDSearch.GotFocus
dv.Sort = "au_id"
ClearControls()
End Sub
Private Sub txtIDSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtIDSearch.TextChanged
Dim lcStr As String
lcStr = UCase(Trim(Me.txtIDSearch.Text().ToString))
IncrSearch(lcStr, "au_id")
End Sub
Private Sub txtLNSearch_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtLNSearch.GotFocus
dv.Sort = "au_lname"
ClearControls()
End Sub
Private Sub txtLNSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtLNSearch.TextChanged
Dim lcStr As String
lcStr = UCase(Trim(Me.txtLNSearch.Text().ToString))
IncrSearch(lcStr, "au_lname")
End Sub
Private Sub IncrSearch(ByVal lcSearchValue, ByVal lcSortOrder)
'Get the Default View of the DataSet
ds.Tables(0).DefaultView.Sort = lcSortOrder
'Set up the local search variables
Dim i, lnRowCount, lnSize As Integer
Dim lcField, lcSrchTerm, lcReqID As String
Dim bMgr = Me.BindingContext(grdFind.DataSource, grdFind.DataMember)
lnRowCount = ds.Tables(0).DefaultView.Count
lnSize = Len(lcSearchValue)
'Start searching for the lcField value
For i = 0 To lnRowCount - 1
'Get the field value for the row
lcField = UCase(ds.Tables(0).DefaultView(i)(lcSortOrder))
lcSrchTerm = Microsoft.VisualBasic.Left(lcField, lnSize)
If lcSrchTerm = lcSearchValue Then
Dim Position As Integer = ds.Tables(0).DefaultView.Find(lcField)
Me.BindingContext(ds, ds.Tables(0).TableName).Position = Position
bMgr.position = Position
Exit For
End If
Next
End Sub
Private Sub ClearControls()
Me.txtIDSearch.Text = ""
Me.txtLNSearch.Text = ""
End Sub
End Class
-----------------------------------------------------------
Fix any errors.
Here's the way it works:
When a text box gets the focus it sets the sort of the view so that the grid column is displayed in the correct sort order, and then clears out the other text boxes:
dv.Sort = "au_id"
ClearControls()
Then when the text changes, it formats the search string and calls the IncrSearch function with the search string and to set the sort order for the search.
Dim lcStr As String
lcStr = UCase(Trim(Me.txtIDSearch.Text().ToString))
IncrSearch(lcStr, "au_id")
The incremental search function then makes sure the Default View is in the correct sort order. It then sets up the local search variables and binds the grid to the data set.
It then looks through the records, first to get the actual field value, then for the first letter of the search string we are looking for, and if it finds a match, will get it's position, makes sure the grid is pointing to it, and then jumps out of the loop, ready to get the next letter. (Warning, this code doesn't like Nulls in the fields, so for now I just make sure there isn't any in my tables.)
That's it. I hope this will help you all out.
CU
Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102
