Why Does My WebSearchField Not Search My Database?

Databases
1

I’ve been following the example of EddiesElectronics where the search field contents is able to search the database and return values to the list box. However, I’ve had no luck In Implementing this myself.

I have three methods in the page as follows.

FindPatientsByName

Dim stmt As SQLitePreparedStatement 
stmt = session.db.Prepare("SELECT Forename, Surname, DateOfBirth, Address FROM Patients WHERE Forename LIKE ? OR Surname LIKE ? ORDER BY Forename, Surname")
stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
stmt.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
stmt.Bind(0, SearchName+"%")
stmt.Bind(1, SearchName+"%")

Dim rs As RecordSet = stmt.SQLSelect

Return rs

PatientSearchUpdate

Dim patientRS As RecordSet
patientRS = FindPatientsByName(searchText)

If patientRS <> Nil Then
  lbSearch.DeleteAllRows
  
  For i As Integer = 1 To patientRS.RecordCount
    lbSearch.AddRow(patientRS.Field("Forename").StringValue + ", " + patientRS.Field("Surname").StringValue)
    patientRS.MoveNext
  Next
  
  lbSearch.ListIndex = 0
  
  patientRS.Close
End If

LoadPatients

Dim patientRS As RecordSet
patientRS = FindPatientsByName(searchText)

If Session.DB is Nil Then Return

Dim sql As Text = "SELECT Forename, Surname, DateOfBirth, Address FROM Patient ORDER BY Forename"

Dim patients As RecordSet = Session.DB.SQLSelect(sql)

If Session.DB.Error Or patients is Nil Then
  MsgBox("Error: " + Session.DB.ErrorMessage)
  Return
End If

While Not patients.EOF
  lbSearch.AddRow(Patients.field("Forename").StringValue, patients.field("Surname").StringValue, patients.field("DateOfBirth").StringValue, patients.field("Address").StringValue)
  patients.MoveNext
Wend

patients.close

I’ve been trying for hours and nothing I seem to do works. I type into the search bar and the list box doesn’t change at all. I’ve tried to make it as close to the Eddies Electronics example as possible but it’s not worked out. My ‘LoadPatients’ Is rather different to the example counterpart, but that’s because my ListBox would not load any data at all when I followed the examples version. Would really appreciate if someone can tell me why it’s not working.

Thanks

2

If nothing happens when you type in the search box, then the first place I would check is the TextChanged event handler of your WebSearchField. You should be calling PatientSearchUpdate(Me.Text) from within that handler.

3

Thank you for replying! I already have ‘PatientSearchUpdate(Me.Text)’ in my TextChanged event handler, but to no avail. I can’t think where the issue might be.

4

so the listbox populates with the full list at launch, and when you type in the search field the listbox remains unchanged (that is, it still displays the full list)?

5

Yes, that’s exactly what happens. On Launch the listbox is populated with data from the Database. But typing into the search doesn’t add or remove anything.

6

Then my next guess is that in PatientSearchUpdate, patientRS is NIL. If it wasn’t NIL, then at the very least the listbox would be emptied of all rows.

A NIL recordset usually indicates a problem with the database (i.e.- the SELECT statement produced an error or there is no connection to the DB). You can check session.db.ErrorMessage right after you execute the prepared statement or right after you call FindPatientsByName to see what it says.

8

I feel very stupid now, having spent hours wondering what’s wrong. You are right, the SQL statement was wrong, I had written ‘FROM Patients instead of FROM Patient’. One ‘s’ caused hours of misery.

Thank You So Much, for helping me realise my mistake!

9

Don’t feel bad, I bet that several people that saw your code didn’t catch the difference (I didn’t, until you posted about the ‘s’)

10

its nothing I haven’t done plenty of times myself. But good debugging skills will help you find these kind of errors pretty quickly. And don’t forget to check the things that couldn’t possibly be wrong - because a lot of times that’s where the problem is.

11

Wasn‘t there a recommended rule that column names should ALWAYS be singular?

12

Another great example of using exceptions rather than error codes. Looking forward to API 2.0!