The database is locked error appears even after I have disposed all the commands. I'm trying to write to the database and it is failing on the INSERT command at the bottom. I had it working before but for some reason it has started to fail now.
Sub Btn_SubmitClick(sender As Object, e As EventArgs)
If MsgBox("Are you sure?",vbYesNo,"Submit?") = 7 Then
'returns to previous screen
Else
'commences insert into database
Rows = (dataGridView1.RowCount - 1)
While count < rows
'putting grid stuff into variables
DateStart = dataGridView1.Rows(Count).Cells(0).Value.ToString
DateEnd = dataGridView1.Rows(Count).Cells(2).Value.ToString 'note other way round
TimeStart = dataGridView1.Rows(Count).Cells(1).Value.ToString
TimeEnd = dataGridView1.Rows(Count).Cells(3).Value.ToString
TotalHours = dataGridView1.Rows(Count).Cells(4).Value.ToString
OccuranceNo = dataGridView1.Rows(Count).Cells(5).Value.ToString
'fetching reason ID for Storage
SQLcommand = SQLconnect.CreateCommand
SQLcommand.CommandText = "SELECT Reason_ID FROM Reasons WHERE Reason_Name = '" & dataGridView1.Rows(Count).Cells(6).Value.ToString & "'"
SQLreader = SQLcommand.ExecuteReader
ReasonID = SQLreader("Reason_ID")
SQLcommand.Dispose
'fetching site ID for storage
SQLcommand = SQLconnect.CreateCommand
SQLcommand.CommandText = "SELECT Site_ID FROM Sites WHERE Site_Name = '" & dataGridView1.Rows(Count).Cells(7).Value.ToString & "'"
SQLreader = SQLcommand.ExecuteReader
SiteID = SQLreader("Site_ID")
SQLcommand.Dispose
Oncall = dataGridView1.Rows(Count).Cells(8).Value.ToString
'increment counter
Count = Count + 1
'send to database
SQLcommand = SQLconnect.CreateCommand
SQLcommand.CommandText = "INSERT INTO Shifts (Staff_ID, Date_Shift_Start, Date_Shift_End, Time_Shift_Start, Time_Shift_End, Total_Hours, Occurance_No, Site_ID, On_Call_Req, Rate, Approved, Reason_ID) VALUES ('" & userID & "' , '" & DateStart &"' , '" & DateEnd & "' , '" & TimeStart & "' , '" & TimeEnd & "' , '" & TotalHours & "' , '" & OccuranceNo & "' , '" & SiteID & "' , '" & Oncall & "' , '"& "1" & "' , '" & "N" & "' , '" & ReasonID & "')"
SQLcommand.ExecuteNonQuery()
SQLcommand.Dispose
End While
MsgBox("Ok")
End If
End Sub
There are several things which ought be changed in the code shown. Since none of the Connection
, Command
or Reader
objects are declared in the code, they must be global objects you are reusing. Don't do that.
There can be reasons for one persistent connection, but queries are very specific in nature, so trying to reuse DbCommand
and DataReader
s can be counterproductive. Since these work closely with the DbConnection
, all sorts of bad things can happen. And it means the root of the problem could be anywhere in your code.
The following will loop thru a DGV to insert however many rows there are.
Dim SQL = "INSERT INTO Sample (Fish, Bird, Color, Value, Price) VALUES (@f, @b, @c, @v, @p)"
Using dbcon As New SQLiteConnection(LiteConnStr)
Using cmd As New SQLiteCommand(SQL, dbcon)
dbcon.Open()
cmd.Parameters.Add("@f", DbType.String)
cmd.Parameters.Add("@b", DbType.String)
cmd.Parameters.Add("@c", DbType.String)
cmd.Parameters.Add("@v", DbType.Int32)
cmd.Parameters.Add("@p", DbType.Double)
Dim fishName As String
For Each dgvR As DataGridViewRow In dgv2.Rows
' skip the NewRow, it has no data
If dgvR.IsNewRow Then Continue For
' look up from another table
' just to shorten the code
userText = dgvR.Cells(0).Value.ToString()
fishName = dtFish.AsEnumerable().
FirstOrDefault(Function(f) f.Field(Of String)("Code") = userText).
Field(Of String)("Fish")
' or
'Dim drs = dtFish.Select(String.Format("Code = '{0}'", userText))
'fishName = drs(0)("Fish").ToString()
cmd.Parameters("@f").Value = fishName
cmd.Parameters("@b").Value = dgvR.Cells(1).Value
cmd.Parameters("@c").Value = dgvR.Cells(2).Value
cmd.Parameters("@v").Value = dgvR.Cells(3).Value
cmd.Parameters("@p").Value = dgvR.Cells(4).Value
cmd.ExecuteNonQuery()
Next
End Using
End Using
Using
blocks which will declare and create the target objects (dbCommands, connections) and dispose of them when done with them. They cannot interfere with code elsewhere because they only exist in that block.InvalidOperationException
since it never Read
s from the DataReader
.
Sites
and Reasons
to be ComboBox
column in the DGV where the user sees whatever text, and the ValueMember
would already be available for the code to store.DataTable
with the data and then use some extension methods to look up the value needed.Using
blocks.For Each
loop is used which provides the actual row being examined.startDateTime
as individual Date
and Time
fields.These are the basics if using DB Provider objects. It is not certain that refactoring that procedure shown will fix anything, because the problem could very well be anywhere in the code as a result of DBProvider objects left open and not disposed.
One other thing to check is any UI Manager you may be using for the db. Many of these accumulate changes until you click a Save or Write button. In the interim, some have the DB locked.
Finally, even though the code here is shorter and simpler, using a DataAdapter
and a DataTable
would allow new data in the DGV to automatically update the database:
rowsAffected = myDA.Update(myDT)
It would take perhaps 30 mins to learn how to configure it that way.