Two recordsets and paging

[RushiShroff]

I have 2 queries in a page.
They are built in such a way that I can not make them into one query.So i have seperate two.

Now after executing them,I have 2 recordsets.
say Rs1 and Rs2.

Either of it may be empty.Or None.May be both of them are empty.

Now I want to loop through both and also want the combined result in a form of paged records-10 on each page.

How can I obtain this functionality ?
Beginners need not reply.Only experts please.

Regards,
Rushi@emqube.com

 

[Ovatvvon]

I made a database named "db1" which contains two tables: "users" and "stores"
Users:
- userPK
- userFirst
- userLast
- userEmail
Stores:
- storePK
- storeName
- storePhone

Then I made a page that runs through them both...obviously you will have two edit the page to suit your page layout and database ordinal names...but here is the one that works with the database I designed as declared above.

Hope this helps...
*******************************

<% @ Language=VBScript %>
<%
Option Explicit
Response.Expires = 0
%>
<!-- #include file=&quot;adovbs.inc&quot; -->
<%
Const pageSize = 3
Dim currentPage, conn, connString
Dim rs1, rs2, sql1, sql2
Dim totalPages, i

If Request.ServerVariables(&quot;Content_Length&quot = 0 Then
currentPage = 1
Else
currentPage = CInt(Request.Form(&quot;currentPage&quot)
SELECT CASE Request.Form(&quot;submit&quot
Case &quot;Previous&quot;
currentPage = currentPage - 1
CASE &quot;Next&quot;
currentPage = currentPage + 1
End Select
End If

Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
connString = &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=&quot; & Server.MapPath(&quot;db1.mdb&quot & &quot;;&quot;
conn.Open connString
Set rs1 = Server.CreateObject(&quot;ADODB.Recordset&quot
rs1.CursorLocation = 3
rs1.CursorType = 3
rs1.CacheSize = pageSize

Set rs2 = Server.CreateObject(&quot;ADODB.Recordset&quot
rs2.CursorLocation = 3
rs2.CursorType = 3
rs2.CacheSize = pageSize

sql1 = &quot;SELECT * FROM users ORDER BY users.userLast;&quot;
rs1.Open sql1, conn, , , adCmdText
sql2 = &quot;SELECT * FROM stores ORDER BY stores.storeName;&quot;
rs2.Open sql2, conn, , , adCmdText


rs1.pageSize = pageSize
If not(rs1.EOF) Then rs1.AbsolutePage = currentPage
rs2.pageSize = pageSize
If not(rs2.EOF) Then rs2.AbsolutePage = currentPage

If rs1.PageCount > rs2.PageCount Then
totalPages = rs1.PageCount
Else
totalPages = rs2.PageCount
End If

Response.write(&quot;<table border=1><Tr><Td valign='top'>&quot

For i = 1 to rs1.PageSize
If rs1.EOF Then Exit For
Response.write(i & &quot &quot; & rs1(&quot;userFirst&quot & &quot; &quot; & rs1(&quot;userLast&quot & &quot;<BR>&quot
rs1.MoveNext
Next

Response.write(&quot;</td><td valign='top'>&quot
Response.write(&quot;<table border=0>&quot
For i = 1 to rs2.PageSize
If rs2.EOF Then Exit For
Response.write(&quot;<tr><td><li> &quot; & rs2(&quot;storePhone&quot & &quot; </td><td> &quot; & rs2(&quot;storeName&quot & &quot;</td></tr>&quot
rs2.MoveNext
Next
Response.write(&quot;</table>&quot

Response.write(&quot;</td></tr></table>&quot

rs1.Close : rs2.Close : conn.Close
Set rs1 = Nothing : Set rs2 = Nothing : Set conn = Nothing


Response.write(&quot;<BR>&quot
Response.write(&quot;Page &quot; & currentPage & &quot; of &quot; & totalPages & &quot;<BR>&quot
Response.write(&quot;<FORM Action='&quot; & Request.ServerVariables(&quot;Script_Name&quot & &quot;' Method='post'>&quot
Response.write(&quot;<input type='hidden' name='currentPage' value='&quot; & currentPage & &quot;'>&quot

If currentPage > 1 Then
Response.write(&quot;<input type='Submit' name='submit' value='Previous'>&quot
End If
If currentPage <> totalPages Then
Response.write(&quot;<input type='Submit' name='submit' value='Next'>&quot
End If
Response.write(&quot;</FORM>&quot

%>

-Ovatvvon :-Q

 

[vasah20]

A little more about the queries would really help here...
how are you going to list the results?

At any rate, if you're using SQL Server or something else enterprise level, you could separate the SQL by semicolons, and then iterate through the recordsets using .NextRecordset.

ex:
sql = &quot;select * from tableA;select * from tableB&quot;
r.open sql, c
if not r.EOF then
'do stuff with the first recordset

r.getNextRecordset
'and so on...

I don't remember if you need to close the first rec, and then get the next one. Also, I'm not sure if the syntax is .NextRecordset or .GetNextRecordset. Check

http://www.devguru.com...

they'll show you the light.

Your 2nd option, is data shaping. Check out:

http://www.4guysfromrolla.com/webtech/092599-1.shtml

Lastly, if they aren't related in any way, then just check for .EOF for each respective recordset. And display results accordingly.

I'm not sure if I answered (or helped you answer) your Q at all... if not, post back with a little bit more info.

HTH - leo

------------
Leo Mendoza
lmendoza@garbersoft.net