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

MikeSimpson · Sep 17, 2014

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:

B4X:

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:

B4X:

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:

B4X:

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:

B4X:

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 · Sep 17, 2014

Are you sure you wrote the correct cell names ?

You read the highscore cell with:

B4X:

oldscore = Cursor1.GetInt("score")

But you try to update with:

B4X:

SET highscore = ?,

Should it be

B4X:

SET score = ?,

MikeSimpson · Sep 17, 2014

Eumel said:
Should it be

B4X:

SET score = ?,

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

Reviewnow · Sep 17, 2014

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

B4X:

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 · Sep 17, 2014

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 · Sep 17, 2014

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 · Sep 17, 2014

Mike,

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

B4X:

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 · Sep 17, 2014

Thank you for your help Reviewnow,

You saved my day

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

MikeSimpson

Member

Eumel

Active Member

MikeSimpson

Member

Reviewnow

Active Member

MikeSimpson

Member

MikeSimpson

Member

Attachments

Reviewnow

Active Member

MikeSimpson

Member

Similar Threads

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

Member

Active Member

Member

Active Member

Member

Member

Attachments

Active Member

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency