I.T. Discussion Community!
-Collapse +Expand
ASP Classic
Search ASP Classic Group:

Advanced
-Collapse +Expand ASP Classic Store

Prestwood eMagazine

April Edition
Subscribe now! It's Free!
Enter your email:

   ► KBWebsite Scri...ASP ClassicLanguage Basics   Print This     
  From the May 2009 Issue of Prestwood eMag
 
ASP Classic Language Basics:
Buffer a RecordSet in an application variable
 
Posted 13 years ago on 11/14/2006 and updated 2/26/2007
Take Away: In ASP, you can buffer a RecordSet in either a session or application variable. This code snippet shows you how to create a reusable method for buffering RecordSets in an application variable.

KB100410



The following code snippet uses Set Application("MyAppVar") = ARecordSet to assign a record set to an application variable. It also uses a second variable to store the current time and uses that variable to determine when to redo the select.

Sub DB_SelectReadBufferedApp(ASeconds, ByRef ARecordSet, AStrSQL)
 Dim AppVar
 Dim AppVarDateTime
 
 AppVar      = "DB_SelectReadBuffered5" & AStrSQL
 AppVarDateTime = "DateTime" & AppVar
 If Len(Application(AppVarDateTime) & "") = 0 Then





  ''' DB_SelectRead is a typical read forward ASP execute select method. '''





  DB_SelectRead ARecordSet, AStrSQL
  Application.Lock
  Set Application(AppVar)   = ARecordSet
  Application(AppVarDateTime) = Now
  Application.Unlock
 ElseIf DateDiff("s", CDate(Application(AppVarDateTime)), Now) > ASeconds Then
  DB_SelectRead ARecordSet, AStrSQL
  Application.Lock
  Set Application(AppVar)   = ARecordSet
  Application(AppVarDateTime) = Now
  Application.Unlock
 End If 
 Set ARecordSet = Application(AppVar)
 
 If ARecordSet.RecordCount > 1 Then
  ARecordSet.MoveFirst
 End If
End Sub

Here is a usage example:

Dim obj
Dim strSQL
strSQL = "select * from Core_Members where ProfileIsPublic = 'Y' and Picture<>'' "
DB_SelectReadBufferedApp 3600, objMembersRS, strSQL
objMembersRS.Sort = "LastName, FirstName"
while Not objMembersRS.EOF
Response.Write objMembersRS.Fields("LastName") & ", " & objMembersRS.Fields("FirstName")
objMembersRS.MoveNext
WEnd

What about storing a RecordSet in a session variable?

You don't want to use this trick with session variables because that will break the thread model and actually make your website less scalable.

Can I over use this techinique?

Yes, very very easily. You should use this for ONLY the most frequently used data.

Here are some more resources on this subject:

Enjoy! Post your findings below.


Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1196A1
Enter key:
KB Post Contributed By Mike Prestwood:

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Visit Profile

 KB Article #100410 Counter
9306
Since 4/2/2008
-
   Contact Us!
 
PrestwoodBoards.com was developed and is maintainted by me. Do you have a question or suggestion? Do you see a problem? Contact me now. My goal is to build an ad-free and spam-free source of I.T. information with many contributers (ok to promote your website/company in your bio). Yes, my company Prestwood IT Solutions is mentioned in my bio which shows with every post, but you can contribute and promote your pet project too!

3,005 People Online Now!!  
Sign In to see who's online now!  Not a member? Join now. It's free!
Show more stats...


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech