Opening two recordsets from the same table - both open simultaniously

[mail]

How can I open two recordsets from the same database table, both of
them being open simultaniously?

rs1.open sqlstring,connection

rs2.open sqlstring,connection

When I do this I get the ADODB.Recordset error '800a0bb9'

Help anybody?

 

[Jeff Dillon]

And it works ok if you only open one?

Jeff

 

[Bob Barrows]

How can I open two recordsets from the same database table, both of
them being open simultaniously?

rs1.open sqlstring,connection

rs2.open sqlstring,connection

When I do this I get the ADODB.Recordset error '800a0bb9'

"Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another"?

I would be looking elsewhere for your error ...
http://www.adopenstatic.com/FAQ/800a0bb9.asp

 

[mail]

I would be looking elsewhere for your error ...http://www.adopenstatic.com/FAQ/800a0bb9.asp

I have read that article of course, but somehow it does not help.

It's not clear if this is a connection problem - i.e. it's impossible
to have two recordsets open to the same table over the same
connection, or a cursor conflict? I have tried all the cursor /
locking conbinations I can think of, but still no go.

 

[mail]

And it works ok if you only open one?

Jeff

Yes of course.

 

[carrzkiss]

Hello jazzis

I put together a little code, just replace your information with this one.
(or) Create a Database with 2 tables.
Give them the names of: (Does not matter really what they are, you can
change if you like)
DB Name = Upload.mdb
Table Name=========
DestFileName = text
UploadID = AutoNumber

Use this code and run it, and see what happens.
I have noticed that sometimes it is the connection string that causes the
problem.
So if you are not using the Jet Connection string, then maybe it is the
problem.
===============================================
<%
dim rs1, rs2, cnconn, sqlstring

Set cnconn = Server.CreateObject("ADODB.Connection")
cnconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
_
Server.MapPath ("db/Upload.mdb") & ";"
cnconn.Open
%>
<%

sqlstring = "SELECT DestFileName, UploadID FROM UploadsFiles"
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.open sqlstring,cnconn
%>
<%

sqlstring = "SELECT DestFileName, UploadID FROM UploadsFiles"
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.open sqlstring,cnconn
%>

<%=rs1("DestFileName")%> & <%=rs2("UploadID")%>
===============================================

Hope this helps.

Wayne Barron

 

[Anthony Jones]

How can I open two recordsets from the same database table, both of
them being open simultaniously?

rs1.open sqlstring,connection

rs2.open sqlstring,connection

When I do this I get the ADODB.Recordset error '800a0bb9'

Help anybody?

It seems to me the most likely problem is not that you are accessing the
same table but that you are using the same connection.

You haven't specified which database you are using but on SQL server the
default is a forward only cursor (actually not a cursor just a firehose
stream of records).

You can't open another recordest on that connection until you've consumed
the existing stream of records. Even with static local cursors you need to
ensure the first recordset its completely filled before reusing the
connection for another recordset.

I suggest you open two connections.

 

[Bob Barrows]

It seems to me the most likely problem is not that you are accessing
the same table but that you are using the same connection.

You haven't specified which database you are using but on SQL server
the default is a forward only cursor (actually not a cursor just a
firehose stream of records).

You can't open another recordest on that connection until you've
consumed the existing stream of records. Even with static local
cursors you need to ensure the first recordset its completely filled
before reusing the connection for another recordset.

I suggest you open two connections.

That was my first thought, but that problem would produce a different error
message (I think). If this is the problem, however, the solution is to
either, a) as you say, use two connections, or b) swtich to using
client-side cursors by setting the recordset objects' (or connection
object's) CursorLocation property to 3 (adUseClient).

 

[Anthony Jones]

That was my first thought, but that problem would produce a different
error message (I think). If this is the problem, however, the solution is
to either, a) as you say, use two connections, or b) swtich to using
client-side cursors by setting the recordset objects' (or connection
object's) CursorLocation property to 3 (adUseClient).

Experience tells me that when something looks wrong _and_ is broken, fixing
what looks wrong often fixes what is broken. Its certainly a good first
step IMO.

 

[carrzkiss]

I sent over a code example that uses good connections
Just take a look up the thread for "carrzkiss" and you will
Find the example that I have presented to you and the group.

Let me know if it helps you any?
Wayne

 

[carrzkiss]

Here it is again.

Hello jazzis

I put together a little code, just replace your information with this one.
(or) Create a Database with 2 tables.
Give them the names of: (Does not matter really what they are, you can
change if you like)
DB Name = Upload.mdb
Table Name=========
DestFileName = text
UploadID = AutoNumber

