IT SOLUTIONS
Your full service technology partner! 
-Collapse +Expand
ASP Classic
Search ASP Classic Group:

Advanced
-Collapse +Expand ASP Classic Store

Prestwood eMagazine

May 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 = P1202A1
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
9362
Since 4/2/2008
Go ahead!   Use Us! Call: 916-726-5675  Or visit our new sales site: 
www.prestwood.com


©1995-2019 Prestwood IT Solutions.   [Security & Privacy]