Search Code Not Working

[ReportsNerd]

Hi, I have been using a code example I found to search the access db and populate 3 text boxes on my form, but it does not appear to be working correctly. This code is returning the wrong record Im searching on. Not sure why. the item mappings are good with the text boxes, as they appear to be mapping to the fields in the database correctly. I honestly dont understand this loop construct (Btn_Seek) on the dataset and I think it may be wrong. Help appreciated. Thanks.

Public Class Menu
Dim dbProvider As String
Dim dbSource As String
'Dim con As New OleDb.OleDbConnection
Dim Sql As String = "SELECT * FROM DEBTOR"
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim i As Integer
Dim len As Integer
Dim ind As Integer = 0
Dim arrNumbers() As String
Dim no As Integer

Private Sub Menu_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
'dbSource = " C:\Users\Lynne\documents\visual studio 2010\Projects\DT\DT\TestDB1.accdb "
'con.ConnectionString = dbProvider & dbSource
Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Lynne\documents\visual studio 2010\Projects\DT\DT\TestDB1.accdb'")
con.Open()
da = New OleDb.OleDbDataAdapter(Sql, con)
da.Fill(ds, "Debtors")
len = da.Fill(ds, "Debtors")
End Sub

Private Sub BtnSeek_Click(sender As System.Object, e As System.EventArgs) Handles BtnSeek.Click
Dim sql1 = InputBox("Name:", "Search", "Anderson")
Dim no As Integer
Dim found As Boolean = False
For no = 0 To len - 1
If ds.Tables("Debtors").Rows(no).Item(2).ToString.ToLower = sql1.ToString.ToLower Then
i = no
nav()
found = True
End If
Next
If (found = False) Then
MsgBox("Search Not Found")
End If
End Sub

Private Sub nav()
txtName1.Text = ds.Tables("Debtors").Rows(1).Item(2)
txtAddr1.Text = ds.Tables("Debtors").Rows(1).Item(4)
txtAddr2.Text = ds.Tables("Debtors").Rows(1).Item(5)
End Sub

 

[IanRyder]

Hi,

The reason why you cannot see your problem is because that code is horrible to read and understand. When coding, you must remember to use variable declarations that describe what their function is, thereby when you get an issue like this you can quickly and easily pinpoint the problem.

As it is, I can see the issue straight off, but if you give me a half hour I am going to rewrite this horrible code into something more readable so that you can pinpoint the error on your own and learn the importance of correct naming conventions when programming.

I know that does not really help at the moment but keep an eye out for my next post.

Cheers,

Ian

 

[IanRyder]

Hi,

Please compare this code with the code that you posted:-

Public Class Form1
 
  Dim mySelectionString As String = "SELECT * FROM DEBTOR"
  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='C:\Users\Lynne\documents\visual studio 2010\Projects\DT\DT\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
    Dim 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 = mySearchString.ToString.ToLower Then
        TheCorrectRowIndexOfTheTable_BasedOnTheSearchCriteria = CurrentTableRowNumber
        DisplaySearchResults()
        Exit For
      End If
    Next
    MsgBox("Search Not Found")
  End Sub
 
  Private Sub DisplaySearchResults()
    txtName1.Text = myDataSet.Tables("Debtors").Rows(1).Item(2).ToString
    txtAddr1.Text = myDataSet.Tables("Debtors").Rows(1).Item(4).ToString
    txtAddr2.Text = myDataSet.Tables("Debtors").Rows(1).Item(5).ToString
  End Sub
End Class

I have deliberately not made this any better or more efficient than what was posted other than removing unused lines of code which just confuse the issue and adding an Exit For in your For loop.

As you will see I have NOT added any additional comments and I am hoping that from reading through the code you should be able to see what the problem is based on the fact that things are now a bit more understandable. To give you a small hint about what is wrong with the code, look at the statements in the DisplaySearchResults subroutine?

Hope that helps and good luck.

