Error When Deleting Recordset

arimakidd · May 17th, 2006, 08:42 PM

I keep getting an error when I try to delete a row from a recordset. The funny thing is, in the access database the record is deleted, but the page returns an error so it doesn't Response.Write the message at the end. Here is the code:

Code:

Dim adoCon           
Dim serVisit  
Dim strSQL            
Dim edRec
edRec = CLng(Request.Form("record"))

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("gutter.mdb")

'Create an ADO recordset object
Set serVisit = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "DELETE service.* FROM service WHERE OrderNumber=" & edRec & ";" 

serVisit.LockType = 3
'Open the recordset with the SQL query
serVisit.Open strSQL, adoCon
'Tell the recordset we are deleting a record 
serVisit.Delete 

serVisit.Close
'Reset server objects
Set serVisit = Nothing
Set adoCon = Nothing

Response.Write "Record Deleted"

I would really appreciate some help here. The error I keep getting is this:

Code:

ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

ChrisScott · May 18th, 2006, 05:27 AM

Hi arimakid,

A DELETE statement will not return a recordset, it just deletes records meeting the criteria of the query. There is no need to open a recordset, just change the lines...

Code:

'Create an ADO recordset object
Set serVisit = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "DELETE service.* FROM service WHERE OrderNumber=" & edRec & ";" 

serVisit.LockType = 3
'Open the recordset with the SQL query
serVisit.Open strSQL, adoCon
'Tell the recordset we are deleting a record 
serVisit.Delete 

serVisit.Close
'Reset server objects
Set serVisit = Nothing

to...

Code:

strSQL = "DELETE service.* FROM service WHERE OrderNumber=" & edRec & ";" 
adoCon.Execute strSQL, , adCmdText

(I assume you have a reference to the ADO constants in your page)

HTH,

Chris

woodyz · May 18th, 2006, 09:49 AM

Hello arimakid,

Although the syntax for your delete statement will work, a properly formatted delete statement will look like this:

DELETE FROM service WHERE OrderNumber = 123

If you wish to use the Recordset.Delete method you would use a regular Select statement rather than a delete statement:

strSql = "Select * FROM service WHERE OrderNumber=" & edRec
...

Then open the connection as a dynamic cursor with an optimistic lock type.
Then call the Delete method of the recordset. This deletes the record currently pointed to by the cursor, which in this case will be the first (and only) record in your recordset if it is not EOF. The record is immediately deleted. If you use a lock type of Batch Optimistic you can mark a number of records for deletion and then call UpdateBatch to actually delete them all.

However, using the Execute method of the Connection object is the way I would typically delete a record as shown by Chris...

Woody Z http://www.learntoprogramnow.com

arimakidd · May 22nd, 2006, 10:59 AM

OK 2 quick questions. First how come ChrisScott you didn't have to set adCmdText to 1. My new problem involves a batch delete. Having said that what would I have to set the optimistic locktype to as well as the dynamic cursor to in order to delete several records.