Results 1 to 2 of 2

Thread: SQLite Database Locked - Going Bonkers

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2018

    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.

    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()
                connect = New SQLiteConnection("Data Source=C:\Users\JLappy\source\repos\VendorContact\VendorContact\bin\Debug\world.db") ' Connection DataSource
                If connect.State = ConnectionState.Closed Then
                    '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
                    DGV.Rows(i).DefaultCellStyle.BackColor = Color.FromKnownColor(KnownColor.Control)
                End If
        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
                    DGV1.Rows(i).DefaultCellStyle.BackColor = Color.FromKnownColor(KnownColor.Control)
                End If
        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("Edit").Visible = False
            DataGridView2.Columns("Delete").Visible = False
            DataGridView2.RowHeadersVisible = False
            'DataGridView2.DataSource = Nothing
                'If connect.State = ConnectionState.Open Then connect.Close()
                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
                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"
                'vendorid = DataGridView1.CurrentRow.Cells(0).Value
            Catch ex As Exception
            End Try
        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
            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
                        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
                        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"}
                        'dt.Columns.Insert(6, btn)
                        'DataGridView2.ReadOnly = True
                        'vendorid = DataGridView1.CurrentRow.Cells(0).Value
                    Catch ex As Exception
                    End Try
                End If
            End With
            'DataGridView2.DataSource = Nothing
        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
            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
                If connect.State = ConnectionState.Open Then connect.Close()
                Using cmd As SQLiteCommand = New SQLiteCommand(query, connect)
                    cmd.CommandText = query
                End Using
            Catch ex As Exception
            End Try
        End Sub
    End Class

  2. #2
    New Member
    Join Date
    Jun 2018

    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.

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