Using OleDbCommand in VB With Access DB

Question

I am using OleDbCommand to get a count of records in a table "Select count(MyField1) from MyTable". Using the following code.

SQL = "Select count(Field) from Table"
Dim DBcmd As New OleDbCommand(sql, _connection)
Return DBcmd.ExecuteReader

It locks the table so it can't be modified. I later try Alter Table MyTable Alter Column MyField2 Counter(1,1). MyField2 is an AutoNumber

I have tried to get around this with no luck. Anybody got any ideas?

All of this is with VB and an Access DB

I have tried using different connections but I always get the same error.

Answer 1

When using ADO.NET, you should pretty much always create, use and destroy your data-access objects where you need them. If you create your own data access layer, that means doing so in each method that gets or saves data. In this case, you ought to create, use and destroy all three of the connection, command and data reader. It makes little sense to return the whole data reader when the code that receives it is then just going to have to get the count from it. It makes far more sense to get the count yourself and return that. You can then close the data reader yourself. It is almost certainly the open data reader that is locking your table, rather than the open connection. Closing the connection will close the data reader too but you ought to be doing that regardless.

Using connection As New OleDbConnection(_connectionString),
      command As New OleDbCommand(sql, connection)
    connection.Open()

    Using reader = command.ExecuteReader()
        reader.Read()

        Return reader.GetInt32(0)
    End Using
End Using

Note that the connection string is stored in a member field, rather than the connection. Each object that is created with a Using statement is disposed at the corresponding End Using statement. As demonstrated, you can use one Using block to handle multiple objects if you don't need any extra code between the creation of each and disposal of each.

Having said all that, you should not be using ExecuteReader in this case anyway. ExecuteScalar exists specifically to get the first column of the first row of the result set of a query, which is exactly what you want. It basically does what the code above does, i.e. creates a data reader and gets the first value, but letting that method do that work simplifies your code:

Using connection As New OleDbConnection(_connectionString),
      command As New OleDbCommand(sql, connection)
    connection.Open()

    Return CInt(command.ExecuteScalar())
End Using

The Using statement to create the connection and command are still the same. Note that it is fine to have a Return inside a Using block. The object(s) created will still be disposed no matter how you leave the Using block, even if an exception is thrown.