Use this code and run it, and see what happens.
I have noticed that sometimes it is the connection string that causes the
problem.
So if you are not using the Jet Connection string, then maybe it is the
problem.
===============================================
<%
dim rs1, rs2, cnconn, sqlstring

Set cnconn = Server.CreateObject("ADODB.Connection")
cnconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
_
Server.MapPath ("db/Upload.mdb") & ";"
cnconn.Open
%>
<%

sqlstring = "SELECT DestFileName, UploadID FROM UploadsFiles"
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.open sqlstring,cnconn
%>
<%

sqlstring = "SELECT DestFileName, UploadID FROM UploadsFiles"
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.open sqlstring,cnconn
%>

<%=rs1("DestFileName")%> & <%=rs2("UploadID")%>
===============================================

Hope this helps.

Wayne Barron

 

[Bob Barrows]

Here it is again.
So if you are not using the Jet Connection string, then maybe it is

You might consider finding out what database the person is using before
taking the time to write all that code
Just a suggestion ...

 

[Bob Barrows]

Well, given that a static local cursor by definition completely consumes the
server-side firehose cursor that was used to populate it, that appears to be
a given. ;-)

 

[mail]

A big THANK YOU to everybody who is trying to help. Here are some
points:

1. I tried this with Access and SQL Server - same results.

2. Please see the actual code below. If anybody has an idea how to do
this in an alternative way - fire away.

3. I'm very much aware of the meaning of cursor and lock types - as I
said I've tried alraedy all combinations (i.e. 2,3 / 3,3, etc) to no
avail.

4. I have not tried yet doing it over two separate connections - this
will probably work, but again this is a challenge to solve it over one
connection

CODE
============================================================================


set rs=server.createobject("adodb.recordset")
set rs_int=server.createobject("adodb.recordset")

sqlstring="select * from anamnesis_wordgroups where wordgroup_id=" &
request.form("id_old")

rs.open sqlstring,connection,2,3

do while not rs.eof

sqlsrtingint="select * from anamnesis_wordgroups where patient_id=" &
rs("patient_id") & " and question_id=" & rs("question_id") & " and
wordgroup_id=" & request.form("id_new")

rs_int.open sqlstringint,connection,2,3

if rs_int.eof then
sqlstring="delete from anamnesis_wordgroups where patient_id=" & rs
("patient_id") & " and question_id=" & rs("question_id") & " and
wordgroup_id=" & request.form("id_old")
else
sqlstring="update anamnesis_wordgroups set wordgroup_id=" &
request.form("id_new") & " where patient_id=" & rs("patient_id") & "
and question_id=" & rs("question_id") & " and wordgroup_id=" &
request.form("id_old")
end if

rs_int.close

connection.execute sqlstring

rs.movenext

loop

rs.close

 

[Bob Barrows]

3. I'm very much aware of the meaning of cursor and lock types - as I
said I've tried alraedy all combinations (i.e. 2,3 / 3,3, etc) to no
avail.

Well, from what I can see, you have not tried client-side cursors. Set the
CursorLocation property of one or both of your recordsets to adUseClient to
force a client-side static cursor.

 

[Steve]

If it makes you feel better about it being possible. I was able to
open two recordsets (containing the same dataset) concurrently on the
same connection at the same time and then run and update the same
table using Cursor/Lock types of 3 for both. I was too lazy to modify
your code so I used some old existing code of my own, but it was
essentially the same code. HTH!

 

[mail]

If it makes you feel better about it being possible.  I was able to
open two recordsets (containing the same dataset) concurrently on the
same connection at the same time and then run and update the same
table using Cursor/Lock types of 3 for both.  I was too lazy to modify
your code so I used some old existing code of my own, but it was
essentially the same code.  HTH!

Sorry it does not work for me

rs.open sqlstring,connection,3,3
rs_int.open sqlstring_int,connection,3,3

 

[mail]

Well, from what I can see, you have not tried client-side cursors. Set the
CursorLocation property of one or both of your recordsets to adUseClient to
force a client-side static cursor.

I have tried it now

rs,cursorlocation=3 for both and still it won't work.

 

[Bob Barrows]

I have tried it now

rs,cursorlocation=3 for both and still it won't work.

.... which tends to reinforce my theory that your problem is elsewhere.
You're still getting the "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another" error?

 

[mail]

... which tends to reinforce my theory that your problem is elsewhere.
You're still getting the "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another" error?

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Yes - same error.

Of course there is always the second connection solution.