Get 1 cell of data out of a SQLite database table

[McJaegs]

I am trying to take just 1 particular cell of data out of a SQLite database and put it into a label. When I do this however I just get "android.database.sqlite.SQLiteCursor@44f31e28" in the label instead of the data. What is making it do this?

 

[Ricky D]

It's nearly impossible to see why it's doing that without seeing your code.

Can you please post it inside code tags then we can help.

I use this to grab sqlite data

assume atable is one of your tables, Field1 is an Int and Id is the primary key

Dim cur As Cursor

cur = mySQL.ExecuteQuery("SELECT Field1 FROM atable WHERE Id=2")
cur.Position = 0
dim f1 As Int
f1 = cur.GetInt("Field1")

Regards, Ricky

 

[McJaegs]

Here is my code.

Dim lvd() As String
lvd = Value 
Dim cursor1 As Cursor
   
cursor1 = RadioSQL.ExecQuery("SELECT _id From " & CitySpinner.SelectedItem & " WHERE Name LIKE '" & lvd(1) & "%' ") 
   
IDResults.Text = cursor1

 

[Ricky D]

Where is Value dimmed?

After the cursor1 = line

you need to add

cursor1.Position = 0

before you try to do anything with it.

Regards, Ricky

 

[McJaegs]

Ok, when I change my code to this

Dim lvd() As String
lvd = Value 
Dim cursor1 As Cursor
   
cursor1 = RadioSQL.ExecQuery("SELECT _id From " & CitySpinner.SelectedItem & " WHERE Name LIKE '" & lvd(1) & "%' ") 
cursor1.Position = 0
Dim string1 As String
string1 = cursor1.GetString("_id")
IDResults.Text = string1

I get a error when I try to set string1 equal to the cursor. The error says "LastExcpetion android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0"

 

[Ricky D]

it looks like there are no records being returned.

test if there are rows in the cursor by

If cursor1.RowCount>0 Then
'do cursor related code
Else
    Msgbox("No record found", "")
End If

Regards, Ricky

 

[timo]

You just showed the pointer, not the record.

cursor1 = Main.RadioSQL.ExecQuery("SELECT * FROM yourTableName etc.....
   NumRows=cursor1.RowCount
          If NumRows = 1 Then
            cursor1.Position = 0
            IDResults.Text =cursor1.GetString("yourTableName")
         Else
           Log("more then 1 or 0 records; modify your code")
         End If
   cursor1.Close

 

[timo]

opss...while posting just had some responses

 

[McJaegs]

it looks like there are no records being returned.

test if there are rows in the cursor by

If cursor1.RowCount>0 Then
'do cursor related code
Else
    Msgbox("No record found", "")
End If

Regards, Ricky

Ok, so there aren't any records being returned. Is my query wrong or would there be some other reason for this? I know that there is data in those rows

Also to answer your previous question about where Value is dimmed

Sub ListView1_ItemClick (Position As Int, Value As Object)

 

[Ricky D]

What's in Value(1)?

Regards, Ricky

 

[Erel]

Note that you can use SQL.ExecQuerySingleResult if you only need a single result. It is simpler.

 

[McJaegs]

What's in Value(1)?

Regards, Ricky

That is the second label in my list view

Also, when I try to get a single result nothing is displayed in the label.

 

[Ricky D]

Now you to need to zip up the project including the database so we can see what's happening.

Regards, Ricky

 

[McJaegs]

Now you to need to zip up the project including the database so we can see what's happening.

Regards, Ricky

Ok, here is my project folder. The database is in the files folder. For some reason it isn't returning any records. Hopefully you guys can figure this out. I would like to get it working because I am supposed to present my progress on the app on friday.

The more that is done, the better. Thanks.

 

[eps]

I would break it down.

First execute the SQL on it's own to prove it works and data is returned by the query.
For values, just plug in some which you think are correct.

Once you've established the Sql works then worry about the B4A side.

Make sure values being passed into the query are as expected. Possibly try to simplify the SQL if only to prove the data.

Along the way the penny should drop...

 

[Ricky D]

I've just run your code.

What are you expecting in lv?

The name like The Loop or it's frequency 97.9 FM ?

If I change it to look for The Loop it works, if it's looking for 97.9 FM it doesn't

If you are looking for the name like The Loop then change the query to search on lv(0) instead of lv(1)

Doing that returned station id WLUP in the IdResults label.

regards, Ricky

P.S Looking at your database this is definitely the change needed for you to progress

 

[McJaegs]

I've just run your code.

What are you expecting in lv?

The name like The Loop or it's frequency 97.9 FM ?

If I change it to look for The Loop it works, if it's looking for 97.9 FM it doesn't

If you are looking for the name like The Loop then change the query to search on lv(0) instead of lv(1)

Doing that returned station id WLUP in the IdResults label.

regards, Ricky

Well I am looking for the frequency because not all of the radio stations have a name in the database, but they all have a frequency.

 

[Ricky D]

Ok.

Change the cursor1 line to

   cursor1 = RadioSQL.ExecQuery("SELECT _id From " & CitySpinner.SelectedItem & " WHERE Frequency LIKE '" & lvd(1) & "%' ")

that seems to fix it

regards, Ricky

 

[McJaegs]

Ok.

Change the cursor1 line to

   cursor1 = RadioSQL.ExecQuery("SELECT _id From " & CitySpinner.SelectedItem & " WHERE Frequency LIKE '" & lvd(1) & "%' ")

that seems to fix it

regards, Ricky

Wow.... I feel really dumb now. That is such an obvious mistake. Thanks for your help.

 

[Ricky D]

Don't worry - we've all done this kind of stuff in our time

Regards, Ricky