How do you use a session recordset? 1

[jsteph]

I've got an issue I don't understand. I have an asp page where I set a session variable as a recordset, for the purposes of having other asp pages read what I'd expect to be the same data. It does not work this way, and I'm wondering if anyone can help here.

First, in the Session.On_start of global.asa, I do
set session("globalrs") = server.createobject("adodb.recordset")

Then, in Page1.asp I do (pseudocode):

set rs = myDataClass.GetRecset() 
set session("globalrs") = rs

(above is a dll that returns an ado recordset. It works fine)

Then, I loop through that set:

Do until session("globalrs").eof
   iRecNum = iRecNum + 1
   response.write "<iframe src=Page2.asp?rec=" & iRecNum & ">"
   session("globalrs").movenext
loop

Now, in Page2.asp (the src of the above Iframe), I have:

iRecNum = Request("rec")
response.write "Record: " & iRecNum
for i = 0 to session("globalrs").fields.count -1
   session("globalrs").fields(i)
next

Now...I may have typos above and it's pseudocode, so please ignore that, because everything works EXCEPT:
The list of iframes printed out shows (assume 5 records in table, and 2 fields):
Record: 1 I'm Record 1 Field1 I'm Record 1 Field2
Record: 2 I'm Record 1 Field1 I'm Record 1 Field2
Record: 3 I'm Record 1 Field1 I'm Record 1 Field2
Record: 4 I'm Record 1 Field1 I'm Record 1 Field2
Record: 5 I'm Record 1 Field1 I'm Record 1 Field2

So basically, session("globalrs") get's a valid set of 5 records, and page1 loops through them fine. But in Page2.asp, session("globalrs") is always on record 1!! Why is this? If it's the same as in page 1, then it should be on the same record as Page1.asp in the loop. The loop is going fine, and the record ordinal num (iRecNum) is passed fine to Page2.asp, and the recordset is read properly in Page2.asp, but it's the same first record. What is up with that?
--J

 

[CassidyHunt]

This works for me.

<%
dim cn
dim rs
dim rs2

Set cn = Server.CreateObject ("ADODB.Connection")
Set rs = Server.CreateObject ("ADODB.Recordset")
Set rs2 = Server.CreateObject ("ADODB.Recordset")

with cn
	.Provider = "Your Provider"
	.ConnectionString = "Your Connection String"
	.Open
end with

rs.Open "Select * from table",cn,3,3

set Session("myrs") = rs
set rs2 = Session("myrs")

do until rs2.EOF = true
	Response.Write rs2("Field") & "<BR>"
	rs2.MoveNext 
loop

%>

Basically I ended up creating another recordset object and point the session variable back at it.

Let me know how it turns out.

Cassidy

 

[Sheco]

This is not really a comment on your problem but...

If all users will be using the same recordset then maybe put it in an Application variable instead of a session variable... then you wont have a separate copy for each session. This will impact your ability to scale to many users.

 

[jsteph]

Cassidy,
Is that all in the same asp page? The way you've posted it, it appears to be in the same page. In my example there are 2 asp pages. Where my problem is would start directly after the line in your example:
set Session("myrs") = rs

...after that line--the asp page then calls a second asp page. The call to this second page is via a 'src' tag in an iFrame, so directly after that you would do

do until rs.eof
response.write "<iframe src=page2.asp>"
rs.movenext
loop
*********************** END OF ASP PAGE **********

Now your page2 would be:

Response.Write Session("myrs")("SomeField") & "<BR>"


...does that work for you? For me, the output here in page2.asp is always the first record, even though the session recordset should be on whatever record page1 is on. I even tried using just session("myrs") in the page1 loop, and it was still the same result.

What it seems like is happening is that the session variable is not truly a session variable, it's just a copy, and there is no synchronization, which--if that it the case--is very, very bad in my opinion. Does it work the same for you if you use 2 pages?
Thanks,
--J
PS sheco,
Thanks for that, but the recordset will not be global to all users, just the session user.

 

[CassidyHunt]

