phone book with sql lite

[fifiddu70]

Hello everyone, I would build a phone book consists of three editext: name, phone, address book, should work with sql lite, I'm not very good with the database, I have this code, the save button, memorize the data in the database, while the spinner call the selected item, the problem is that if a person memorize memorize it and then another, in the selection of the spinner always visualize a person and not the one corresponding to the storage, why?
I'll post some of my code and picture of the project.

Sub Activity_Create(FirstTime As Boolean)
   
   Activity.LoadLayout("totems")
   p.SetScreenOrientation(1)
   
   If File.Exists(File.DirInternal, "totem.db") = False Then 
   File.Copy(File.DirAssets, "totem.db", File.DirInternal, "totem.db") 
   End If
   dbSQL.Initialize(File.DirInternal, "totem.db", True)

   PopulateSpinner
   
End Sub

Sub PopulateSpinner
   spagent.Clear
   dbCursor = dbSQL.ExecQuery("SELECT * FROM Field2")
    For I = 0 To dbCursor.RowCount - 1
   DoEvents
   dbCursor.Position = I
   spagent.Add(dbCursor.GetString("Data2"))
   Next   
   dbCursor.Close
End Sub

Sub agent_Click
   dbSQL.ExecNonQuery2("INSERT INTO Field2 VALUES (?)", Array As String(txtname.Text))
   dbSQL.ExecNonQuery2("INSERT INTO Field3 VALUES (?)", Array As String(txtlastname.Text))
   dbSQL.ExecNonQuery2("INSERT INTO Field4 VALUES (?)", Array As String(txtphone.Text))
   spagent.Add(txtlocale.Text)
   txtname.Text = ""
   txtlastname.Text=""
   txtphone.Text=""
   dbCursor.Close
End Sub

Sub spagent_ItemClick (Position As Int, Value As Object)
   txtname.Text = ""
   txtlastname.Text = ""
   txtphone.Text= ""
   dbCursor = dbSQL.ExecQuery("SELECT * FROM Field2")
   For I = 0 To dbCursor.RowCount - 1
   DoEvents
   dbCursor.Position = I
   txtname.Text = (dbCursor.GetString("Data2"))
   Next   
   dbCursor.Close
   dbCursor = dbSQL.ExecQuery("SELECT * FROM Field3")
   For I = 0 To dbCursor.RowCount - 1
   DoEvents
   dbCursor.Position = I
   txtlastname.Text = (dbCursor.GetString("Data3"))
   Next   
   dbCursor.Close
   dbCursor = dbSQL.ExecQuery("SELECT * FROM Field4")
   For I = 0 To dbCursor.RowCount - 1
   DoEvents
   dbCursor.Position = I
   txtphone.Text = (dbCursor.GetString("Data4"))
   Next   
   dbCursor.Close
End Sub

 

[mangojack]

In your code it appears you are creating a separate table (Field2,Field3,Field4) for each record(name,lastname,phone) Why not just one Table ?
When you click a spinner entry you then loop thru all records and always display the last entry in each Table.

This might help ..

Sub Activity_Create(FirstTime As Boolean)
    
    Activity.LoadLayout("totems")
    p.SetScreenOrientation(1)
    
   If File.Exists(File.DirInternal, "totem.db") = False Then 
    File.Copy(File.DirAssets, "totem.db", File.DirInternal, "totem.db") 
    End If
    dbSQL.Initialize(File.DirInternal, "totem.db", True)
   
   'dbSQL.ExecNonQuery("CREATE TABLE IF NOT EXISTS MyTable (name TEXT, lastname TEXT, phone TEXT)")

    PopulateSpinner
    
End Sub

Sub PopulateSpinner
    
   Dim Fname,Lname,Pnum As String
   
   spagent.Clear
    dbCursor = dbSQL.ExecQuery("SELECT * FROM MyTable")
      
   For I = 0 To dbCursor.RowCount - 1
       dbCursor.Position = I
       'Fname = dbCursor.GetString("name")
      Lname = dbCursor.GetString("lastname")
       'Pnum = dbCursor.GetString("phone")
      
      spagent.Add(Lname)           '.Add(Fname & "   " & Lname & "   " & Pnum)
    Next    
   
    dbCursor.Close
   
End Sub

Sub agent_Click
       
   dbSQL.ExecNonQuery2("INSERT INTO MyTable VALUES  (?, ?, ?)" , Array As String (txtname.Text, txtlastname.Text, txtphone.text))
   ' spagent.Add(txtlocale.Text) ??????
   
   txtname.Text = ""
    txtlastname.Text=""
    txtphone.Text=""
   
   PopulateSpinner
   
End Sub

Sub spagent_ItemClick (Position As Int, Value As Object)
    
    dbCursor = dbSQL.ExecQuery("SELECT * FROM MyTable WHERE lastname = '"& Value &"' ")   ' ...    "  '  &  Variable  &  '  "
    
    'presumes only single entry with this last name
    dbCursor.Position = 0
    txtname.Text = (dbCursor.GetString("name"))
    txtlastname.Text = (dbCursor.GetString("lastname"))
    txtphone.Text = (dbCursor.GetString("phone"))
   
    dbCursor.Close
   
End Sub

If you haven't already read ..
SQL tutorial/demos
Erels SQL Tutorial

Cheers mj

 

[mrjaw]

I think your problem is not B4A else acknowledgement database