Android Question Can't update SQL database

juacochero · Feb 13, 2022

Hi everyone!
I've been going crazy and I can't seem to get this to work.
I'm using DBUtils to try to update an internal SQL database I have in B4A, and it just doesn't update.

My starter file:

Starter file:

dbdir = DBUtils.CopyDBFromAssets("database.db")
sqlDB.Initialize(dbdir, "database.db", False)

And my update code:

B4X:

 'Create the map'
Dim Map1 As Map
Map1.Initialize
'Where clause'
Map1.Put("userEmail", strUserEmail)
'fields to update'
DBUtils.UpdateRecord(Starter.sqlDB, "userconfig", "lastUser", "si", Map1)
DBUtils.UpdateRecord(Starter.sqlDB, "userconfig", "userEmail", strUserEmail, Map1)
DBUtils.UpdateRecord(Starter.sqlDB, "userconfig", "username", strUserFullName, Map1)
DBUtils.UpdateRecord(Starter.sqlDB, "userconfig", "userOrg", strUserOrg, Map1)
DBUtils.UpdateRecord(Starter.sqlDB, "userconfig", "UserTipoUsuario", strUserTipoUsuario, Map1)

If I then run a query such as this, it gets sent back empty:

B4X:

Dim userMap As Map
userMap.Initialize
userMap = DBUtils.ExecuteMap(Starter.sqlDB, "SELECT * FROM userconfig", Null)

Any ideas?
Thank you in advance!

Mahares · Feb 13, 2022

juacochero said:
Any ideas?

I do not use DBUtils lib much, but if you want to update the table the conventional way, this example works if you modified it for your data and table:

B4X:

Dim strQuery As String =$"
    UPDATE Crossfalls SET Stakevalue = ?, Left_Crossfall = ?, Right_Crossfall =? 
    WHERE Stakevalue = ?"$
    SQL.ExecNonQuery2(strQuery, Array As Object(5000, 6, 6, 2000))

If you try it and get stuck post the code and I am pretty sure someone will come to the rescue. Note also that there is: DBUtils.UpdateRecord2( which you can use to update several fields at the same time.

Mahares · Feb 13, 2022

juacochero said:
Any ideas?

I do not use DBUtils lib much, but if you want to update the table the conventional way, this example works if you modified it for your data and table:

B4X:

Dim strQuery As String =$"
    UPDATE Crossfalls SET Stakevalue = ?, Left_Crossfall = ?, Right_Crossfall =? 
    WHERE Stakevalue = ?"$
    SQL.ExecNonQuery2(strQuery, Array As Object(5000, 6, 6, 2000))

If you try it and get stuck post the code and I am pretty sure someone will come to the rescue. Note also that there is: DBUtils.UpdateRecord2( which you can use to update several fields at the same time.

juacochero · Feb 13, 2022

Mahares said:
I do not use DBUtils lib much, but if you want to update the table the conventional way, this example works if you modified it for your data and table:

B4X:

Dim strQuery As String =$" UPDATE Crossfalls SET Stakevalue = ?, Left_Crossfall = ?, Right_Crossfall =? WHERE Stakevalue = ?"$ SQL.ExecNonQuery2(strQuery, Array As Object(5000, 6, 6, 2000))

If you try it and get stuck post the code and I am pretty sure someone will come to the rescue. Note also that there is: DBUtils.UpdateRecord2( which you can use to update several fields at the same time.

Thank you Mahares. I did try the non-DButils way, but still no results.

I'm wondering if there's a permissions/access problem? Don't think so, since the DB is copied (With CopyDBFromAssets) and I can read test data if I add it externally, but I cannot seem to update it.

Mahares · Feb 13, 2022

juacochero said:
but I cannot seem to update it.

Hard to tell without you posting your code to update and how and where you save the database at, and any error messages. It is even better if you can post your project, provided your data is not sensitive or reproduce it in a small project. SQLite is quite popular in the forum and there are many qualified members who can help you.

juacochero · Feb 14, 2022

Mahares said:
Hard to tell without you posting your code to update and how and where you save the database at, and any error messages. It is even better if you can post your project, provided your data is not sensitive or reproduce it in a small project. SQLite is quite popular in the forum and there are many qualified members who can help you.

Sometimes all I need is a good night sleep...
I figured it out, I was never inserting the record, I was trying to update without inserting first... #noob

Thank you for your time Mahares!

Android Question Can't update SQL database

juacochero

Member

Mahares

Mahares

Expert

juacochero

Member

Mahares

Expert

juacochero

Member

Similar Threads

Android Question Can't update SQL database

juacochero

Member

Mahares

Expert

juacochero

Member

Mahares

Expert

juacochero

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency