.NET Developer Steinman Recruiting Associates US-PA-York Post A Job | Post A Resume

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

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article