Android Example Filter the Contents of a ListaVieW By Sql code

This example shows how to filter the contents of a database and load the results in a listview using an edittext. for each letter entered in edittext the results are shown in the listview. It could be useful for someone.

Sub EditText1_TextChanged (Old As String, New As String)
Dim ListRow(1000) As String
dim Cursor1 As Cursor
ListView1.Clear

Cursor1=s.ExecQuery("SELECT   Col1  FROM  Table1 WHERE  Col1 LIKE('" & "%" & "')")

If Cursor1.RowCount > 0 Then
For i= 0 To Cursor1.RowCount-1
Cursor1.Position =i
ListRow(Cursor1.Position) = Cursor1.GetString("Col1")

If ListRow.Contains(EditText1.Text) then
ListView1.AddSingleLine(ListRow(i))
EndIf
Next
Cursor1.Close
End If
EndSub

 

[ronell]

you dont need the listrow in the code.. you can fill the listview inside the iteration

Sub txtsearch_TextChanged (Old As String, New As String)
    cursor = sql.ExecQuery("SELECT first_name FROM user where first_name LIKE '%"&New&"%'")
    If cursor.RowCount > 0 Then
        For i = 0 To cursor.RowCount - 1
            cursor.Position = i
            ListView1.AddSingleLine(cursor.GetString("first_name"))
        Next
    Else
        ListView1.AddSingleLine("no data...")
    End If
End Sub

 

[Erel]

There are several mistakes here.

1. Cursor is not closed.
2. Cursor should be a local variable.
3. Better to use ResultSet instead of Cursor. ResultSet is cross platform.
4. Code will fail with an error if New includes an apostrophe.
5. If the database is large then this query will be slow as it needs to check each item every keystroke. You can improve it by using ExecQueryAsync. Or better switch to SearchView which creates an index.

This code fixes some of the issues:

Sub txtsearch_TextChanged (Old As String, New As String)
   ListView1.Clear
   Dim rs As ResultSet = Starter.sql.ExecQuery2("SELECT first_name FROM user where first_name LIKE ?", Array As String("%" & New & "%"))
   Do While rs.NextRow
       ListView1.AddSingleLine(rs.GetString("first_name"))
   Loop
   rs.Close
   If ListView1.Size = 0 Then
       ListView1.AddSingleLine("no data...")
   End If
End Sub

 

[ronell]

There are several mistakes here.

1. Cursor is not closed.
2. Cursor should be a local variable.
3. Better to use ResultSet instead of Cursor. ResultSet is cross platform.
4. Code will fail with an error if New includes an apostrophe.
5. If the database is large then this query will be slow as it needs to check each item every keystroke. You can improve it by using ExecQueryAsync. Or better switch to SearchView which creates an index.

This code fixes some of the issues:

Sub txtsearch_TextChanged (Old As String, New As String)
   ListView1.Clear
   Dim rs As ResultSet = Starter.sql.ExecQuery2("SELECT first_name FROM user where first_name LIKE ?", Array As String("%" & New & "%"))
   Do While rs.NextRow
       ListView1.AddSingleLine(rs.GetString("first_name"))
   Loop
   rs.Close
   If ListView1.Size = 0 Then
       ListView1.AddSingleLine("no data...")
   End If
End Sub

another entry in my bookmark

 

[WebQuest]

Hello Roonell thanks for the note. I agree with you in simplifying the ListRow function, but I found it quicker to access a dynamic list containing the row. This is the example code in which I use (SingleLine) in the full version of my app I use a type variable (String, String, Bitmap) and load (FristRow, SecondRow, Picture) with TwoLineAndBitmap.