Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID"
dbCmd.Connection.Open()
Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
If reader("SomeColumn") = SomeCalculatedValue Then
Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID")
dbCmd2.Connection.Open()
dbCmd2.ExecuteNonQuery()
dbCmd2.Connection.Close()
End If
End While
reader.Close()
This code worked well with an MS Access database, but when I changed toSQL Server, I get a database timeout error when attempting to do theDELETE. I suspect the reason is that the connection the reader has openhas the record locked so it cannot be deleted.
The SQL connection string I am using is something like this:
UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname"
The connection string I used for MS Access included the property"Mode=Share Deny None". I wonder if there is some similar way to tellSQL Server to allow editing of records that are open for reading withan OleDbDataReader.
Any help would be appreciated.
Hi,
If you are using SQL Server, use SqlClient class.
The SqlClient classes use the native SQL Server drivers to access a database, while the OleDb classes use the generic OLE-DB interface
HTH
|||Thanks for the tip, HTH, but the application was originally written touse MS Access databases using the OleDb classes, and then modificationswere made to upgrade to SQL Server. While using the native SQL driversmay be more efficient, I'm not looking to re-write the application atthe moment - I just want to resolve the issue at hand.
Can anyone tell me how to set up an SQL Server connection so thatopening a connection for read by an OleDbDataReader doesn't lock therecords? I know that I could read all the records into a datagrid, andthen delete the required rows, or that I could add the records thatneed to be deleted to an arraylist for later deletion. But this seemsmuch more complicated than it should be.
All I really need to do is open a datareader, read through the recordsone at a time, and delete a few of the records that match certaincriteria. This was easily done with MS Access, but with SQL Server(using the coonnection string described in my original post) I get atimeout error when I try to delete a record. I am presuming that atimeout occurs because the record I am trying to delete is locked bythe datareader.
Someone surely has a simple answer to this problem.
|||Go to Enterprise Manager click on tools then Query Analyzer and click on tools again then Options then click on Connections there are a few options see if one could help. But I would also run the SQL statement timing out in the Query Analyzer and click on show plan before execution to see if a minor change in the statement will make a difference and use the Profiler to create a trace on statement start and statement close events. I would also run a search for Query Governor in the BOL(books online) see what adjustments you need. Hope this helps.
