CODEWITHSUNDEEP
vb.netsqlitevisual-studio-2010
John Moore
John Moore

Reputation: 519

SQLite Database is Locked error in vb.net

I've got a form that creates a new user to be added to my database

Before the user is added, a check is made to make sure the username doesn't already exist

Dim sqlConn As New SQLiteConnection("Data Source=" & dbPath & ";Version=3;New=True;Compress=True;Password=mypassword")
    sqlConn.Open()
    Dim sqlCommand As New SQLiteCommand("SELECT * FROM usertable WHERE username ='" & txtUserName.Text & "'", sqlConn)
    Dim sqlReader As SQLiteDataReader = sqlCommand.ExecuteReader()
    If sqlReader.HasRows = False Then
        sqlReader.Close()
        sqlCommand.Dispose()
        Dim sInsertSQL As String = "MY INSERT SQL"
        sqlCommand = New SQLiteCommand(sInsertSQL, sqlConn)
        sqlCommand.ExecuteNonQuery() 'I GET DATABASE IS LOCKED ERROR HERE'
        sqlCommand.Dispose()
        sqlConn.Close()
    ELSE
        'Username already exists message box
    END IF

I checked out this page before asking my question: Diagnosing cause of SQLite locking

As you can see, I'm closing the Reader and Command before re-querying the database but I still get the locked error. I've even tried closing the connection and reestablishing everything but the error appears at the same line

Upvotes: 1

Views: 2269

Answers (1)

G.Nuy
G.Nuy

Reputation: 11

Having had the same issue, I figured out some other process prior to adding new values locked the db. Maybe another code was run prior to the snippet you show.

Try this one, works fine for me:

Using SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
SQLconnect.ConnectionString = sDBName
If SQLconnect.State = ConnectionState.Open Then SQLconnect.Close()

SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand
SQLcommand.CommandTimeout = 10

SQLcommand.CommandText = "INSERT INTO news (id, M_IP, M_TYPE, M_MESSAGE, M_DONE) VALUES (1, '192.168.2.7' ,'STATUS', 'Hallo?' ,1 )"
'SQLcommand.CommandText = "UPDATE news Set name = 'Johnny' WHERE id = last_insert_rowid()"
'SQLcommand.CommandText = "DELETE FROM news WHERE id = last_insert_rowid()"

Try

    Console.WriteLine("Erzeugung von n Zeilen: " & SQLcommand.ExecuteNonQuery(CommandBehavior.CloseConnection))

    SQLcommand.Dispose()
    If SQLconnect.State = ConnectionState.Open Then SQLconnect.Close()

Catch sql_ex As SQLiteException
    Console.WriteLine(sql_ex.ToString)
    Console.WriteLine(sql_ex.Message)

End Try

End Using

Best Regards

GN

Upvotes: 1

Related Questions