Struggling with sqlLite and Spinner control

[Dman]

Hi all, I am a very newbie so forgive my ignorance but I am trying to learn.

I am trying to populate a spinner control with values from an sqllite db.

Here is the structure of my db.

Name:test.db
1 table named customers
6 fields named:
id
name
street
city
state
zip

Here is my code. When I run the debugger, it gives me a "customer table not found" error.

I know it is something stupid on my part but I can't figure it out.

Sub Activity_Create(FirstTime As Boolean)
   

    Activity.LoadLayout("Invoice")

    If File.Exists(File.DirRootExternal, "test.db") = False Then
      File.Copy(File.DirAssets, "test.db", File.DirRootExternal, "test.db")
   End If
            
   SQL1.Initialize(File.DirRootExternal, "test.db", True)   
            
   PopulateSpinner

End Sub

Sub PopulateSpinner

    dbCursor = SQL1.ExecQuery("SELECT id FROM customers")

   For I = 0 To dbCursor.RowCount - 1
     DoEvents
       dbCursor.Position = I
      spnId.Add(dbCursor.GetString("id"))
   Next   

End Sub

 

[tremara1]

check sd

Is there an sd available for this..try checking for external available first and if not copy db to dirInternal and work from there instead of DirDefaultExternal...just a thought.

 

[Dman]

Well duh!

Newbies, don't you love us?

Thanks a lot, that worked. I'm sure I will have a lot more questions than answers for a while.

 

[Erel]

Check DBUtils it will help you with such tasks: DBUtils - Android databases are now simple!

 

[Dman]

I tried that but I think I am missing something as you can see in the picture and I haven't figured out what it is yet. I thought that if something is red like that something is missing.

 

[Erel]

The version variable is not declared so the IDE paints it in red. You can add:

Dim version As Int

Though it should also work now.

 

[mc73]

I tried that but I am missing something as you can see in the picture and I haven't figured out what it is yet.

This is an integer representing the version of your database. Perhaps you mean something else?

 

[Dman]

It doesn't give an error when compiling but I didn't now if it would work or not. Like I said I am very new. I have this code for the spinner itemclick sub and it doesn't populate the label so I figured that was the issue.

Sub spnId_ItemClick (Position As Int, Value As Object)


   Dim m As Map
   m = DBUtils.ExecuteMap(SQL1, "SELECT street FROM customers WHERE id = ?", Array As String(Value))
   If m = Null Then 'Null will return if there is no match
      lblAddress.Text = "N/A"

   Else
      lblAddress.Text = m.Get("street") 'keys are lower cased!
   End If




End Sub

 

[Erel]

Put a breakpoint in this sub and see which values return from ExecuteMap.

 

[Dman]

I'm not sure what you mean.
I put a msgbox(m.get("street","") on there and it returns a C but that is it.

In the records, I have id's of c1, c2 and names of customer1, customer2 and then the street addresses and that is it.

 

[mc73]

Where are you dimming lbladdress?

 

[Dman]

OK I figured it out. My problem wasn't the retrieving, it was the way that I saved them. I forgot to add the .text after most of the editboxes when I saved.

It wasn't showing on this page when I retrieved them because I had a white label with white text. Another duh moment.

Thanks guys!