Building an Internet Database Drilldown Application, Part 2

Building an Internet Database Drilldown Application, Part 2

Read Building an Internet Database Drilldown Application, Part 1

Read Building an Internet Database Drilldown Application, Part 3

Roger Drye Directed Minds

Homepage: www.directedminds.com Email: info@directedminds.com

Webmaster's Note:
In this article, Roger creates a Session-level Connection object. This is not the best thing to do. Placing large objects in Session level variables can be disadvantageous. Please refer to the following articles for more information on Session level variables:

Pros & Cons of Session Variables

Are Session Variables Really Evil?

Keeping large objects out of Session Scope

Making the database connection ... well not quite yet.

Can I do this the easy way?

I looked at several editor options when creating the ASP pages for the drilldown application. I've tried FrontPage (various versions) several times. But I keep putting it away. A few things about it drive me crazy. First of all, I don't want my editor to reformat my pages for me. I write code that I think is well laid out and understandable. Just about every time I get it looking just right, I hit the save button and FrontPage turns something like this:

into this jumbled mess:

<input type="hidden" name="fname" value="<%=fname%>"><input type="hidden"

name="lname" value="<%=lname%>"><input type="hidden" name="address"

value="<%=address%>"><input type="hidden" name="city" value="<%=city%>"><input

type="hidden" name="state" value="<%=state%>"><input type="hidden" name="zip"

value="<%=zip%>"><input type="hidden" name="phone" value="<%=phone%>">

I'm sure there is an option I can select that will stop this, but I usually slam the FrontPage editor shut before I ever get a chance to check it out.

Then, when you are debugging with FrontPage, and your browser (such as Netscape or IE) says you have an error on line 483 of your ASP file, how do you find that number in FrontPage? Start at line 1 and count down 482 times.

Visual InterDev is pretty good if you don't mind doing things Microsoft's way, which is of course to make the easy thing easy, and the hard things impossible. I don't think I am a purist, but when I write code, I want my code on the server, not the 47,000 lines of code InterDev adds to your HTML to create a simple online form.

I have even tried using the Microsoft Access wizard to export to ASP. Right. If you get that thing to create anything like a usable data entry form that isn't 20 times the size of a handwritten form, please let me know what magic you used. And do I really want my users to rely on an HTML layout form object? I thought it was my job to layout HTML.

So, the solution. Notepad. Works great. Lightweight. Also, I have started using ASP Express, a shareware product, and it does a really good job as well. And you get line numbers!

Global.asa...Session State...Application Startup...WHAT?

First thing: the global.asa is an optional file. So you can do without it, but it does offer some advantages to the ASP developer. It is a place to store event scripts and declare objects that have session or application scope. The contents of the global.asa are not viewable by users and must be stored in the root directory of the application.

Here's what a global.asa with empty event scripts would look like:

SUB Application_OnStart
'The Application_OnStart event is executed the first time that an ASP page within an application is requested.
END SUB

SUB Application_OnEnd
'This event is called when the application ends. This could be cause by the application being inactive for a period of time or the web site being stopped.
END SUB

SUB Session_OnStart
'This event is executed for each user when they first request a page within an application.
END SUB

SUB Session_OnEnd
'This event is called when a user's session ends. This could be cause by an explicit end (ie: session.abandon) or the session timeout being exceeded.
END SUB
</script>

And here is how the global.asa might be used for an application that uses a DSN-less connection to an Access database.

<SCRIPT LANGUAGE="VBScript" RUNAT="Server">

Sub Application_OnStart()
	Application("DB_PATH") = "D:\mydomain\database"
	Application("DB_CONNECTION_STRING")="DBQ="& amp;Application("DB_PATH") & "\mydatabase.mdb;DefaultDir="&Application("DB_PATH") &"; Driver={Microsoft Access Driver(*.mdb)}; DriverId=25;FIL=MS Access; ImplicitCommitSync=Yes; MaxBufferSize=512; MaxScanRows=8; PageTimeout=5;Threads=3; UserCommitSync=Yes;"
	Application("DB_CONNECTION_LOGIN") = "test"
	Application("DB_CONNECTION_PASSWORD") = "1234"
End Sub

Sub Application_OnEnd()
	' Do not do anything
End Sub

Sub Session_onStart()
	' Connect to the database Set Session("DB_CONNECTION") = Server.CreateObject("ADODB.Connection")
	Session("DB_CONNECTION").Open Application("DB_CONNECTION_STRING"), Application("DB_CONNECTION_LOGIN"), Application("DB_CONNECTION_PASSWORD")
End Sub

Sub Session_onEnd()
	' A graceful disconnect Session("DB_CONNECTION").Close
	Set Session("DB_CONNECTION") = Nothing
End Sub

</SCRIPT>

As you can see, every new session looks to the application for information. You can say that the application object serves all users, the session object serves only a single user.

And so, since we have used the global.asa to create a database connection with each session, now each page of our ASP need only concentrate on creating recordsets and manipulating data. And we are guaranteed (or at least better our chances of) a graceful exit when the session ends.

BTW, give it all up if your user won't accept cookies or can't because of their browser type. It's just too hard! Session state is maintained by cookie objects, even though they may never be written to the client machine. If you have users that don't accept cookies, you will need to test for this on your startup page and redirect them another page that tells them how to accept them. Or you can scrap the whole session idea, and create a new connection on each ASP page. But since no login credentials have been saved (no cookies), they will have to login on each and every page. Doesn't sound like fun.

Next ... Data please!

Read Building an Internet Database Drilldown Application, Part 1

Read Building an Internet Database Drilldown Application, Part 3

Directed Minds is a web development company specializing in hosting and developing ASP, database and ecommerce solutions for large and small organizations.

Roger Drye is the owner of Directed Minds. He has been developing Internet solutions for small and large companies and government entities for many years.