asp to sql server - batch update

Question

asp to sql server - batch update

matthersjr

SSC Eights!

Points: 810
More actions
July 29, 2003 at 9:27 am

#159557

I've written an asp 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.
Any ideas how to resolve it? My code is as follows:
response.write "1st value: " & objRS("FName") & " "
objRS("FName") = "Tom"
objRS.MoveNext
response.write "2nd value: " & objRS("FName") & ""
objRS("FName") = "Tim"
objRS.UpdateBatch
objRS.MoveFirst
response.write "Changed 1st value: " & objRS("FName") & " "
objRS.MoveNext
response.write "Changed 2nd value: " & objRS("FName")
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

Check that your Recordset supports the MoveFirst after the MoveNext method. I think that when you have CursorType = adOpenStatic or adOpenForwardOnly or adOpenReadOnly that you can't do this. Make sure it is CursorType = adOpenDynamic. I apologize ahead of time if I spelled the ado constants incorrectly...

matthersjr SSC Eights! Points: 810 More actions · Answer 2

Thanks for the information. I 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=Sailors;SERVER=MyServerName"

dim sqlStorProc

sqlStorProc = "qAllBoatNames"

objRS.CursorLocation = adUseClient 'error occurs here

objRS.open sqlStorProc, strSource, adOpenStatic, adLockBatchOptimistic

response.write "1st value: " & objRS("BoatName") & " "

objRS("BoatName") = "A Sweet Song"

objRS.MoveNext

response.write "2nd value: " & objRS("BoatName") & " "

objRS("BoatName") = "Blackbeard"

objRS.UpdateBatch

objRS.MoveFirst

response.write "Changed 1st value: " & objRS("BoatName") & " "

objRS.MoveNext

response.write "Changed 2nd value: " & objRS("BoatName") & " "

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

jpipes SSChampion Points: 12230 More actions · Answer 3

Are you including adovbs.inc? If not, you need to replace the ad-prefix Constants with the numbers they represent.

matthersjr SSC Eights! Points: 810 More actions · Answer 4

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=Sailors;SERVER=MyServerName"

dim sqlStorProc

sqlStorProc = "qAllBoatNames"

objRS.CursorLocation = adUseClient

objRS.open sqlStorProc, strSource, adOpenStatic, adLockBatchOptimistic

response.write "1st value: " & objRS("BoatName") & " "

objRS("BoatName") = "A Sweet Song"

objRS.MoveNext

response.write "2nd value: " & objRS("BoatName") & " "

objRS("BoatName") = "Blackbeard"

objRS.UpdateBatch

objRS.MoveFirst

response.write "Changed 1st value: " & objRS("BoatName") & " "

objRS.MoveNext

response.write "Changed 2nd value: " & objRS("BoatName") & " "

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.

jpipes SSChampion Points: 12230 More actions · Answer 5

Not sure. You may want to post this on an ASP forum.

matthersjr SSC Eights! Points: 810 More actions · Answer 6

matthersjr

SSC Eights!

Points: 810

July 30, 2003 at 10:07 am

#467791

Right; thanks for your help.

Cheers