Database Locked even after disposing all commands

Question

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

Answer 1

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 DataReaders 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

• NOTE: Like the original code there is no Data Validation - that is, it assumes that whatever they typed is always valid. This is rarely a good assumption.
• The code implements 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.
• SQL Parameters are used to simplify code and specify datatypes. A side effect of concatenating SQL as you are, is that everything is passed as string! This can be very bad with SQLite which is typeless.
• I would avoid firing off multiple look up queries in the loop. The original code should throw an InvalidOperationException since it never Reads from the DataReader.
  • Perhaps the best way to do this would be for 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.
  • Another alternative shown in the answer is to pre-load a DataTable with the data and then use some extension methods to look up the value needed.
  • If you "must" use readers, implement them in their own Using blocks.
• A For Each loop is used which provides the actual row being examined.
• I'd seriously reconsider the idea of storing something like a 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.