Android Question Combines SQLLite with SearchView

[A Z M JANNAT UL KARIM]

Hi,

I had tried with the following examples to create a searchable listview from where data were retrieved from the SQLLite tables.

https://www.b4x.com/android/forum/t...es-contactsutils-with-searchview.31372/page-2

Following are the codes that I am using and successfully added to the panel. My problem is when I write on search text box nothing shows in SearchList !!! I mean searching is not working and the listbox blanked. If anyone have any sample code will be helpfull for me.

Thanks

Sub Globals
    Dim srvwRetailer As SearchView
    Private retList As List
End Sub

Sub FILL_LIST
        Dim cur As Cursor
        cur = Starter.sqlLiteDB.ExecQuery("SELECT PARTY_NAME, PARTY_SMS_CODE FROM tblCustomer WHERE PARTY_TYPE = 'R' ORDER BY PARTY_NAME")
        retList.Initialize
    For i = 0 To cur.RowCount - 1
        cur.Position = i
        Dim it As Item
        it.Title = cur.GetString("PARTY_NAME")
        it.Text = cur.GetString("PARTY_SMS_CODE")
        it.SearchText = cur.GetString("PARTY_NAME")
        retList.Add(it)
    Next
    srvwRetailer.SetItems(retList)
End Sub

Also I have changed the AddItemsToList for AddSingleLine

lv.AddSingleLine2(it.Title, it.Text)

 

[A Z M JANNAT UL KARIM]

Hi,

I think I manage to solve the issue. Just changing the following line from

it.SearchText = cur.GetString("PARTY_NAME")

To

it.SearchText = cur.GetString("PARTY_NAME").ToLowerCase

Makes the search. I just forget about the Case Sensivity.

Thanks

 

[eps]

If that's the case then ideally you need to remove all case sensitivity... (it was a bit hard to work out what you were doing - especially from the limited code you posted - it doesn't relate very well to the update you made..)

e.g. adjust the text entered so that behind the scenes...

Text entered, John Smith or john smith or JOHN SMITH

becomes john smith

Then make sure that the matching field in your SQL query - not sure how this is structured, but also case insensitive as well.. otherwise if it's held as John Smith on the database you will never match on it, even if your User enters John Smith.

e.g. use LOWER or LIKE

But still not sure what your SQL looks like when you execute the fetch of a single or multiple values...

 

[eps]

or potentially match on case sensitive values and if that return nothing then try a case insensitive SELECT and possibly a shortened SELECT LIKE 'surname' for instance if that returns nothing... That might present the best results and most desired. HTH

 

[A Z M JANNAT UL KARIM]

Hi EPs,

Thank you for your reply. Actually I am just populating the SearchView from the SQLLite database nothing more.

That is where I like Erel's code very much. The code behind the SearchView do all the stuff. I just need to pass the data as it requires and the Job done. My confusion comes because of altering code from Contact code where he is using some kind of indexes which is unclear to me.

Thank you again for your kind support.

 

[eps]

Ah right,

Which part is unclear? Sorry - I didn't realise you were querying the Contacts.. or are you just using that code to query your own database?

Indexes usually just speed up database access. If used in the proper database context.

 

[LucaMs]

You could add a code similar to this (it is a property of a my class):

' Sets/Gets the type of comparison of the text.
' It is False by default, then not case sensitive.
Public Sub setCaseSensitive(CaseSensitive As Boolean)
    mCaseSensitive = CaseSensitive
    If mCaseSensitive Then
        mDB.ExecNonQuery("PRAGMA case_sensitive_like=ON")
    Else
        mDB.ExecNonQuery("PRAGMA case_sensitive_like=OFF")
    End If
End Sub

Public Sub getCaseSensitive As Boolean
    Return mCaseSensitive
End Sub

 

[A Z M JANNAT UL KARIM]

Hi eps,

Thank you for your concern. After you ask I re-examine the code and found that those lines are of ContactUtils part and not required for my current project.

Hi LucaMs,

Thank you for sharing your code.

 

[Mahares]

You could add a code similar to this (it is a property of a my class):

@LucaMs:
Luca, you are a genius with an uncanny wit. Sometimes, I cannot tell if you are joking or serious in your posts. But, could he just add: COLLATE NOCASE at the end of his statement and would not have to worry about whether the data in the table is stored as upper, lower or mixed case. Something like this:

cur = Starter.sqlLiteDB.ExecQuery("SELECT PARTY_NAME, PARTY_SMS_CODE FROM tblCustomer WHERE PARTY_TYPE = 'R' " _
& " ORDER BY PARTY_NAME  COLLATE NOCASE")

 

[LucaMs]

Sometimes, I cannot tell if you are joking or serious in your posts.

It's easy: when you read in my post something like: , well that post is very serious.

Both method works; I think that you should use "mine" because:

1) you set it once and then I think it should be faster;
2) you don't need to change old queries.

Do you see some above? No? So I'm joking!