[02/03] Executing many SQL commands in a short period of time

[Noa D]

In my program I need to access the database several times. Very often I get an error message saying "Could not update, currently locked", and my query isn't executed.
I am using Access database with ADO.NET (Visual Studio.NET 2003).

Can anyone help me solve this problem?

Thanks in advance.

[talkro]

can you post your code?

[bmahler]

You need to show us how your are accessing the database and perhaps we can be of some help. It sounds like you are perhaps trying to open multiple connections to access which may be causing this, however until wee see what you are doing, can't really help.

[Noa D]

I connect to the database once at the beginning of the program and it stays connected all through the program -

Dim DataConnection as ADODB.Connection
DataConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBlocation)

then, all through the program, I execute SQL queries like this -

Dim strSQL as string = "UPDATE bla bla bla"
DataConnection.Execute(strSQL)

[talkro]

I don't know if this is gonna help, but you need to open and close your connection somewhere before and after your querey is executed, otherwise at some point you are going to end up with a number of connections than can't be handled by your db

If you post a more detail code many we can help you better than this

[bmahler]

Talkro is right, you should open and close your connection when executing your queries. Easiest way to do it is to have a function that accepts the SQL and then open your connection, run your update, and then close the connection in that function.

ie

Public Function UpdateData(ByVal SQL As String)
    Dim DataConnection as ADODB.Connection
    DataConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBlocation)

    'Run update

    DataConnection.Close()
    DataConnection.Dispose()
End Function

It is also good practice to have a try catch block in ther to catch any errors.

On a side note, it is also good practice to use parameterized queries. You can use a paramarray to pass any number of items to that function to do this. Here is an exampel of a Function I use for SQL sErver that accepts parameters.

    Public Function ExecuteQuery(ByVal SQL As String, ByVal ParamArray Parameters As Object()) As Boolean
        Dim con As New SqlClient.SqlConnection(connString)
        Dim oCmd As New SqlClient.SqlCommand(SQL, con)

        If Not Parameters Is Nothing Then
            Dim split As String()
            For Each p As String In Parameters
                Dim delimeter As Char() = {","c}
                split = p.Split(Delimeter, 2)
                If split(1) = String.Empty Then
                    oCmd.Parameters.AddWithValue(split(0), DBNull.Value)
                Else
                    oCmd.Parameters.AddWithValue(split(0), CType(split(1), Object))
                End If
            Next
        End If
        Try
            con.Open()
            If oCmd.ExecuteNonQuery() > 0 Then
                ExecuteQuery = True
            Else
                ExecuteQuery = False
            End If
        Catch ex As Exception
            strError = ex.Message
            ExecuteQuery = False
        Finally
            If Not oCmd Is Nothing Then
                oCmd.Dispose()
            End If
        End Try
        Return ExecuteQuery
    End Function 'Parameter Query

[Noa D]

In my program I access the database several times a minute so it's not realistic to open and close the connection each time. But I did try it anyway and it doesn't solve the problem.
I searched this error message and found some information about it here - http://support.microsoft.com/kb/331594, where there are some suggestions on how to solve the problem. They say to use an ADODB.Recordset object instead of an ADODB.Connection obect. I've never worked with a recordset before, so I'm not sure exactly what they mean. Do they suggest I copy my table to a recordset, update the data there and save it back to the database? But what if I want to make minor changes each time? I don't think a recordset would be an effective solution for me.

Also, I found a post by someone who said -
"The problem is that if one user is editing a record, you may find that
lots of other records (typically 30-50) are also locked. This is
because Access stores its data in "pages" of data that are 2k in size,
so any record that is locked on the page will automatically lock all
the others." (http://www.thescripts.com/forum/thread190244.html)

[Noa D]

Oh and another thing - on VB6 this code works perfect, with no such error.

[talkro]

recordsets are used in vb6, in vb.net, I would suggest that you use dataset.
With a dataset, you can load all your database structure and data in it and then update each datatable within the dataset. The modification are gonna be effective and accessible for the current through out the session. When you are done making your modification, you can then update the actual database. This has the advantage to reduce the access to the database and is pretty efficient for a stand alone application. If your database is to be used within a network and need a regular update of the database, you can still use the database but then make a more frequent update of the real database to make the changes available for everybody else.

[Noa D]

Okay, I've completely changed the way I work with the database.
Like you suggested, I switched the recordset to a dataset. I added DataAdapters for each table and set their Update,Insert and Delete commands. Then I used DataAdapter.Update(DataSet, TableName) and finally did AcceptChanges().
I get no errors during runtime but neither the dataset nor the database are updated. I've checked my commands and they're all correct. What does the Update method do exactly? When is the dataset changed and when is the database changed? Do you have any idea why nothing's happening?

Thanks.

[talkro]

I suppose you are correctly inserting, deleting and updating rows in your tables within the dataset by using th dataset.tables.rows or other methods. when a row in changed within the dataset, it status is set to inserted, modified or deleted which then trigger the right command when you update the database. By calling acceptChanges, you force these values to become unchanged and during the update event, the dataset thinks that nothing has been change and therefore nothing needs to be changed in the db.

[Noa D]

What I still don't understand, is how do I modify my dataset. I know I can go straight to a certain row by using dataset.tables.rows, but if so, what is the purpose of the update, insert and delete commands that you set for each dataadapter? They don't seem to change anything at all..

Here is a sample of what my code looks like:

Dim DS As New DataSet
Dim DA As New OleDbDataAdapter
Dim SelectCommand as new OleDbCommand
Dim UpdateCommand As new OleDbCommand
Dim Connection As OleDbConnection

'First I fill the dataset so that it contains all the data
SelectCommand.Connection = Connection
SelectCommand.CommandText = "SELECT * FROM TableName"
DA.SelectCommand = SelectCommand
DA.Fill(DS, TableName)

'Once I have the data stored in my dataset, I want to modify it
UpdateCommand .Connection = Connection
UpdateCommand .CommandText = "Update TableName etc."
DA.UpdateCommand = UpdateCommand 

'Finally, I want to update the database
DA.Update(DS.Tables(TableName))
DS.AcceptChanges()

I'm probably doing something wrong, but what is it?

[talkro]

I can't see anything wrong in your code, but wild guess, is your connection open?

[Noa D]

Yup, it's open, I keep checking.

Update: I just checked again and found out that it's not open (I must have changed something, because it was open before) BUT- it doesn't change anything even if I open it. Plus, When I use the Fill method to fill the dataset it does get all the data from the database even though it says the connection is closed, but it has to be open, otherwise how does the data get there? :\
It's just the Update method that doesn't work.