I'm using the Beginning ASP Databases book and have written a script to perform a simple batch update and encountered the following error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E25)
Row handles must all be released before new ones can be obtained.
Error occurs at 1st: objRS.MoveNext


Any ideas how to resolve it? My code is as follows:

<%@Language=VBScript%>
<% option explicit %>
<%
dim adOpenStatic, adLockBatchOptimistic
adOpenStatic = 3
adLockBatchOptimistic = 4
dim objRS
set objRS = server.createobject("adodb.recordset")
dim strSource
strSource = "Provider=SQLOLEDB.1;DRIVER={MS SQL-Server};UID=MyUserID;PWD=MyPassword;DATABASE=Sailo rs;SERVER=MyServerName"
dim sqlStorProc
sqlStorProc = "qAllBoatNames"
objRS.open sqlStorProc, strSource, adOpenStatic, adLockBatchOptimistic
response.write "1st value: " & objRS("BoatName") & "<br>"
objRS("BoatName") = "A Sweet Song"
objRS.MoveNext 'error occurs here
response.write "2nd value: " & objRS("BoatName") & "<br>"
objRS("BoatName") = "Blackbeard"
objRS.UpdateBatch
objRS.MoveFirst
response.write "Changed 1st value: " & objRS("BoatName") & "<br>"
objRS.MoveNext
response.write "Changed 2nd value: " & objRS("BoatName") & "<br>"
objRS.close
set objRS = nothing
%>


Thanks

Wills

This is apparently a bug in ADO. Try using a client-side cursor instead (by default you get a server-side cursor) by setting objRS.CursorLocation = adUseClient before opening the recordset.

See Microsoft Knowledge Base Article 257731 for details.

hth
Phil

Phil,

Thanks for the information. I read the article and adjusted the code:

<%@Language=VBScript%>
<% option explicit %>
<%
dim adOpenStatic, adLockBatchOptimistic, adUseClient
adOpenStatic = 3
adLockBatchOptimistic = 4
dim objRS
set objRS = server.createobject("adodb.recordset")
dim strSource
strSource = "Provider=SQLOLEDB.1;DRIVER={MS SQL-Server};UID=MyUserID;PWD=MyPassword;DATABASE=Sailo rs;SERVER=MyServerName"
dim sqlStorProc
sqlStorProc = "qAllBoatNames"
objRS.CursorLocation = adUseClient 'error occurs here
objRS.open sqlStorProc, strSource, adOpenStatic, adLockBatchOptimistic
response.write "1st value: " & objRS("BoatName") & "<br>"
objRS("BoatName") = "A Sweet Song"
objRS.MoveNext
response.write "2nd value: " & objRS("BoatName") & "<br>"
objRS("BoatName") = "Blackbeard"
objRS.UpdateBatch
objRS.MoveFirst
response.write "Changed 1st value: " & objRS("BoatName") & "<br>"
objRS.MoveNext
response.write "Changed 2nd value: " & objRS("BoatName") & "<br>"
objRS.close
set objRS = nothing
%>
---------------------------------
My new error is as follows:

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Any other ideas?

Thanks,

Wills

I changed the code to the following:

<%@Language=VBScript%>
<% option explicit %>

<%
dim objRS
set objRS = server.createobject("adodb.recordset")
dim strSource
strSource = "Provider=SQLOLEDB.1;DRIVER={MS SQL-Server};UID=MyUserID;PWD=MyPassword;DATABASE=Sailo rs;SERVER=MyServerName"
dim sqlStorProc
sqlStorProc = "qAllBoatNames"
objRS.CursorLocation = adUseClient
objRS.open sqlStorProc, strSource, adOpenStatic, adLockBatchOptimistic
response.write "1st value: " & objRS("BoatName") & "<br>"
objRS("BoatName") = "A Sweet Song"
objRS.MoveNext
response.write "2nd value: " & objRS("BoatName") & "<br>"
objRS("BoatName") = "Blackbeard"
objRS.UpdateBatch
objRS.MoveFirst
response.write "Changed 1st value: " & objRS("BoatName") & "<br>"
objRS.MoveNext
response.write "Changed 2nd value: " & objRS("BoatName") & "<br>"
objRS.close
set objRS = nothing
%>

Now it does not appear to extract the data from the table. The first two values should be Lyric and Jacko. Instead it displays what I want to change these to and does not update the table.

Nevermind, I used the wrong stored procedure. Duh - It works fine now.