Android Example Filter the Contents of a ListaVieW By Sql code

Discussion in 'Tutorials & Examples' started by Lowegaterecords, Sep 28, 2018.

  1. Lowegaterecords

    Lowegaterecords Member Licensed User

    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.
    Code:
    Sub EditText1_TextChanged (Old As String, New As String)
    Dim ListRow(1000As 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
     
    Last edited: Sep 28, 2018
    Alexander Stolte and mendiburen like this.
  2. ronell

    ronell Well-Known Member Licensed User

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

    Code:
    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
     
  3. Erel

    Erel Administrator Staff Member Licensed User

    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:
    Code:
    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
     
    mondiq, Myr0n, jimmyF and 2 others like this.
  4. ronell

    ronell Well-Known Member Licensed User

    another entry in my bookmark :D
     
    jimmyF likes this.
  5. Lowegaterecords

    Lowegaterecords Member Licensed User

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice