Results 1 to 3 of 3

Thread: SQLite Database Locked - Going Bonkers

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    1

    Post SQLite Database Locked - Going Bonkers

    Hello Experts,

    I have a form, two datagrids and a textbox The operation is pretty simple I am loading a list of vendors in the first datagrid on form load, the user selects any vendor the address is displayed in the text box beside and the contact details are displayed in the bottom with a button column "Delete"

    Now whenever I edit any cell in the details datagrid I am getting the database locked error when trying to update on cellvaluechanged event.

    I have tried everything that I know off, still no luck. All the code is below.

    Thanks for all the help.

    Code:
    Imports System.Data.SQLite ' using SQLite Namespaces
    
    Public Class Form1
        Dim connect As SQLiteConnection ' declare the connection
        'Dim vendorid As Integer
    
        Dim var_id As Integer
    
        Sub connection()
            Try
                connect = New SQLiteConnection("Data Source=C:\Users\JLappy\source\repos\VendorContact\VendorContact\bin\Debug\world.db") ' Connection DataSource
                If connect.State = ConnectionState.Closed Then
                    connect.Open()
                    'MsgBox("Connection Success!", MsgBoxStyle.Information, "Informations")
                End If
            Catch ex As Exception
                MsgBox("Failed to connect to SQLite Database", MsgBoxStyle.Information, "Warning")
            End Try
    
        End Sub
    
        Private Sub GridFormatting(ByVal DGV As DataGridView)
            DGV.ForeColor = Color.Black
            DGV.BackgroundColor = Color.AliceBlue
            'DGV.EnableHeadersVisualStyles = False
            DGV.RowHeadersVisible = False
            DGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            DGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
    
            DataGridView1.Columns(0).Width = 60
            DataGridView1.Columns(1).Width = 329
            DataGridView1.Columns(2).Width = 100
            'DataGridView1.Columns(3).Width = 100
    
            DataGridView1.Columns(3).Visible = False
            DataGridView1.Columns(4).Visible = False
            DataGridView1.Columns(5).Visible = False
            DataGridView1.Columns(6).Visible = False
    
    
            For i As Integer = 0 To DGV.RowCount - 1 'Alternating Colors
                If i Mod 2 = 0 Then
                    DGV.Rows(i).DefaultCellStyle.BackColor = Color.White
                Else
                    DGV.Rows(i).DefaultCellStyle.BackColor = Color.FromKnownColor(KnownColor.Control)
    
                End If
            Next
        End Sub
    
        Private Sub Grid2Formatting(ByVal DGV1 As DataGridView)
    
    
            DataGridView2.RowHeadersVisible = False
            DataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
    
            DataGridView2.Columns(1).Width = 50
            DataGridView2.Columns(2).Width = 70
            DataGridView2.Columns(3).Width = 200
            DataGridView2.Columns(4).Width = 300
            DataGridView2.Columns(5).Width = 134
            DataGridView2.Columns(6).Width = 135
    
            For i As Integer = 0 To DGV1.RowCount - 1 'Alternating Colors
                If i Mod 2 = 0 Then
                    DGV1.Rows(i).DefaultCellStyle.BackColor = Color.White
                Else
                    DGV1.Rows(i).DefaultCellStyle.BackColor = Color.FromKnownColor(KnownColor.Control)
    
                End If
            Next
    
        End Sub
    
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
    
            Dim grid2_btn_Delete As New DataGridViewButtonColumn()
            With grid2_btn_Delete
                .Name = "Delete"
                .HeaderText = "Delete"
                .Text = "Delete"
                .UseColumnTextForButtonValue = True
                .AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
                .FlatStyle = FlatStyle.Standard
                .FlatStyle = FlatStyle.Flat
                .CellTemplate.Style.BackColor = Color.Honeydew
                '.CellTemplate.Style.ForeColor = Color.Red
                .DisplayIndex = 1
            End With
            DataGridView2.Columns.Add(grid2_btn_Delete)
    
    
            'DataGridView2.Columns("Edit").Visible = False
            DataGridView2.Columns("Delete").Visible = False
    
            DataGridView2.RowHeadersVisible = False
    
            'DataGridView2.DataSource = Nothing
            'DataGridView2.Refresh()
            'DataGridView2.Rows.Clear()
    
            Try
                'If connect.State = ConnectionState.Open Then connect.Close()
                connection()
                Dim sql = "SELECT Vendor_ID, Vendor_Name1, Shop_Type, Address, City, Postal_Code, Country FROM tbl_Vendors"
    
                Dim cmdDataGrid As SQLiteCommand = New SQLiteCommand(sql, connect)
    
                Dim da As New SQLiteDataAdapter
                da.SelectCommand = cmdDataGrid
                Dim dt As New DataTable
                da.Fill(dt)
    
                DataGridView1.DataSource = dt
                'DataGridView2.DataSource = dt
    
                Dim readerDataGrid As SQLiteDataReader = cmdDataGrid.ExecuteReader()
    
                'DataGridView1.AutoGenerateColumns = False
                'DataGridView1.ColumnCount = 4
    
                dt.Columns(0).ColumnName = "ID"
                dt.Columns(1).ColumnName = "Vendor Name"
                dt.Columns(2).ColumnName = "Type"
    
                GridFormatting(DataGridView1)
    
                'vendorid = DataGridView1.CurrentRow.Cells(0).Value
    
                'dt.Dispose()
            Catch ex As Exception
                MsgBox(ex.ToString())
            Finally
                connect.Close()
            End Try
    
    
            'connect.Dispose()
        End Sub
    
        Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
    
            'DataGridView2.Columns("Edit").Visible = True
            DataGridView2.Columns("Delete").Visible = True
    
            DataGridView2.Refresh()
    
            Dim i As Integer
            Dim var_address As String
            Dim var_postal As String
            Dim var_city As String
            Dim var_country As String
            'Dim var_id As Integer
    
    
            With DataGridView1
                If e.RowIndex >= 0 Then
                    i = .CurrentRow.Index
    
                    var_id = .Rows(i).Cells("id").Value
    
                    var_address = .Rows(i).Cells("Address").Value.ToString
                    var_postal = .Rows(i).Cells("Postal_Code").Value.ToString
                    var_city = .Rows(i).Cells("City").Value.ToString
                    var_country = .Rows(i).Cells("Country").Value.ToString
    
                    'TextBox1.Text = TextBox1.Text.PadLeft(TextBox1.Text.Length + 20)
    
                    TextBox1.Text = var_address + vbCrLf + var_city + vbCrLf + var_postal + vbCrLf + var_country
                    TextBox1.ReadOnly = True
                    Try
    
                        connection()
                        Dim readsql = "SELECT plant_id, storage_location, inv_maintainer, inv_maintainer_email, inv_maintainer_phone1, inv_maintainer_phone2 FROM tbl_Vendor_details where cp_vendor_id =" & var_id & ""
    
                        Dim cmdreadDataGrid As SQLiteCommand = New SQLiteCommand(readsql, connect)
    
                        Dim readda As New SQLiteDataAdapter
                        readda.SelectCommand = cmdreadDataGrid
                        Dim readdt As New DataTable
                        'dt.Columns.Clear()
                        'dt.Rows.Clear()
                        readda.Fill(readdt)
    
    
    
                        DataGridView2.DataSource = readdt
                        'DataGridView2.DataSource = dt
    
                        Dim readerDataGrid As SQLiteDataReader = cmdreadDataGrid.ExecuteReader()
    
    
                        'DataGridView1.AutoGenerateColumns = False
                        'DataGridView1.ColumnCount = 4
    
    
                        'dt.Columns.Add("Test", Type.GetType("System.String"))
    
                        readdt.Columns(0).ColumnName = "Plant"
                        readdt.Columns(1).ColumnName = "Storage Location"
                        readdt.Columns(2).ColumnName = "Inventory Maintainer"
                        readdt.Columns(3).ColumnName = "Email"
                        readdt.Columns(4).ColumnName = "Phone"
                        readdt.Columns(5).ColumnName = "Alt. Phone"
    
                        'Dim row = New String() {"4", "Product 4", "4000"}
                        'DataGridView1.Rows.Add(row)
                        'dt.Columns.Add(btn)
                        'dt.Columns.Insert(6, btn)
                        'DataGridView2.Columns.Add(btn)
    
                        Grid2Formatting(DataGridView2)
    
                        'DataGridView2.ReadOnly = True
    
                        'vendorid = DataGridView1.CurrentRow.Cells(0).Value
                        readdt.Dispose()
                    Catch ex As Exception
                        MsgBox(ex.ToString())
                    Finally
                        connect.Close()
                    End Try
    
    
                End If
    
            End With
            'DataGridView2.DataSource = Nothing
            'DataGridView2.Refresh()
            'DataGridView2.Rows.Clear()
            'connect.Dispose()
    
        End Sub
    
        Private Sub DataGridView2_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView2.CellValueChanged
    
            Dim Plant_id = DataGridView2.Rows(e.RowIndex).Cells(1).Value
            Dim storage = DataGridView2.Rows(e.RowIndex).Cells(2).Value
            Dim var_maintainer = DataGridView2.Rows(e.RowIndex).Cells(3).Value
    
            'MessageBox.Show(DataGridView2.Rows(e.RowIndex).Cells(e.ColumnIndex).Value)
    
            'MsgBox(Plant_id)
            'MsgBox(storage)
            'MsgBox(var_maintainer)
            MsgBox(var_id)
    
    
    
            Dim query As String = "UPDATE tbl_vendor_details SET plant_id = '" & Plant_id & "', storage_location = '" & storage & "', inv_maintainer = '" & var_maintainer & "' WHERE cp_vendor_id = '" & var_id & "'"
            Dim affectedRows As Integer = 0
            Try
                If connect.State = ConnectionState.Open Then connect.Close()
                connection()
                Using cmd As SQLiteCommand = New SQLiteCommand(query, connect)
    
                    cmd.CommandText = query
                    cmd.ExecuteNonQuery()
    
                    cmd.Dispose()
                    connect.Close()
                End Using
    
    
            Catch ex As Exception
                MsgBox(ex.ToString())
    
            End Try
    
            DataGridView2.Refresh()
            connect.Dispose()
    
        End Sub
    
    End Class

  2. #2
    Lively Member
    Join Date
    Jun 2018
    Location
    Krähental
    Posts
    64

    Re: SQLite Database Locked - Going Bonkers

    I don't use datatables so I don't know if this is relevant.

    I use a connection, a command object and a DataReader to read the data.

    But SQLite has a weird 'feature'. If you use a DataReader then you "must" close the reader when you're done with it before closing the connection. if you just close the connection without closing the reader then the database is kind of locked.

    I don't fully understand the reason but it may have something to do with connection pooling. When you close a connection it actually stays connected and that pooled connection still has an open datareader associated with it which prevents creation of subsequent readers within the same connection - Attempting to write anything throws an error saying that the connection can't write while it has an open datareader - or something like that.

    In any case I just needed to make sure that I Close the DataReader before closing the connection. Closing the connection in SQLite does not automatically close any open datareaders.

    I don't know if that also affects dataadapters/datatables. In any case it might be worth making sure they're all correctly closed/disposed before closing the connection.

  3. #3
    New Member
    Join Date
    Feb 2022
    Posts
    1

    Re: SQLite Database Locked - Going Bonkers

    I created an account just to thank you. I spent 2 days finally this is the correct answer
    But SQLite has a weird 'feature'. If you use a DataReader then you "must" close the reader when you're done with it before closing the connection. if you just close the connection without closing the reader then the database is kind of locked.
    thank you so much @Axcontrols2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width