October 29, 2003 at 11:30 am
I have an ASP app that opens 7 different recordsets on one connection. They're all various combinations of columns from tables in the same db. The app originally used 7 different SQL Select statements embedded in the code and used in the rs.open method. Some were exceedingly complex (8-10 lines of SQL code) so I thought to replace them with calls to stored procedures with parms. I'm still using rs objects, but using cmd objects in the rs.Open. I keep getting the above error on about the third or fourth recordset I open.
All the cursors on the rs.Open's are set to OpenStatic, LockOptimistic. Any ideas why I'm getting this error?
Example of the code. There are already 3 recordsets open when I get here. It fails on the rsRC.Open call:
set cmdTimeSheet = server.CreateObject("ADODB.Command")
cmdTimeSheet.ActiveConnection = dbconn
set rsTimeSheet = server.CreateObject("ADODB.Recordset")
cmdTimeSheet.CommandText = "Exec sp_timesheet_rsTimeSheet " & "'" & strreqdate & "' , '" & struser & "' ,'" & strSortBy & "'"
rsTimeSheet.Open cmdTimeSheet, ,3, 3
set cmdRC = server.CreateObject("ADODB.Command")
cmdRC.ActiveConnection = dbconn
set rsRC = server.CreateObject("ADODB.Recordset")
cmdRC.CommandText = "Exec sp_timesheet_rowcount "& "'" & strreqdate & "' , '" & struser & "'"
rsRC.Open cmdRC, , 3, 3
October 29, 2003 at 3:24 pm
Try using more than one connection object. The same connection cannot start the next query with pending results in MS SQL Server - we run into this alot with various apps.
Jeff
October 30, 2003 at 4:32 am
But then how do I know when the query is complete? I've gotten the code to work so that all the recordsets are established, but when I reference a field in a row in one of the recordsets I get the above error.
Even if I switch to multiple connections aren't I going to have problems when I get to referencing the results if one of the querys is incomplete?
October 31, 2003 at 4:39 am
I've changed the app to use a separate Connection object for each Recordset. This seems to have corrected the problem.
November 3, 2003 at 6:27 am
hi!
you shouldn't create a connection for each recordset as each connection consumes resources on your db server (don't forget to think of your webapp serving multiple requests of such a kind!
concerning your problem: use client side cursors (from ado 2.7 on you'll have to adjust your connection properites (CursorLocation) to be "adUseClient" - 3).
try to keep your code simple, like follows:
dim conn
dim rs1, rs2, rs3
set conn = server.CreateObject("ADODB.Connection")
conn.CurosorLocation = 3 ' adUseClient
conn.Open ...
set rs1 = conn.Execute("select * from ...")
set rs2 = conn.Execute(...)
set rs3 = conn.Execute(...)
...
set rs1 = nothing ' Don't the hell forget this!!!
set rs2 = nothing
set rs3 = nothing
set conn = nothing
best regards,
chris.
November 5, 2003 at 9:54 am
Thanks cneuhold. That fixed it. In fact the app also seems to run faster.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply