Vb.net Sqlite Locked

Question

i have a table in sqlite database UserSetting

CREATE TABLE "UserSetting" (
    "id"    INTEGER NOT NULL UNIQUE,
    "install"   Boolean NOT NULL DEFAULT 0,
    "databasetype"  TEXT,
    "connectionstring"  TEXT,
    PRIMARY KEY("id" AUTOINCREMENT)
);

this Code for return True or false from first record Usersetting.install where id=1

 Function installcomplete() As Boolean
        Try
            Using con As New SQLiteConnection(GetSQLiteConnectionString(False))
                con.Open()
                Dim cmd As New SQLiteCommand
                With cmd
                    .CommandText = "Select install from UserSetting where id=1;"
                    .Connection = con
                    .CommandTimeout = 10
                End With
                Dim b As Boolean = CBool(cmd.ExecuteScalar)
                con.Close()
                Return b
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
            Return False
        End Try
    End Function

This Method for first record Usersetting.install where id=1

  Sub completeinstall(ByVal t As Boolean)
            Try
                Using con As New SQLiteConnection(GetSQLiteConnectionString(False))
                    con.Open()
                    Dim cmd As New SQLiteCommand
                    With cmd
                        .CommandText = "Update UserSetting set install =1 where id=1;"
                        .Connection = con
                        .CommandTimeout = 10
                        .ExecuteNonQuery()
                    End With
                    con.Close()
                End Using
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub

When run function installcompleted return real information. but when run completeinstall return error "Database is locked" and i always use "Using" when connection because it dispose connection automatically .how to fix?

GetSqlconnectionstring for build connectionstring from Structure

Public Structure Msql
        Shared datasource As String
        Shared IntSec As Boolean
        Shared username As String
        Shared password As String
        Shared DatabaseName As String
    End Structure

  Public Function GetSQLConnectionString(ByVal includeDatabase As Boolean) As String
        Dim builder As New SqlConnectionStringBuilder()
        'Build a connection string from the user input.'

        builder.DataSource = Msql.datasource
        builder.IntegratedSecurity = Msql.IntSec
        If builder.IntegratedSecurity = False Then
            builder.UserID = Msql.username
            builder.Password = Msql.password
        End If
        If includeDatabase Then
            builder.InitialCatalog = Msql.DatabaseName
        End If

        Return builder.ConnectionString
    End Function

You have another connection still open. SQLite is an embedded file-based database that allows only *one* connection to open the file for modification at a time. You'll have to find which part of your code creates connections and forgets to close them. Are you trying to connect from multiple threads perhaps? — Panagiotis Kanavos, Jul 26 '20 at 17:24
What is the actual connection string? What does `GetSQLiteConnectionString(False)` return? You can add `;PRAGMA journal_mode=WAL;` to prevent blocking on updates *but* that means reads can get "stale" data until the changes written to the Write-Ahead-Log get persisted — Panagiotis Kanavos, Jul 26 '20 at 17:27
@PanagiotisKanavos i always use "Using" when connection because it dispose connection automatically — Moustafa Khazaal, Jul 26 '20 at 17:37
And yet, the file is still locked. That's what the error says. Are you using multiple threads? What is the actual connection string? — Panagiotis Kanavos, Jul 26 '20 at 17:40
@PanagiotisKanavos I don't know what PRAGMA nor how to use it — Moustafa Khazaal, Jul 26 '20 at 17:40
Given that SQLite is an embedded database you should probably use only *one* point to perform queries and updates, eg a single data access class. — Panagiotis Kanavos, Jul 26 '20 at 17:41
@PanagiotisKanavos connection string "data source=Data.db;version=3" — Moustafa Khazaal, Jul 26 '20 at 17:42

score 0 · Accepted Answer · answered Jul 26 '20 at 18:03

Somewhere along the way a connection is getting left open. Get rid of OpenConnection and CloseConnection and change ExecuteNonQuery to this:

 Using c As New SQLiteConnection(ConnectionString)
        c.Open()
        Using cmd As New SQLiteCommand(sql, c)
            cmd.ExecuteNonQuery()
        End Using
    End Using

Answer Link

Vb.net Sqlite Locked

1 Answers1