Current Recordset does not support updating

Other SQL Server Topics
1

I get the following error when trying to delete a recordset from an SQL Server DB:

ADODB.Recordset error '800a0cb3'
Current Recordset does not support updating. This may be a limitation of the provider,
or of the selected locktype.
/sqlwishlist/Database2_interface/wishlists1/editor/deletionverified.asp,
line 33

The code is:

WISHLIST RECORD DELETION VERIFICATION

<%
Dim blnNew

   blnNew = False

Dim objConn, objRS
Dim qry, connectstr
Dim listid
listid = Session("localid")
tablename = "wishlists"

connectstr="Driver={SQL Server};SERVER=SSS.HHH;DATABASE=DB;UID=IDIDID;PWD=PWPWPW#;"

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Mode = adModeReadWrite

objConn.Open connectstr , adopenstatic, adLockOptimistic

Qry= "SELECT * FROM wishlists Where id = " & listid & ";"

Set objRS = objConn.Execute(qry)

objRS.Delete

objRS.close
Set objRS = Nothing

%>

The failure occurs at the delete command.

Web suggestions have been no help.

2

I think you cannot use the Delete method by filling the recordset using Execute as you are doing. See the examples at the bottom of this page. They use the Open method on the recordset, call the Delete method on the record set to delete in the recordset, and then call the UpdateBatch method to send that to the database.

Alternatively, you can change your select statement to a delete statement with the correct where clauses and use Execute.

If you are allowed to create stored procedures in the database, a better approach would be to create a stored procedure to delete the records and call that with the appropriate parameters.

3

The solution is not relevant to SQL Server. You should change in your Visual basic code. Please refer the below link
http://bytes.com/topic/visual-basic/answers/702292-current-recordset-does-not-support-updating

4

Thanks - Solved using open instead of execute, and adding a METADATA type = "typelib" to get past a "wrong type" problem with adLockOptimistic.