Sql result set too big

[Gill1978]

Hi,

I have an query that brings back 2 and a half thousand records, it takes AGES to write all these results to the screen. Is there some way of say bringing back 20 results at a time?

The code for the result set brought back is:

<table border=&quot;0&quot; cellSpacing=&quot;0&quot; cellPadding=&quot;0&quot; width=&quot;100%&quot;>
<thead>
<tr height=&quot;28&quot;><td class=&quot;header&quot; COLSPAN=&quot;4&quot; style=&quot;font-size: 18&quot;>Contracts</td></tr>
<tr height=&quot;12&quot;>
<td style=&quot;width: 100&quot;>Contract</td>
<td>Description</td>
<td style=&quot;width: 40&quot;>Year</td>
<td style=&quot;width: 80&quot;>Agent</td>
</tr>
</thead>
<% dim oRS, i
set oRS = OpenDBQuery(&quot;Select top 250 * From Contract&quot
i=0
while not oRS.EOF %>
<tr class=&quot;TR<%=((i mod 2)+1)%>&quot; style=&quot;height: 17px&quot;
onClick=&quot;window.location.href='Test2.asp?Contract=<%=Server.URLEncode(oRS.Fields(&quot;bindno&quot)%>&Year=<%=oRS.Fields(&quot;bindyr&quot%>'&quot;
onMouseOver=&quot;Javascript:SelectionHoverOver(this,true);&quot;
onMouseOut=&quot;Javascript:SelectionHoverOut(this,<%=((i mod 2)+1)%>);&quot;>
<td><%=oRS.Fields(&quot;bindno&quot%></td>
<td><%=oRS.Fields(&quot;binddesc&quot%></td>
<td><%=oRS.Fields(&quot;bindyr&quot%></td>
<td><%=oRS.Fields(&quot;Agent&quot%></td>
</tr>
<% i=i+1
oRS.MoveNext
wend
oRS.close
set oRS = nothing
%>
</table>

Can anyone help?????

Thanks

Julie

 

[mithrilhall]

Try paging:

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

====================================
I love people. They taste just like
chicken!

 

[Gill1978]

I've tried doing the paging code ... but i get the following error and i haven't a clue why:

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/Miller_Local/codeblocks/db.asp, line 3, column 7
Public Function OpenDBConnection
------^


Browser Type:
Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)

Page:
GET /Miller_Local/Test.asp

The OpenDBConnection file contains:

<SCRIPT LANGUAGE=&quot;VBSCRIPT&quot; RUNAT=&quot;SERVER&quot;>

Public Function OpenDBConnection
Dim oConn
Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot
oConn.ConnectionString = &quot;Driver = SQLServer; TrustedConnection = true; Server=&quot; & Application.Contents(&quot;DBServer&quot
oConn.Open
Set OpenDBConnection = oConn

End Function


Function OpenDBQuery(sSQL)

dim oRS
set oRS = Server.CreateObject(&quot;ADODB.Recordset&quot
oRS.Open sSQL, &quot; driver={SQL Server}; server=&quot; & Application.Contents(&quot;DBServer&quot & &quot;,1433 ; database=nastats; network=dbmsscon; TrustedConnection=true&quot;,1
Set OpenDBQuery = oRS

End Function


</SCRIPT>

Can anyone help?

Thanks
Julie

 

[Snaggs]

Julie,

Go out to SAMS website at

http://sams.informit.com

In the search window type &quot;ASP&quot; without the quotes. Go to the second page and click on the white book with the title &quot;Sams Teach Yourself Active Server Pages in 24 Hours&quot;.

Now Click on downloads below the book.

Download the file called aspin24.zip.

Open the zip file and extract &quot;Listing1701.asp&quot;

If you don't have a DSN setup for the Northwinds Traders Database, you'll have to set one up to work with the example. This should answer your question about paging through records. I have the book but it was a lot of code to type in and I wouldn't feel right copying their code here.

Good Luck,
Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.