Getting error: "cannot commit-no transaction is active"?

Databases
1

It works at first (it appears to have saved according to a ListBox) but when I close the project (when testing) and reopen it, it has not saved (listbox shows previous value). Can anyone help please?
It doesn’t give an error when the first line is uncommented however it still has the same result- not saving in the SQLite database.

'gDatabase.SQLExecute("begin") Dim record As RecordSet=gDatabase.SQLSelect("SELECT * From Login") if record<>nil then record.MoveFirst while not record.EOF if record.Field("Coach").IntegerValue=0 then if InTeam(record.Field("RunnerID").IntegerValue) then if record.Field("Team").IntegerValue=0 then record.Edit record.Field("Team").IntegerValue=1 record.Update end else if record.Field("Team").IntegerValue=1 then record.Edit record.Field("Team").IntegerValue=0 record.Update end if record.Field("Team").IntegerValue=2 then record.Edit record.Field("Team").IntegerValue=3 record.Update end end end record.MoveNext wend end gDatabase.Commit if gDatabase.Error then MsgBox("ERROR: "+gDatabase.ErrorMessage) end

Thank you.

2

May be the “Commit” has to be executed before the “MoveNext” and after the “Update”?

3

SQLiteDatabase is automatically set to autocommit, unless you start a transaction.
So either you uncomment your “begin” or you comment your commit.

http://documentation.xojo.com/index.php/Database.Commit

https://www.sqlite.org/lang_transaction.html

4

I thought the database won’t save unless I commit it?

5

SQLiteDAtabase standard setting is to immediately execute each insert, update or delete and make permanent changes to the database.

There are cases where you can drastically speed up the execution by wrapping it into a a transaction with begin and commit.
Once you begin a transaction you can rollback or commit it.

This is also useful when you have to execute several statements in a row which belong together: when all of them successful, then commit, otherwise rollback.

To answer your question : SQLitedatabase does not need a commit in order to save an insert, update or delete.

6

I would also do some error checking after each update and in case of an error, then rollback the transaction and exit the while loop. You can set a variable ErrorOccured to TRUE and skip the commit at the end …

7

I removed the gDatabase.Commit but it still has the same result.

8

It’s fixed now!

Thank you to everyone for their help!