Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Active Server Pages (ASP)
Expertise: Beginner
Jan 18, 2000

Use a Where Clause in SQL Instead of a Filter

Question:

Here is a snippet of my code:

set conn = Server.CreateObject("ADODB.Connection")
conn.open "gorillaclasses"
set rs = Server.CreateObject("ADODB.Recordset")

stmt = "SELECT class,time,instructor FROM " & clubtable 
   rs.open clubtable, conn, adOpenStatic,,adCmdTable %>
rs.filter = "dayslot = '7' and timeslot = '3'"

I want to loop the filter and set the dayslot number to a variable so that with each loop, the number increases by one. I've set up a "do while" deal and the variable, but I cannot get the filter to work if I use a variable. Is this possible? Can I use a variable in the rs.filter statement? If so, how would I construct it?

Answer:

Instead of using a filter, try a different approach. When you use a filter, you are accessing all the records first and generating a recordset. Then you are filtering the data to only view what is needed. This approach is very wasteful. Instead, bring back only the records you need with a Where clause in your SQL Statement. So, rewrite your code as follows (assuming you want to set the value of the dayslot variable from 1 to 7):

set conn = Server.CreateObject("ADODB.Connection")
conn.open "gorillaclasses"
set rs = Server.CreateObject("ADODB.Recordset")

' -- (NEW CODE FROM HERE ON) -----
Dim i
For i = 1 to 7
   stmt = "SELECT class,time,instructor FROM " & _
     "clubtable WHERE dayslot = '" & i & "' " & _
     " and timeslot = '3'"
   ' -- generate the fastest kind of recordset, 
   ' -- a forward only recordset
   set rs = conn.Execute (stmt)
   ' --- Process the recordset
   do while not rs.eof
      ... your other code here
   loop
' -- proceed to the next dayslot
Next
' -- release resources
set rs = nothing
set conn = nothing

For each value of "i", you are obtaining a very small recordset to loop through. This approach will be much faster than obtaining all the records and then applying a filter to it.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date