Using Access or SQL Server to Write yout ASP Code

Rick Curtis - Tutorial 9

Using Access or SQL Server to Write your ASP Code

Sometimes I'm just plain lazy. Other times I don't want to have to spend hours debugging ASP code because of typos so I've found a way to help short cut some repetitive ASP coding tasks. The idea is an old one that I used back in Access 2.0 days to write Web pages in Access. The idea is to mix strings of text with database field values to create the lines of code you need. So here we go.

I was working on a site that contains resumes. The headache for the database design for this is that there are about 70 different skills to track for people. Since the resumes aren't stored permanently but removed after 6 months creating a multi-table relational database to store this would have been a nightmare. So I went with one basic table storing the person's resume in one place. This means a lot of fields in the database.

I also needed a Lookup table to store all the Skill values to be able to use for dynamically created list boxes in UltraDev. So I first created the Lookup table called Skills.

Field Name	SQL 7 Data Type	Access 2000/97 Data Type	Notes
SkillID (Primary Key)	[varchar](50)	Text(50)	Usually I set the Primary key to Integer and have it be an Autonumber. In this case I used a text field with unique values. You'll see why as we go
Category	[varchar](50)	Text(50)	The Skill Category
Lookup	[varchar](50)	Text(50)	The Skill Description
SortOrder	[int]	Integer	What Order to Sort the Skills in (sometimes I don't want to use alpha order so this way I can create my own ordering scheme)
Active	[bit]	Yes/No	I often use a Yes/No field which lets me turn on or off these values. All my queries or stored procedures are set up to run "Select...Where Active = 1"

Here's some sample data.

SkillID	Category	Lookup	SortOrder	Active
M1	Medical	CPR	1	1
M2	Medical	Wilderness First Aid (WFA)	2	1
M3	Medical	Wilderness Advanced First Aid (WAFA)	3	1
M4	Medical	Wilderness First Responder (WFR)	4	1
M5	Medical	First Responder	5	1
M6	Medical	EMT	6	1
M7	Medical	Wilderness EMT	7	1
M8	Medical	Paramedic	8	1

There are 4 other Categories within the skills table for a total of 70 different skills. I created a total of 5 recordsets each one showing only the records from that Category like the Medical Category above. I used these 5 recordsets in an Insert page to drive dynamic check boxes so that people could check yes if they have that skill.

Now let's look at the Resume Table. It obviously has name, address, and all the skill fields. As you can see I gave the field names for the skills the same value as the Primary Key Name in the Skills table so that I could easily make the connections. [It also means that if I need to add more skills I add them to the Skills table with the descriptive name and can easily add field M9 to the Resume table making it much easier to read.]

Field Name	SQL 7 Data Type	Access 2000/97 Data Type	Notes
ResumeID (Primary Key)	[int]	Integer	Autonumber
M1	[bit]	Yes/No	CPR - Yes/No
M2	[bit]	Yes/No	Wilderness First Aid (WFA) - Yes/No
M3	[bit]	Yes/No	Wilderness Advanced First Aid (WAFA) - Yes/No
M4	[bit]	Yes/No	Wilderness First Responder (WFR) - Yes/No
M5	[bit]	Yes/No	First Responder - Yes/No
M6	[bit]	Yes/No	EMT - Yes/No
M7	[bit]	Yes/No	Wilderness EMT - Yes/No
M8	[bit]	Yes/No	Paramedic - Yes/No

On the Insert form I give each Checkbox a name (M1, M2, M3...) so that when I apply the UD Insert Server Behavior it maps the the correct Checkbox to the proper field in the Resume table. Here's a more concise way to say it.

The Skills Table Primary Key Value corresponds to the Resume Table Field Name for that particular Skill.

Example: The Skills table row with the SkillID of M1 is the CPR row. In the Resume Table if the value of the Field called M1 is yes then it means the person has CPR.

Creating a View/Query/Stored Procedure

You can accomplish this using either Access 97/2000 or SQL Server. In my case I am using SQL Server so the syntax for things like string concatenation are a little different (SQL uses the '+' sign while Access uses the '&' sign). Here is a View I created in SQL 7 using the query grid that is similar to Access. Press the SQL button on the toolbar to see the SQL code generated. The code in blue is the basic SQL code which would map out to SELECT SkillID, Lookup FROM Skills. Everything in red is simply a string set off in single quotes. You will see that the single quotes are in blue because they are part of the SQL syntax, not the string. In the places where I need actual field values I have to close the string with a single quote then use the + sign to concatenate the string to the field value then another + and a new single quote to connect to the next string. You need to pay special attention to where you need spaces and single or double quotation marks as you concatenate the entire string. It takes little trial and error, but once it is set up it works like a charm.

What I am going to do is run a query off the Skills table which will bring up two field values, the Skill ID and the Lookup description. To that query I and going to add text strings that contain the ASP code I need. Once you start adding strings, the grid is no longer showing just a single field so it adds the AS Expr1 clause.

SELECT '<% IF (rsSearch.Fields.Item("' + SkillID + '").Value) = "True" THEN Response.Write "<li> ' + Lookup + ' </li>" END IF %>' AS Expr1
FROM Skills

When you run the query or view here is what you see in the results grid. Voila, perfect ASP code almost ready to cut and paste into your page. I say almost ready because what you see in the query grid may not be what you get when you actually copy it (SQL Server only). In Access what you see is what you get. In SQL Server copying the query rows adds extra double quotes so what you get when you copy it is " at the ends and "" inside as shown below. The extraneous double quotes are shown in red.

"<% IF (rsSearch.Fields.Item(""M1"").Value) = ""True"" THEN Response.Write ""<li> CPR </li>"" END IF %>"

A quick search and replace will eliminate the doubles leaving you with perfect code to paste into your page.

<% IF (rsSearch.Fields.Item("M1").Value) = "True" THEN Response.Write "<li> CPR </li>" END IF %>
<% IF (rsSearch.Fields.Item("M2").Value) = "True" THEN Response.Write "<li> Wilderness First Aid (WFA) </li>" END IF %>
<% IF (rsSearch.Fields.Item("M3").Value) = "True" THEN Response.Write "<li> Wilderness Advanced First Aid (WAFA) </li>" END IF %>
<% IF (rsSearch.Fields.Item("M4").Value) = "True" THEN Response.Write "<li> Wilderness First Responder (WFR) </li>" END IF %>
<% IF (rsSearch.Fields.Item("M5").Value) = "True" THEN Response.Write "<li> First Responder </li>" END IF %>
<% IF (rsSearch.Fields.Item("M6").Value) = "True" THEN Response.Write "<li> EMT </li>" END IF %>
<% IF (rsSearch.Fields.Item("M7").Value) = "True" THEN Response.Write "<li> Wilderness EMT </li>" END IF %>
<% IF (rsSearch.Fields.Item("M8").Value) = "True" THEN Response.Write "<li> Paramedic </li>" END IF %>

Let me explain what is happening with this code. Since there is no actual relationship between the two tables don't have a way of connecting the Skills Table and the Resumes table directly. The rsSearch recordset is the UD recordset that calls up the Resume table based on user search criteria. By having the SkillID Primary Key value be M1 and that is the same name for the corresponding field in the Resume Table I can essentially create the ASP lookup statement just from the Skills query in red and blue above.

The actual ASP code is simply evaluating if the Resume table field M1 value is True, if so it writes out the Description in an unordered list. I took me a little time to work this out, but thinking about typing/pasting & editing all 70 ASP statements and then figuring out where I typed it incorrectly, it was worth it. Also, if I add new skills to the Skills table and corresponding fields to the Resume table all I have to do is run the query again and cut and paste in the new values. You obviously won't write an whole page this way, but anytime you have a lot of repetitive code that would be interspersed with database values, this technique can save a lot of time.

Another quick example uses the following stored procedure. This is another lookup but in this case I wanted to have the database generate the ASP code in the correctly sorted order. Well SQL Views don't support the ORDER BY clause (unless you go through a bunch of extra steps that I won't go into). So I wrote the following stored procedure.

Alter Procedure spASPPopulation

As
set nocount on
SELECT '<% IF (rsSearch.Fields.Item("' + PopID + '").Value) = "True" THEN Response.Write "<li> '+ PopulationType + ' </li>" END IF %>' AS Expr1
FROM Population
ORDER BY PopulationType
return

You can use this same approach anywhere you need to create repetitive ASP code that interfaces with your database. For example, imagine that you needed to define 70 variables on a page. Just set up a string that merges your Dim statement with your database field names/values and just cut and paste. Hope you find this tip useful.