To read the article online, visit http://www.4GuysFromRolla.com/webtech/071799-1.shtml

Using Custom Recordsets

By Ryan S.


I'm back again for another fun filled tutorial going into the depths of the unknown to bring back a little bit of new knowledge to help make everyone's life easier. You should realize now that I always like to have little speil at the beginning of the article, and this one's no exception. So, while I could talk about some extremely technical topic, I'll get everyone OFF-topic and tell them to go to www.torley.com .... It's got music for just about every taste. Anyway, onto the article.

As some of you may or may not know, often my articles deal with SQL. Now, just so you realize, I don't know jack about SQL ;-) Anything I have learned I have found out by searchin' the ASP pages and just checking out the docs. If I can't find what I need, I make it. Anyway, what I'm going to discuss today is utilizing custom recordsets. Yes, that's right, I said CUSTOM.

You probably know what a recordset is and how to either explicity or implicity create it, and of course you are probably aware of the various cursor and lock types. However, have you ever wanted to create a recordset without creating a table on the server, or just keep from hitting up the ODBC drivers a whole bunch each time you want to update a table? Well, besides using disconnected recordsets, you can use custom recordsets.

Custom recordsets are, as the name implies, custom. That means made by you. Defined by you. Created by you. Controlled by you. Enough of those "by yous"... Any more and we would have a few megalomaniacs on our hands ;-) Anyway, you probably already have an idea of how you would use them, but since I try to keep my tutorials on a "... For Dummies" level, I'll assume that you have no clue in the heck why you would want to use a custom recordset. The example that I will use is a very simple one: A user database. Now, while some of you may hate me, I personally happen to like using Application wide variables. However, they have to be treated with special care. You have to Lock/Unlock your Application variables each time you use them, so there's not a sharing violation. (We don't want two people trying to tinker around with the same Application variable at the same time!)

Now, in a custom recordset, first you have to define your variable as a recordset. This is accomplished by:

<%
       Dim customRS
       set customRS = Server.CreateObject("ADODB.Recordset")
%>

Next, you must add fields to it. Now, in pristine conditions, you are using your custom recordset in a VB environment, meaning you can use all of the built-in field types. However, because the world is not pristine, you may have to declare some field types. Also note that you only want to call your "creating" code once, or you can blow off your code. Another reason to use a VB object is the Private Function vs Public Function =D Anyway, you add a field by using the following syntax:

customRecordSet.Fields.Append "name", adFieldType, FieldLength

adFieldType is something like adVarChar, adInteger, etc etc. These contants are listed in ADOVBS.inc. (If you are unfamiliar with what ADOVBS.inc is, read this article.) The FieldLength is the maximum amount of characters the field will hold. I often do 5 for IDs, and 20 for text, however, in your situations, you might need more or less. No biggy. Now, open your recordset by doing:

customRecordSet.Open

And you are ready to access all the fields. Just do:

customRecordSet.Fields("FieldName").Value = blah

or

customRecordSet("FieldName") = blah

or

customRecordSet(x) = blah

(where x is the index of the field)

and you are ready to rumble. Now for the sample app.

Let's say we want to keep track of how many users are online, such as Aspin.com does, but we also want to be able to have an admin page to see all the users. We will be tracking the users IP, the users ID, and the time they first logged on. Ok, here goes the code:

First, we need to have our global.asa file updated, to create our custom recordset as well as keep track of users logging on/off.

'-- Global.ASA file --

<SCRIPT LANGUAGE=VBSCRIPT RUNAT=SERVER>
'This is the code for adVarChar.  A complete listing is available
'in ADOVBS.inc.
Const adVarChar = 200
Const adInteger = 3

Sub Application_OnStart
	'This event will fire once when the server starts up for the first
	'time.  So, we will want to create our custom recordset here...

	'We will need a unique ID for every user.  To do that we will
	'create an application variable that will serve as the ID.  Each
	'new user will increment the counter by one (via Session_OnStart)
	'Initialize the ID to 0...
        Application("CurrentID") = 0

	'Our ID field
        customRS.Fields.Append "ID", adInteger, 5

	'Keep track of the IP (15 chars (xxx.yyy.zzz.aaa) long)
        customRS.Fields.Append "IP", adVarChar, 15

	'HH:MM tracking.. u could do more
        customRS.Fields.Append "LogOn", adVarChar, 5    


        customRS.Open	'Open the recordset
End Sub


Sub Session_OnStart
	'This will fire when every new user connects to our web.
	'We will need to lock the Application scope *before* we
	'start using Application variables:

        Application.Lock

        If not customRS.EOF Then
                customRS.MoveLast
                AvailID = CInt(customRS("ID")) + 1
        Else
                AvailID = 1
        End If

	'Create a new row in our recordset
        customRS.AddNew
        customRS("ID") = CLng(Application("CurrentID"))
        Session("ID") = CLng(customRS("ID").Value)
        customRS("IP") = Request.ServerVariables("REMOTE_ADDR")
        customRS("LogOn") = Hour(now) & ":" & Minute(Now)
        customRS.Update

	'Unlock the application scope
        Application.Unlock


	'Set the Session Timeout
	'This is how long it will take (in minutes) before a session
	'times out.  You might want to make this small to save memory
	Session.TimeOut = 5	'Expire in 5 minutes
End Sub


Sub Session_OnEnd
	'This event will fire when the user's session times out
	'It locks the Application scope, then deletes the
	'appropriate entry

        Application.Lock

        customRS.Find("ID = '" & Session("ID") & "'")
        customRS.Delete

        Application.UnLock
End Sub
</SCRIPT>

'This creates an application level instance of our custom recordset
<OBJECT RUNAT=Server SCOPE=Application ID=CustomRS PROGID="ADODB.Recordset">
</OBJECT>

'-- End Global.ASA File

Now you have an application-wide variable, a recordset, called CustomRS that you can access. I used the <object> code because it prevents having to

set customRS = Application("CustomRS")

each time we want to use it in a page. Now, you should have working code. You can try to access the CustomRS object and have fun with it.

Now, how in the hell do you use these custom recordsets in your ASP pages? Well, let's look at that in Part 2!

  • Read Using Custom Recordsets, Part 2


  • Article Information
    Article Title: Using Custom Recordsets
    Article Author: Ryan S.
    Published Date: Saturday, July 17, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/071799-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers