Why Does My WebSearchField Not Search My Database?

  1. 3 months ago

    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

    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.

  2. Clifford A

    Jun 3 Pre-Release Testers, Xojo Pro Charlotte, NC, USA

    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. @Clifford Antrim 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.

    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. Clifford A

    Jun 3 Pre-Release Testers, Xojo Pro Charlotte, NC, USA

    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. Clifford A

    Jun 3 Pre-Release Testers, Xojo Pro Answer Charlotte, NC, USA

    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.

  7. 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!

  8. Alberto D

    Jun 3 Pre-Release Testers

    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')

  9. Clifford A

    Jun 3 Pre-Release Testers, Xojo Pro Charlotte, NC, USA

    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.

  10. Markus W

    Jun 3 Pre-Release Testers #JeSuisHuman New Zealand, Auc...

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

  11. Wayne G

    Jun 3 Pre-Release Testers, Xojo Pro New Zealand axisdirect.nz

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

or Sign Up to reply!