Android Question How to update a value in a SQL database?

[MikeSimpson]

I have created a database that is loaded into my app. One table is named "highscores" where I want to store the "highscore" and "time" As Int.

To read the cell is like this:

Cursor1 = Sql1.ExecQuery("SELECT * FROM highscores")
        Cursor1.Position = level
               
        oldscore = Cursor1.GetInt("score")
        oldtime = Cursor1.GetInt("time")
       
    Cursor1.Close

But how is the code to update a cell? The only code I could find was this:

Sub UpdateEntry
Dim Query As String
Query = "UPDATE persons Set FirstName = ?, LastName = ?, City = ? _
WHERE ID = " & IDList.Get(CurrentIndex)
SQL1.ExecNonQuery2(Query, Array As String(edtFirstName.Text, _
edtLastName.Text, edtCity.Text))
ToastMessageShow("Entry updated", False)
End Sub

But I was not able to change the code so that it worked for me.
I tried this:

Dim Query As String
    Query = "UPDATE highscores SET highscore = ?, time = ? WHERE ID = " & level
   
    Sql1.ExecNonQuery2(Query, Array As String(newhighscore, newbesttime))

But this wasn't working.

I am looking for something like this, if it is possible:

Cursor1 = Sql1.ExecQuery("UPDATE * FROM highscores")
        Cursor1.Position = level
               
  '     Cursor1.Update("score") = newhighscore
  '     Cursor1.Update("time") = newbesttime
       
    Cursor1.Close

I know that this code is totaly wrong, but I think it explane waht I want.
Anyone an idea how to updabe the two cells in an easy way?

 

[Eumel]

Are you sure you wrote the correct cell names ?

You read the highscore cell with:

oldscore = Cursor1.GetInt("score")

But you try to update with:

SET highscore = ?,

Should it be

SET score = ?,

 

[MikeSimpson]

Should it be

SET score = ?,

Yes, you are right, but it still isn't working.

 

[Reviewnow]

I would suggest adding dbutils to your arsenal
http://www.b4x.com/android/forum/threads/dbutils-android-databases-are-now-simple.8475/

Sub updateHighScores(SQL As SQL,highscore As String,time as String,level as String)
    'Create Map of Scores
    Dim ScoreFields as Map
    ScoreFields.Initialize
    ScoreFields.Put("highScore",highscore)
    ScoreFields.Put("time",time)
   
    'Create Where Clause Map
    Dim WhereFields As Map
    WhereFields.Initialize
    WhereFields.Put("ID", Level) '< is level the actual ID ?
    'Do you have a column Called level? Should it be
    'WhereFields.Put("level", level)
   
   
   dbutils.UpdateRecord2(SQL, "highscores", ScoreFields, wherefields)
End Sub

 

[MikeSimpson]

Dose this create a new database, or dose this edit my existing one?
If it is my one, chouldn't SQL be SQL1?

My tabel name is: highscores
"Score" and "time" are the columns
"level" is the row (Similar to the Cursor)

 

[MikeSimpson]

I have created a small sample programm to test read and write the data to the database.
But my code nor the code from Reviewnow is working.

Reading the data is working, but how to get them changed?

Would be nice if someone could have a look, sample is attached.

 

[Reviewnow]

Mike,

You had a few issues with your code replace the two subs in your sample with the subs below and it will work..

Sub btnupdate_Click

    If edtnewhighscore.Text = "" OR edtnewbesttime.Text = "" Then
        Msgbox("Please enter some numbers", "ERROR")
        Return
        Else
    highscore = edtnewhighscore.Text
    time = edtnewbesttime.Text
'    Write_Highscore     'My code is not working
    'updateHighScores    'The is code is not working
    '^this required 4 parameters you can not call a sub procedure like this the correct way is below
   
    updateHighScores(SQL1,highscore,time,level)
   
    End If
   
End Sub

Sub updateHighScores (SQL As SQL,highscore1 As String,time1 As String,level1 As String)
  '^ This was changed becuase you can not use a global parameter in an enclosed block

'    Create Map of Scores
    Dim ScoreFields As Map
    ScoreFields.Initialize
    ScoreFields.Put("score",highscore1)
    ScoreFields.Put("time",time1)
  
'    Create Where Clause Map
    Dim WhereFields As Map
    WhereFields.Initialize
    WhereFields.Put("level", level1) '< is level the actual ID ?
'    Do you have a column Called level? Should it be
'    WhereFields.Put("level", level)
    DBUtils.UpdateRecord2(SQL, "highscores", ScoreFields, WhereFields)
End Sub

 

[MikeSimpson]

Thank you for your help Reviewnow,

You saved my day