Paging through an openschema recordset 1

[yesti]

Hi can you page through a recordset like:

set objRS=objConn.OpenSchema(adSchemaTables)

using objRS.PageSize, objRS.CacheSize, objRS.AbsolutePage, etc.?

I set the objConn.IsolationLevel to 4096 (adoIsolationLevel).

I read that adSchemaTables returns a server-side read-only recordset of cursortype static. Is the server-side attribute what is causing it not to work? I don't get any errors when I set the absoultepage, but the same records are returned no matter what page i set it to.

What I want to do is get a list of tables and column names in my database, but some tables have lots of column so I want to page through the results instead of waiting 5 minutes for the script to finish.

Server.ScriptTimeout=480
Response.Buffer=true
Dim objConn,objRS,objRS2,ctr,temp
Const adoIsolationLevel=4096
Dim iPageSize 'How big our pages are
Dim iPageCount 'The number of pages we get back
Dim iPageCurrent 'The page we want to show
Dim iRecordsShown 'Loop controller for displaying just iPageSize records
Dim I
iPageSize = 2 'since showing 2 records per page
If Request.QueryString("page&quot="" Then iPageCurrent=1 Else iPageCurrent=CInt(Request.QueryString("page&quot)
set objConn=Server.CreateObject("ADODB.Connection&quot
objConn.ConnectionTimeout=1000
objConn.IsolationLevel=adoIsolationLevel
objConn.open "crm"
ctr=0
Set objRS=objConn.OpenSchema(adSchemaTables)
objRS.PageSize=iPageSize
objRS.CacheSize=iPageSize
' Get the count of the pages
iPageCount = objRS.PageCount
' If the request page falls outside the acceptable range,
' give them the closest match (1 or max)
If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
If iPageCurrent < 1 Then iPageCurrent = 1
Set objRS2=objConn.OpenSchema(adSchemaColumns)
objRS.AbsolutePage = iPageCurrent
' Start output with a page x of n line
Response.Write &quot;<p align='left'><FONT SIZE='+1'>Page <B>&quot; & iPageCurrent & &quot;</B> of <B>&quot; & iPageCount & &quot;</B></FONT><br>&quot;
' Show &quot;previous&quot; and &quot;next&quot; page links which pass the page to view
If iPageCurrent > 1 Then Response.Write &quot; <a href='crmtables.asp?page=&quot; & iPageCurrent - 1 & &quot;'>[<< Prev]</a>&quot;
' show page numbers:
Response.Flush
For I = 1 To iPageCount
If I = iPageCurrent Then
Response.Write &quot; &quot; & I
Else
Response.Write &quot; <a href='crmtables.asp?s=1&page=&quot; & I & &quot;'>&quot; & I & &quot;</a>&quot;
End If
Next 'I
If iPageCurrent < iPageCount Then Response.Write &quot; <a href='crmtables.asp?page=&quot; & iPageCurrent + 1 & &quot;'>[Next >>]</a>&quot;
Response.Flush
Do While ctr < iPageSize And Not objRS.EOF
If left(objRS(&quot;Table_Name&quot,2)<>&quot;dt&quot; and left(objRS(&quot;Table_Name&quot,3)<>&quot;sys&quot; and objRS(&quot;Table_Type&quot=&quot;TABLE&quot; Then
Response.Write &quot;<p align=left>&quot;&objRS(&quot;Table_Name&quot&&quot;<br>&quot;
objRS2.MoveFirst
Response.flush
While Not objRS2.EOF
If objRS2(&quot;TABLE_NAME&quot=objRS(&quot;Table_Name&quot Then Response.Write &quot;   &quot;&objRS2(&quot;Column_Name&quot&&quot;<br>&quot;
objRS2.MoveNext
Wend
Response.Write &quot;</p>&quot;
ctr=ctr+1
End If
objRS.MoveNext
Loop
If iPageCurrent > 1 Then Response.Write &quot; <a href='crmtables.asp?page=&quot; & iPageCurrent - 1 & &quot;'>[<< Prev]</a>&quot;
For I = 1 To iPageCount
If I = iPageCurrent Then
Response.Write &quot; &quot; & I
Else
Response.Write &quot; <a href='crmtables.asp?s=1&page=&quot; & I & &quot;'>&quot; & I & &quot;</a>&quot;
End If
Next
If iPageCurrent < iPageCount Then Response.Write &quot; <a href='crmtables.asp?page=&quot; & iPageCurrent + 1 & &quot;'>[Next >>]</a>&quot;
objRS.Close
objRS2.Close
set objRS=nothing
set objRS2=nothing
objConn.Close
set objConn=nothing

 

[onpnt]

try taking out the Response.Flush

____________________________________________________
[sub]get the best answer to your questions by asking the best questions &quot;General FAQ&quot; faq333-2924[/sub]

[sub][sup]

http://www.onpntwebdesigns.com

[/sup][/sub]

 

[onpnt]

also, did you just not paste the End If's to all those If statements?

____________________________________________________
[sub]get the best answer to your questions by asking the best questions &quot;General FAQ&quot; faq333-2924[/sub]

[sub][sup]

http://www.onpntwebdesigns.com

[/sup][/sub]

 

[onpnt]

there's a.lso a while statemenet that needs to be restructured to this
While Not objRS2.EOF
If objRS2(&quot;TABLE_NAME&quot=objRS(&quot;Table_Name&quot Then
Response.Write &quot; &quot;&objRS2(&quot;Column_Name&quot&&quot;<br>&quot;
objRS2.MoveNext
Response.Write &quot;</p>&quot;
ctr=ctr+1
End If
Wend

____________________________________________________
[sub]get the best answer to your questions by asking the best questions &quot;General FAQ&quot; faq333-2924[/sub]

[sub][sup]

http://www.onpntwebdesigns.com

[/sup][/sub]

 

[onpnt]

missing constants
eg: Const adSchemaTables=20

suggestion, add the
<% Option Explicit %>
<!--#include virtual=&quot;/adovbs.inc&quot;-->

to your pages that use the const's


All honesty, I could not get this script to work properly. There are far to many
conditional issues going on in it that are missing End If's and the loops I don't think
are working the way they should be.

Suggestion. read

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

save this script as a new page and tear it down bit by bit

____________________________________________________
[sub]get the best answer to your questions by asking the best questions &quot;General FAQ&quot; faq333-2924[/sub]

[sub][sup]

http://www.onpntwebdesigns.com

[/sup][/sub]

 

[yesti]

Hi, I tried taking out the Response.Flush's and that helped a little, but it is still behaving strangely.

About the If's, if you make the entire statement on one line you don't need the end if. You can even nest a bit and not need them. both of these are legal statements:

If a Then b Else c
If a Then If b Then c

If you add an Else to the end of the second one, if goes with the If b group. Any more nesting than that and you are forced to put them on different lines. Sorry for the bad programming grammar, but one gets lazy after a while.

The reason for the <br> instead of the </p> in the while loop is because i want the table name and columns to be in one <p> tag with the columns separated by <br> then the next table being the next <p> tag. It is just a formatting thing.

Sorry, forgot to post the:
<!--#INCLUDE FILE=&quot;_include/adovbs.inc&quot; -->
it was at the top of the file before some html and the vbscript was further down the page. That takes care of undeclared constants.

Like I said, the biggest improvement was the removal of the response.flush's but the reason those were in there is so the page updates after it gets done with a table so the user is not left wondering if anything is happening. That is left over from when I had the entire page load at once and it took about 5 minutes(!) so I said &quot;I have to page this baby!&quot; Thanks for looking at my code!

 

[yesti]

I used the code from the article you posted and it works perfectly! The difference seems to be in the use of the Array when calling the adOpenTable/Columns. Otherwise, I used the exact same code to page through the recordset. Thanks again.