Your right I did do it on the same page. However I revamped it for two pages and it works great.

Here is how I did it:

Page 1

<form method="post" action="page2.asp">
<%
dim cn
dim rs

Set cn = Server.CreateObject ("ADODB.Connection")
Set rs = Server.CreateObject ("ADODB.Recordset")

with cn
    .Provider = "Your Provider"
    .ConnectionString = "Your Connection String"
    .Open
end with

rs.Open "Select * from table",cn,3,3

set Session("myrs") = rs
set cn = nothing
set rs = nothing
%>
<input type="submit">
</form>

Page 2

<%
dim rs

Set rs = Server.CreateObject ("ADODB.Recordset")
set rs = Session("myrs")

do until rs.EOF = true
    Response.Write rs("Field") & "<BR>"
    rs.MoveNext 
loop

set rs = nothing
%>

I have tried it on 6 different tables and not had a single problem.

BTW The only issue I can see you running into, and I might be wrong, but I believe there is a size limit to a session variable. I ran into something like that in a couple of my application. I ended up creating an XML file on the client side and passing that between each page.

Otherwise the above example works for me. Now I get a similiar problem to what your describing when I try to go directly from the session object. That is why I instantuate another recordset object on the second page pass the session variable to it.

Let me know if you have an issue.

 

[jsteph]

Cassidy,
I think I may have been unclear in my example. I can do what you've done fine. What the problem is--say in your example you stop at the middle of the recordset--or you do a .Find in Page1. Then when you call page 2--don't loop, just read the current record. It should be the exact record where the .Find in page1 left you--but it's not. That's the problem.

See, I'm looping in Page1, and in the middle of the loop on each record, I'm calling Page2 and just reading the current record of the session recordset. Again--it should be the same current record in page1, but it's always the first record--which leads me to believe that it's not really the same recordset--asp is somehow just making a copy instead of giving me a pointer to the exact same set. The pointer is what I expect--then I'd be on the same record. But a copy--that's just a copy and it always starts on BOF, which totally defeats the purpose of 'sharing' the same recordset within the session.
Thanks for any insight you can find on this,
--J

 

[CassidyHunt]

What type of cursor are you using on your recordset? I changed the code above to this:

Page 1

<form method="post" action="page2.asp">
<%
dim cn
dim rs

Set cn = Server.CreateObject ("ADODB.Connection")
Set rs = Server.CreateObject ("ADODB.Recordset")

with cn
    .Provider = "Your Provider"
    .ConnectionString = "Your Connection String"
    .Open
end with

rs.Open "Select * from table",cn,3,3
rs.move 50,1

set Session("myrs") = rs

Response.write rs("Field")

set cn = nothing
set rs = nothing
%>
<input type="submit">
</form>

Page 2

<%
dim rs

Set rs = Server.CreateObject ("ADODB.Recordset")
set rs = Session("myrs")

Response.write rs("Field")

set rs = nothing
%>

The value displayed is record 50 on both pages. If your using find I am curious if your using a client side cursor instead of a server side cursor. See what happens when you try to do a recordcount:

Response.Write rs.RecordCount
[/count]

If that comes back with something like -1 or an error about bookmarks then you need to adjust your cursor.  I wrote a dll class in VB6 that I change the cursor on and I get the result your talking about.  

Here are the values for cursors in VBScript
Cursor Types Values
------------------------------
adOpenForwardOnly = 0
adOpenKeyset = 1
adOpenDynamic = 2
adOpenStatic = 3

Lock Types
-------------------------
adLockReadOnly = 1
adLockPessimistic = 2
adLockOptimistic = 3
adLockBatchOptimistic = 4

Cursor Location
-------------------------
adUseServer = 2
adUseClient = 3

Let me know if this helps.

Cassidy

 

[jsteph]

Cassidy,
Thanks, that sounds promising--I was using a firehose cursor, so that may have been the problem. I'll look at that this evening and let you know! Thanks again,
-J

 

[jsteph]

Cassidy,
Thanks very much, that seemed to do the trick!
J