Preventing database locked errors? Updating and Reading?

[Slopey]

Hi there,

I'm trying to use SQLite as the database for a VB.Net application which is a business management simulation. However, I need to be able to update records in the database while looping through other tables with a datareader. I know that previous versions of SQLite won't allow updates while there's a select in progress, however I thought I could use the Devart library to open the database either in WAL Mode, or with ReadUncommitted = True, but I'm still getting locking issues.

I'm opening the database with:

Code: Select all

 Devart.Data.SQLite.SQLiteConnection.EnableSharedCache()

        Dim builder As New Devart.Data.SQLite.SQLiteConnectionStringBuilder
        builder.ReadUncommitted = True
        builder.DateTimeFormat = Devart.Data.SQLite.SQLiteDateFormats.ISO8601
        builder.DataSource = "c:\MyPlayerDatabase.db"
        builder.DefaultCommandTimeout = 500
        builder.MinPoolSize = 0
        builder.MaxPoolSize = 100
        builder.Pooling = True
        builder.FailIfMissing = False
        builder.LegacyFileFormat = False
        builder.JournalMode = Devart.Data.SQLite.JournalMode.Default

        PlayerConn.ConnectionString = builder.ConnectionString
        PlayerConn.Open()

(I've also tried with JournalMode set to WAL, but to no avail)

And then in my code, I open a datareader and Read() through the records, and want to update another table based on the values in the datareader table, but I still get 'database is locked' errors when updating.

Is it possible to use SQLite to update records within a loop where I have an open datareader?

And if so, can I do it with dotConnect, and do I have the connection settings set up correctly?

Or do I have to set something on the SQLite database file itself? The database file I'm using was created with a previous version of SQLite and Navicat - does it need to be re-created somehow to make it compliant with 3.7 and WAL?

Cheers,
Slopey

[Slopey]

Never mind - I figured it out. The database I was using didn't have WAL set as the journalling mode. In the end I created a new database, imported the data into it, and it now works perfectly with WAL and I can write and select at the same time without the locks.