VB.NET SQL Database locked

Question

My data table is loaded in 2 places, a DataGridView and a ComboBox The ComboBox is to select a record to edit (a TextBox to enter a new value) And the DataGridView is to see the changes (I gave up (for now) updating directly from the DataGridView)

Private Sub EditLoc_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Try
        con.Open()


        Dim sql = Nothing

        sql = "SELECT Location FROM Location"


        Dim cmdDataGrid As SQLiteCommand = New SQLiteCommand(sql, con)


        Dim da As New SQLiteDataAdapter
        da.SelectCommand = cmdDataGrid
        Dim dt As New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt

        Dim readerDataGrid As SQLiteDataReader = cmdDataGrid.ExecuteReader()

        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try



    Try ' TRY CATCH for combobox

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT Location FROM Location"


        dr = cmd.ExecuteReader()

        ' Fill a combo box with the datareader
        Do While dr.Read = True
            ComboBox1.Items.Add(dr.GetString(0))
        Loop

        If ComboBox1.Items.Count > 0 Then
            ComboBox1.SelectedIndex = 0 ' The first item has index 0 '
        End If

        con.Close()

    Catch ex As Exception
        MsgBox(ex.Message)

    End Try


End Sub

This works perfectly Picture

The problem is when I click Save, the app hangs a while, and then I get the "Database is locked" error picture

Here is the code for the Save button:

Private Sub Savebtn_Click(sender As Object, e As EventArgs) Handles Savebtn.Click

    Try
        con.Open()
        cmd = con.CreateCommand
        cmd.CommandText = "UPDATE Location set Location = '" & TextBox1.Text & "' WHERE Location = '" & ComboBox1.Text & "'"
        cmd.ExecuteNonQuery()
        cmd.Dispose()

        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub

Thanks for the help

Answer 1

I fixed this problem by removing all "cmd.Dispose()", "da.Dispose()" and "con.Close()" from the code, leaving them ONLY in

Private Sub Closebtn_Click(sender As Object, e As EventArgs) Handles Closebtn.Click
    da.Dispose()
    cmd.Dispose()
    con.Close()
    Dim fems As New EMS
    fems.Show()
    Me.Close()
End Sub

On Form Load I have

Private Sub EditLoc_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    con.Open()
    Call pull()
End Sub

And the Pull sub has all the rest...

Private Sub pull()
    Try
        Dim sql = Nothing
        sql = "SELECT Location FROM Location"
        Dim cmdDataGrid As SQLiteCommand = New SQLiteCommand(sql, con)
        da.SelectCommand = cmdDataGrid
        Dim dt As New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt
        Dim readerDataGrid As SQLiteDataReader = cmdDataGrid.ExecuteReader()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

    Try ' TRY CATCH for combobox
        cmd.Connection = con
        cmd.CommandText = "SELECT Location FROM Location"
        dr = cmd.ExecuteReader()
        ' Fill a combo box with the datareader
        Do While dr.Read = True
            ComboBox1.Items.Add(dr.GetString(0))
        Loop
        If ComboBox1.Items.Count > 0 Then
            ComboBox1.SelectedIndex = 0 ' The first item has index 0 '
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub

And here is my Save button

Private Sub Savebtn_Click(sender As Object, e As EventArgs) Handles Savebtn.Click

    If Not TextBox1.Text = Nothing Then
        Try
            cmd = con.CreateCommand
            cmd.CommandText = "UPDATE Location set Location = '" & TextBox1.Text & "' WHERE Location = '" & ComboBox1.Text & "'"
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Call pull()
        TextBox1.Text = Nothing

    End If

End Sub

Now everything is working, no errors!

Calling pull() after saving will update the DataGridView

Thanks for the help