dcsimg
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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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