Preventing database locked errors? Updating and Reading?
Posted: Wed 12 Oct 2011 22:26
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:
(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
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()
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