I have a Sub to show data on a DataGridView but after this sub I can't add files because I get a database is locked message.
I tryed using
But is the same. Also I tryed to make the Using block as small as possible.
Any clue why the database is locked after this code?
Private Sub LoadDataGridView1()
Dim connLoadDataGrid = New SQLiteConnection("Data Source=myDataBase.sqlite;Version=3")
Dim sql = "SELECT * FROM users"
Dim cmdDataGrid As SQLiteCommand = New SQLiteCommand(sql, connLoadDataGrid)
'Load data on a Table
Dim da As New SQLiteDataAdapter
da.SelectCommand = cmdDataGrid
Dim dt As New DataTable
DataGridView1.DataSource = dt
'Change the name of the columns
.RowHeadersVisible = False
.Columns(0).HeaderCell.Value = "ID"
.Columns(1).HeaderCell.Value = "Name"
.Columns(2).HeaderCell.Value = "Type"
.Columns(3).HeaderCell.Value = "Notes"
'Autoadjust the columns
Dim readerDataGrid As SQLiteDataReader = cmdDataGrid.ExecuteReader()
Catch ex As Exception
Fundamentally, your code leaves several other things undisposed: the
DataAdapter and most importantly, an odd
DataReader open and alive at the end. Anything which implements the
Dispose method ought to be created and used in a
Using block to be sure it is disposed and resources released.
Now, this will implement code which violates that rule to create a
DataAdapter which will live for the life of your app/form and be able to perform all the DB operations for you:
' form/class level objects Private daLite As SQLiteDataAdapter Private dtLite As DataTable Private LiteConn As SQLiteConnection
Initializing the DB provider objects:
' in this case, a persistent Connex is used LiteConn = New SQLiteConnection(LiteConnStr) ' spell out your SQL Dim SQL = "SELECT Id, Name, Fish, Bird, Color, Price FROM Sample" ' persistent datatadapter daLite = New SQLiteDataAdapter(SQL, LiteConn) dtLite = New DataTable LiteConn.Open() ' teach the DA how to do everything Dim cb As New SQLiteCommandBuilder(daLite) daLite.InsertCommand = cb.GetInsertCommand daLite.UpdateCommand = cb.GetUpdateCommand daLite.DeleteCommand = cb.GetDeleteCommand ' load schema, table daLite.FillSchema(dtLite, SchemaType.Source) daLite.Fill(dtLite) dgv1.DataSource = dtLite ' open and close for each use LiteConn.Close()
At the end, your DataAdapter will know how to Insert, update and delete from the DB. After the user adds perhaps several rows to the
DataGridView, changes some and maybe deletes one or two, update the database:
LiteConn.Open() Dim rows = daLite.Update(dtLite) LiteConn.Close()
HeaderTextdoesnt change the column name, just the displayed text.
DataTableis needed to store the data. Each row has a
RowStateindicating what the user has done to it (change, added etc). The
DataAdapteruses this state to know what to do for each row when it comes time to update.
Transactionwill speed it up.
CommandBuilderis used to create the other DB commands from the SELECT SQL. In the case of SQLite, this cannot be disposed or the
DataAdpaterwill loose the command(s). You can also specify/type in the SQL for the other commands if you prefer.
A side benefit is that you will also be able to "refresh" the rows. If there is more than one user or changes are being made outside the app (such as by you in a UI DB browser). Performing
daLite.Fill(dtLite) again later, picks up just those changes.
You do need to remember to Open/Close the connection as you use it. Also, this only works on simple one table queries as yours appears to be.