I'm recieving the following error message when I'm trying to delete a
record from a SQL table using ASP:
ADODB.Recordset error '800a0cb3'
Current Recordset does not support updating. This may be a limitation
of the provider, or of the selected locktype.
/delete_entry.asp, line 65
Here is the code from delete_entry.asp
<body bgcolor="white" text="black">
<%
'---- Recordset Variables ----
Dim delSQL ' Holds SQL string
Dim lngRecordNo ' Holds the record number to be deleted
Dim rsDel ' Holds the record set
Dim custFname ' Holds first name
Dim custLname ' Holds last name
Dim custDel ' Holds record info to delete
'Create an ADO connection object
Set connDel=Server.CreateObject("ADODB.connection")
connDel.ConnectionTimeout = 10
connDel.CommandTimeout = 20
'SQL Connection Def
connDel.Open "Driver={SQL Server};Server=someserver;Database=somedatabase; UID=userperson; PWD=password"
'Read in the record number that is being passed from delete_select.asp; this is the record
'to be deleted
lngRecordNo = CLng(Request.QueryString("ID"))
%>
<%
'Initialise the strSQL variable with an SQL statement to query the database
'Move the following line into the include file: RecsetRWDel2.inc
' delSQL = "SELECT CustomerID FROM Guest WHERE CustomerID=" & lngRecordNo
'Find Record to delete
custDel = "CustomerID=" & lngRecordNo
Response.Write "<br>The value of custDel is: " & custDel & "<br>"
rsDel.Find custDel
'Read First name and Last name
custFname = rsDel("ContactFirstName")
custLname = rsDel("ContactLastName")
'Delete the record from the database - line 65 below
rsDel.Delete adAffectCurrent
'Display total number of records in recordset after deletion
Response.Write "<p>Name deleted: " & custFname & " " & custLname & ", record number: " & lngRecordNo & "</p>"
Response.Write "<p>Customer Id number is: " & lngRecordNo & "</p>"
Response.Write "<p>The total number of records in the delete recordset is: " & rsDel.RecordCount & "</p>"
'Reset server objects
rsDel.Close
Set rsDel = Nothing
Set connDel = Nothing
'Return to the delete select page in case another record needs deleting
'This can only be used it there is no HTML on the page or it is called
'before any HTML on the page.
'Response.Redirect "delete_select.asp"
%>
Here is the code for RecsetRWDel2.inc:
<%
'--------------------------------------------------------------------
' Filename: RecsetRWDel2.inc
'
' Date created: 10/08/03
'
' Author: Kay LittleJohn
'
' Purpose: Commands for creating a recordset in Read/Write mode
'
'--------------------------------------------------------------------
'---- Recordset Variables ----
Dim strSQL
Set rsDel = Server.CreateObject ("ADODB.Recordset")
'strSQL = "SELECT CustomerID, ContactFirstName, ContactLastName FROM Guest WHERE CustomerID=" & lngRecordNo
strSQL = "SELECT CustomerID, ContactFirstName, ContactLastName FROM Guest"
rsDel.ActiveConnection = connDel
'--------------------------------------------------------------------
' adUseServer - To let the data store manipulate the cursor
' adUseClient - To let ADO manipulate the cursor
'--------------------------------------------------------------------
rsDel.CursorLocation = adUseClient
'--------------------------------------------------------------------
' Can see changes by other users, but new records are not visible.
' If other users delete records, then these will be inaccessible in
' the recordset.
'--------------------------------------------------------------------
rsDel.CursorType = adOpenDynamic
'--------------------------------------------------------------------
' The record is not locked until the changes to the record are
' committed to the data store, by of the Update method.
'--------------------------------------------------------------------
'rsDel.LockType = adLockPessimistic
'--------------------------------------------------------------------
' The source can be a name of a table, a stored query or procedure,
' or a SQL string, a Command object, or any other command applicable
' to the Provider
'--------------------------------------------------------------------
rsDel.Source = strSQL
'rsDel.PageSize = intPageSize
rsDel.Open
%>
Thanks in advance,
Kay
ADODB.Recordset error '800a0cb3'
