-
Aug 3rd, 2018, 10:57 PM
#1
Thread Starter
New Member
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
-
Aug 4th, 2018, 08:54 AM
#2
Lively Member
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.
-
Feb 19th, 2022, 04:47 PM
#3
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|