Can't Update SQLite Database File

I have been working on a problem over the last 3 days and I can’t figure out what is wrong. I want to update data in an SQLite database table and it just will not work.

In the Open Event of the Window I have this code:

[code] Roster= new SQLiteDatabase
Roster.DatabaseFile = GetFolderItem(“Roster.rsd”)

If not Roster.Connect Then
msgbox “Cannot open the database. Aborting.”
Quit
end

//Load Main Calls Database Table
Dim sqlCalls as string
sqlCalls = “select * from Calls”
rsC=Roster.SQLSelect(sqlCalls)

//Moves to the last record
rsC.MoveLast

UpdateDisplay[/code]

The Method UpdateDisplay has this coded:

txtSummary.Text=(rsC.Field("Summary").StringValue)

I them make a change in the text box txtSummary and click a button called “Update” The Update button has this code:

[code]rsC.Edit
rsC.field(“Summary”).StringValue=trim(txtSummary.Text)

rsC.Update

Roster.Commit

if Roster.error then
msgbox "DB ERROR: " + Roster.errormessage
end[/code]

When I clcik on the Update button I get this error:

DB ERROR: cannot commit - no transaction is active.

I have similar code in my project and it works, but for some reason this is not working. Can anyone see what I may be doing wrong? Any help would be greatly appreciated.

Unlike RealSQLDatabase, SQLiteDatabase commits automatically unless you specifically start a transaction. You did not start a transaction, so you do not need the Roster.Commit.

To start a transaction, you would do:

Roster.SQLExecute("BEGIN TRANSACTION")

before the SELECTing the data.

Thanks Paul. I will remember that, but I think I figured out what might be the problem.

I think maybe the database table may be corrupt. I created a new table and used the same code and it does save correctly. My other table had a few thousand records from an Access database file that I imported (through a whole lot of finagling) to a SQlite table. It may be corrupted.

I wish there was an easy way import MS Access database files directly to SQLite. that would have made life a little easier.

[quote=38354:@Paul Lefebvre]Unlike RealSQLDatabase, SQLiteDatabase commits automatically unless you specifically start a transaction. You did not start a transaction, so you do not need the Roster.Commit.

To start a transaction, you would do:

Roster.SQLExecute("BEGIN TRANSACTION")

before the SELECTing the data.[/quote]

That is interesting Paul… So if you BEGIN TRANSACTION does this prevent the changes from being committed until the COMMIT is issued?

The reason I ask is I was trying to add over 1,000 records to a database recently and it took an enormous amount of time. I had no luck rearranging commit, and it didn’t seem to make a difference whether I used it or not. However I never remembered this delay using the REALDatabase, it seemed much faster.

Now, I want to retry using BEGIN TRANSACTION adding the records then committing it. Thoughts?

Correct, changes in a transaction are not permanent until they are committed.

If you have a large number of changes to make (such as adding thousands of rows), you definitely want to use a transaction. Otherwise there would be an implicit commit after each DB operation, which would be quite slow.

If you have many thousands of rows, it often makes sense to commit periodically otherwise there can be a pause the end when all the data is committed.

James - check out this product: http://www.sqlabs.com/sqliteconverter.php

It’ll help you convert access to sqlite.

[quote=38546:@Greg O’Lone]James - check out this product: http://www.sqlabs.com/sqliteconverter.php

It’ll help you convert access to sqlite.[/quote]
Just want to second that recommendation. I recently converted a number of Access tables to sqlite using sqliteconverter. The largest one had a little bit over 125,0000 records, and it worked perfectly.

Another option is MDB / ACCDB Viewer.

[quote]MDB Viewer can export your Access database to pretty much anything.
Create Excel workbooks, SQLite databases, CSV files or SQL dumps.
Use MDB Viewer to export to Apple Numbers, Filemaker, Bento, Open Office or even your Address Book.[/quote]

I’ve converted various MS Access databases this way without any problem.