Expand Record Search Capabilities

[ReportsNerd]

Hi, I need to expand the search capabilities of my app. Right now, I'm using an input box to collect name search criteria. But now, I need to be able to search on Name1, Name2 and customer number. Should I use a modal form instead in order to have more control or can I still use an input box like the code below?

I also need the capability to move next and back in the record set. So after a search on Name1, I need to be able to click the Next button and navigate to the next match in the record set. Note my Select statement is ordered.

Help/suggestions appreciated. Thanks

Public Class Menu
    Dim mySearchString As String
    Dim mySelectionString As String = "SELECT * FROM DEBTOR WHERE NAME1 like '" & mySearchString & "%' ORDER By NAME1"
    Dim myDataSet As New DataSet
    Dim daDebtors As OleDb.OleDbDataAdapter
    Dim TheCorrectRowIndexOfTheTable_BasedOnTheSearchCriteria As Integer
    Dim TotalRowsInDataTable As Integer

Private Sub Menu_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim myConnectionString As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Application.StartupPath & "\TestDB1.accdb'")
        myConnectionString.Open()
        daDebtors = New OleDb.OleDbDataAdapter(mySelectionString, myConnectionString)
        TotalRowsInDataTable = daDebtors.Fill(myDataSet, "Debtors")
    End Sub

Private Sub BtnSeek_Click(sender As System.Object, e As System.EventArgs) Handles BtnSeek.Click
        mySearchString = InputBox("Name:", "Search", "Anderson")
        Dim CurrentTableRowNumber As Integer
        Dim found As Boolean = False
        For CurrentTableRowNumber = 0 To TotalRowsInDataTable - 1
            If myDataSet.Tables("Debtors").Rows(CurrentTableRowNumber).Item(2).ToString.ToLower.StartsWith(mySearchString.ToString.ToLower) Then
                TheCorrectRowIndexOfTheTable_BasedOnTheSearchCriteria = CurrentTableRowNumber
                DisplaySearchResults()
                found = True
                Exit For
            End If
        Next
        If Not found Then
            MsgBox("Search Not Found")
        End If
    End Sub

 Private Sub DisplaySearchResults()
        'Main Tab Controls
        txtName1.Text = myDataSet.Tables("Debtors").Rows(TheCorrectRowIndexOfTheTable_BasedOnTheSearchCriteria).Item(2).ToString
        txtName2.Text = myDataSet.Tables("Debtors").Rows(TheCorrectRowIndexOfTheTable_BasedOnTheSearchCriteria).Item(3).ToString

 

[IanRyder]

Hi,

Should I use a modal form instead in order to have more control or can I still use an input box like the code below?

Getting information from user input is down to preference and is really determined by how you want your project to work. From an experience point of view, I would always go with a form, since you can make them look appealing to the user. That said, there is nothing wrong with using InputBox's.

I also need the capability to move next and back in the record set. So after a search on Name1, I need to be able to click the Next button and navigate to the next match in the record set. Note my Select statement is ordered.

The way to go about this is to change your current logic and use a BindingSource between your original data source and the controls that you are using to display your information.

By using a BindingSource you can do the following:-

1) You can Bind your controls to field names within your dataset
2) You can set Filter criteria on the BindingSource to limit the information that you want to display without affecting the contents of the underlying data source
3) You can also use the navigation methods of the BindingSource to move between your records using MoveFirst, MoveLast, MoveNext and MovePrevious

Have a look at this quick example which uses the Northwind database. It gets the Employees table, associates a BindingSource with the table in the DataSet and then on a click of a button it Binds a TextBox to the field LastName in the DataTable, via the BindingSource, and sets a Filter on the BindingSource. On the click of another button the BindingSource moves to the next record in the Filter.

Imports System.Data.SqlClient
 
Public Class Form1
  Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
  Dim daEmployees As New SqlDataAdapter("Select * From Employees", sqlConn)
  Dim DS As New DataSet
  Dim myBindingSource As BindingSource
 
  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    daEmployees.Fill(DS, "Employees")
    DataGridView1.DataSource = DS.Tables(0)
    myBindingSource = New BindingSource(DS, "Employees")
  End Sub
 
  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    With TextBox1
      .DataBindings.Add(New Binding("Text", myBindingSource, "LastName"))
    End With
    myBindingSource.Filter = "LastName Like 'D%'"
  End Sub
 
  Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    myBindingSource.MoveNex()
  End Sub
End Class

This is using SQL, but the principals are exactly the same for Access.

Hope that helps.

Cheers,

Ian

 

[ReportsNerd]

Very cool, Ian! The data binding works for me and I'm able to move next. The only issue I'm having is when I try to do a another search, I get an exception on the line:

With txtName1.DataBindings.Add("Text", myBindingSource, "NAME1")

Error: This causes two bindings in the collection to bind to the same property.
Parameter name: binding

I assume I need to clear out the bindings when performing another search? Here is my code so far:

Public Class Menu
    Dim mySearchString As String
    Dim myConnectionString As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Application.StartupPath & "\TestDB1.accdb'")
    Dim daDebtors As New OleDb.OleDbDataAdapter("Select * From DEBTOR ORDER By NAME1", myConnectionString)
    Dim myDataSet As New DataSet
    Dim myBindingSource As BindingSource
 
Private Sub Menu_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        daDebtors.Fill(myDataSet, "Debtors")
        myBindingSource = New BindingSource(myDataSet, "Debtors")
End Sub

Private Sub BtnSeek_Click(sender As System.Object, e As System.EventArgs) Handles BtnSeek.Click
        mySearchString = InputBox("Name:", "Search", "Anderson")
        DisplaySearchResults()
End Sub

Private Sub DisplaySearchResults()
               With txtName1.DataBindings.Add("Text", myBindingSource, "NAME1")
        End With
        myBindingSource.Filter = "Name1 Like '" & mySearchString & "%'"
End Sub

Private Sub BtnNext_Click(sender As System.Object, e As System.EventArgs) Handles BtnNext.Click
        myBindingSource.MoveNext()

 

[ReportsNerd]

Sorry, I used

[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]txtName1.DataBindings.Clear()
[/SIZE][/FONT][/SIZE][/FONT]

before binding again and that worked great. Thanks!

 

[IanRyder]

Hi,

There is a small logic error in my own example which you have propagated in your own code. My fault and my apologies. It's just the way I was writing the example yesterday.

You only need to set the Binding to the TextBox's and any other controls once. This should be done after the Binding Source is created in the Form Load event. That way when you apply numerous filers you do not need to clear and reset the bindings to the controls.

Hope that helps to clear things up.

Cheers,

Ian