Cheers,

Ian

BTW, when posting code please do remember to add code tags for ease of reading.

 

[ReportsNerd]

Hi Ian, thank you so much! Based on your advice, this is what I did. Works great:

Private Sub DisplaySearchResults()
        txtName1.Text = myDataSet.Tables("Debtors").Rows(TheCorrectRowIndexOfTheTable_BasedOnTheSearchCriteria).Item(2).ToString
        txtAddr1.Text = myDataSet.Tables("Debtors").Rows(TheCorrectRowIndexOfTheTable_BasedOnTheSearchCriteria).Item(4).ToString
        txtAddr2.Text = myDataSet.Tables("Debtors").Rows(TheCorrectRowIndexOfTheTable_BasedOnTheSearchCriteria).Item(5).ToString
    End Sub

Now, I just have a few more questions regarding this if I may.

1. Im getting the "Search not found" dialog even though it is doing the seach correctly and returning the correct data to the form. Most of the data in the column Im searching on was stored in all caps. Not sure if that is a factor.

2. How do I use a LIKE % instead of having the user search on an exact text string match?

3. Since I eventually want to deploy this solution, how can I shorten my connection string path to something more relative? The access database is going to be in the soltuion, so I should not have to fully quality the path, right?

Thanks

 

[IanRyder]

Hi,

Here are the answers to your additional questions:-

1) My apologies, I accidentally removed one of the variables handling this condition when rewriting the code. Have a look here:-

For CurrentTableRowNumber = 0 To TotalRowsInDataTable - 1
  If myDataSet.Tables("Debtors").Rows(CurrentTableRowNumber).Item(2).ToString.ToLower = 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

Notice the adding back of the Found variable and testing the condition of the Found variable before showing the search error message.

2) When using Wildcards as search criteria with Databases you need to use this in conjunction with a WHERE clause. i.e:-

Dim mySelectionString2 As String = "SELECT * FROM DEBTOR Where DebtorName Like 'N*'"

Here the SQL statement will return all records where a Field called DebtorName starts with the Letter "N". You will then probably want to build the Where clause in your project as you need.

In earlier versions of access, Wildcards were defined with the asterix, "*", and question mark, "?", characters. Just check that the version you are using has not changed to use the Percentage "%" character as the wildcard.

3) What you must remember is that when you deploy your solution the Database part of the solution will still be a separate file on the drive where it will be deployed which therefore means you will sill have to fully qualify the path.

That said you can shorten the coding of the connection string to something like:-

Dim myConnectionString2 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Application.StartupPath & "\TestDB1.accdb'")

Hope that all helps.

Cheers,

Ian

 

[ReportsNerd]

Thanks, Ian. That's great. I was able to get everythig working except the like operator. I put the mySearchString variable up in the class level and then set as so:

Public Class Menu
    Dim mySearchString As String
    Dim mySelectionString As String = "SELECT * FROM DEBTOR WHERE NAME1 like '" & mySearchString & "%'"

However, it still only works when the complete value is searched and it ignores the like opertor. I think this is the reason in the BtnSeek_Click (parts of the code):

mySearchString = InputBox("Name:", "Search", "Anderson")
If myDataSet.Tables("Debtors").Rows(CurrentTableRowNumber).Item(2).ToString.ToLower = mySearchString.ToString.ToLower Then

Is there any way around that? Thanks.

 

[IanRyder]

Hi,

As to you your first point with your SQL query, I have just checked the Net on later versions of Access, and I can confirm that I have answered your question in my previous post. You need to re-read that post.

For your second point, if you want to use a similar form of search criteria in the DataSet using your current logic you can say:-

myDataSet.Tables("Debtors").Rows(CurrentTableRowNumber).Item(2).ToString.ToLower.StartsWith(mySearchString.ToString.ToLower)

Hope that helps.

Cheers,

Ian

 

[ReportsNerd]

That does help! Thanks, Ian. Works great.