Enormous Recordset -----

[link9]

I am at a loss, and I'm taking a shot in the dark that someone will either be able to see something I can't see, or point out my error here. The problem is that I'm receiving a recordset that has tens of thousands of records from a query that should only produce 20 records. This normally means that I have some many-to-many (YIKES!) relationship, but that is NOT the case here...

Here is a copy of the query string that in the SQL Server Query Analyzer does produce the 20 record recordset.

SELECT * FROM voc 
LEFT OUTER JOIN demo ON voc.id = demo.id 
WHERE ((voc.code1 = 1) OR (voc.code2 = 1) OR (voc.code3 = 1) OR (voc.code4 = 1)) AND (region = 1)

When the query executes in my asp page, I try to iterate through it just to see how big it is and the script times out because there are soooooooo many records. Here is a lengthy portion of code just so you can see everything. Please help...

dim verbRS, con, strWhereClause, strSQL

set con = server.CreateObject ("ADODB.Connection")
set verbRS = server.CreateObject ("ADODB.Recordset")

con.Open("DSN=thedemo;UID=myUserName;PWD=myPassword")

verbRS.ActiveConnection = con
verbRS.CursorLocation = adUseClient
verbRS.CursorType = adOpenStatic
verbRS.LockType = adLockBatchOptimistic

strSQL = "SELECT * FROM voc LEFT OUTER JOIN demo ON voc.id = demo.id WHERE ((voc.code1 = " & request("initiative") & ") OR (voc.code2 = " & request("initiative") & ") OR (voc.code3 = " & request("initiative") & ") OR (voc.code4 = " & request("initiative") & "))"
strSQL = strSQL & " AND (region = 1)"

verbRS.Open strSQL

verbRS.ActiveConnection = nothing
con.close

'^^^^^^^^^^^^^^^^

response.write(&quot;<table>&quot;)

dim counter
counter = 1
do while not verbRS.EOF
	Response.Write(&quot;<tr><td>&quot;)
	Response.Write(counter)
	Response.Write(&quot;</td></tr>&quot;)
	counter = counter + 1
	verbRS.MoveNext 
loop

response.write(&quot;</table>&quot;)

I think that gets the meat of it. FYI, the script times out (depending on what machine I'm on) somewhere between 20,000 and 80,000 iterations of the loop.

thx in advance for any insight
Paul Prewett

 

[Nanda]

Paul,
to increase the timeout time, use
<%server.ScriptTimeout = 3600%>...or more as per your time

else, to fetch 20 records from database, use
<%verbRS.MaxRecords = 20
verbRS.ActiveConnection = local_dbConn
verbRS.Open strSQL,,3%>

HTH

 

[vasah20]

in your SQL statement, have you tried using the TOP keyword?

ex:
&quot;SELECT TOP 20 * FROM table&quot;...

if that doesn't do it for you then you can also explicitly declare how many records to return in the recordset. I think the property is

RS.MaxRecords = 20

but you might want to look that up. Doing it the sql way is preferred since the db server does all the work.

 

[link9]

Thank you for both your input...

I found the problem, and as is usually the case, it was a simple and stupid oversight on my part.

The vbConstants were not declared (forgot to add the adovbs.inc file), and when I put those in, it worked fine. Don't ask me why it didn't curse at me for not declaring them (option explicit was set)...

Anyways, it's now fixed.

Thanks!
Paul Prewett