DBUtils.ExecuteListView parameter for the WHERE clause is not working

Question

I would like to use the value from an EditText box to filter a ListView.

Can you look at my coding and let me know what else I need to do to get it to work?

So far it only returns 0 rows.

Sub ButtonSearchFilterEventHandler_Click
  ' Populate the list.
  '-------------------
  DBUtils.ExecuteListView(SQL, "SELECT Id, ResultDescription " & _
    "FROM VisitResultTypes " & _
    "WHERE ResultDescription = ? " & _
    "ORDER BY ResultDescription", _
    Array As String(EditTextResultDescription.Text), 0, _
    ListViewResults, True)
End Sub

Additional Sub routines:

Sub Activity_Create(FirstTime As Boolean)
  SQL.ExecNonQuery("CREATE TABLE VisitResultTypes " & _
    "(Id INTEGER PRIMARY KEY, ResultDescription TEXT)")
End Sub

This one works. It shows all table rows:

Sub PopulateTheListView
 ' Populate the list.
 '-------------------
 DBUtils.ExecuteListView(SQL, "SELECT Id, ResultDescription " & _
    "FROM VisitResultTypes " & _    
    "ORDER BY ResultDescription", _
    Null, 0, ListViewResults, True)
End Sub

I tried this but it returns all rows no matter what I type into the EditText box:

Sub ButtonSearchFilterEventHandler_Click
  ' Populate the list.
  '-------------------
  DBUtils.ExecuteListView(SQL, "SELECT Id, ResultDescription " & _
    "FROM VisitResultTypes " & _
    "WHERE ResultDescription LIKE ? " & _
    "ORDER BY ResultDescription", _
    Array As String(EditTextResultDescription.Text & "%"), 0, _
    ListViewResults, True)
End Sub

This one adds the data into the table:

SQL.ExecNonQuery2("INSERT INTO VisitResultTypes " & _ 
  "(Id, ResultDescription) " & _
  "VALUES " & _
  "(?, ?)", Array As Object(Null, EditTextResultDescription.Text))

Your code appears to be fine, from what I can see. Are you sure that the `ResultDescription` you're passing exists, and that they're exactly the same? (You're using `=` and not `LIKE` with a wildcard, so things like embedded or trailing spaces may matter - I'm not familiar with SQLite yet.) Can you edit your question to add the database definition for columns used in your query, along with a sample of some rows and the value you're using as a parameter? — Ken White, Nov 19 '11 at 18:20
Sure. I will try some experimenting with the "LIKE" keyword first to see if that works. — Emad-ud-deen, Nov 19 '11 at 19:04
I added the database definitions coding. Hopefully you can spot my error. I will try doing the query with the parameter and adding my own WHERE clause to see if it does anything. — Emad-ud-deen, Nov 19 '11 at 19:27
Please see the update at the top of this post. I got it going by adding another TextEdit box and changed the call to ExecuteListView. — Emad-ud-deen, Nov 19 '11 at 19:50
Nice job. I upvoted your original question. Are you aware that, after enough time has passed, you can post your own answer with the solution and accept it? This lets people know that it's been answered more clearly. — Ken White, Nov 19 '11 at 19:57
Thanks for the information. I answered the question for others. — Emad-ud-deen, Nov 19 '11 at 23:05

score 1 · Accepted Answer · edited Nov 19 '11 at 23:45

1

Update - Issue Solved:

I added another EditText box and used this to get the filtering to work:

DBUtils.ExecuteListView(SQL, "SELECT Id, ResultDescription " & _
  "FROM VisitResultTypes " & _
  "WHERE ResultDescription LIKE " & "'" & EditTextSearchFilter.Text & "%' " & _
  "ORDER BY ResultDescription", Null, 0, ListViewResults, True)

edited Nov 19 '11 at 23:45

Ken White

123,280
14
225
444

answered Nov 19 '11 at 23:04

Emad-ud-deen

4,734
21
87
152

DBUtils.ExecuteListView parameter for the WHERE clause is not working

1 Answers1