Hand-Coded SQL for UltraDev ASP Pages

UltraDev is very good at creating SQLSelect statements for recordsets, as explained in our earlier articles ASP using Dreamweaver UltraDev and PWS and More ASP using Dreamweaver UltraDev. But if you want to do really flashy things in SQL there comes a point when UltraDev runs out of steam and you have to write the code yourself. Here are some examples of hand-coding, followed by a guide to debugging errors. As before, these examples are taken from a real (and therefore sometimes imperfect!) development project creating ASP pages for a hospital to display aggregate patient data.

Conditionals in VBScript

Adding conditional elements to your SQL statements can be very useful. Later we'll look at using Case and Iif within the SQL, but first here's an example where the conditional element is written in VBScript and extends the SQL statement if certain conditions are met.

We're creating a recordset that will be used to display a list of clinics (PCGs). But in an earlier page the user may have selected a specific health authority (HA) to limit the results displayed. If they did, we want to use that selection as a filter so we only include relevant clinics. If they didn't make a choice, HA is equal to "All" and we want to display all the clinics without filtering them.

Here's a code snippet:

<%
  'Create the HA variable and set it to
    All if it isn't in the query string
  Dim APPT_varHA
  APPT_varHA = "All"
  if(Request.QueryString("HA") <> "") then APPT_varHA
  = Request.QueryString("HA")
'This recordset populates the PCG menu, with names that exist
  in OP_APPT, and
  filtered by HA unless HA is All
  dim sqlwhere
  sqlwhere = "and HA_code = '" + Replace(APPT_varHA, "'",
  "''") + "'"
  dim sqlstmnt
  sqlstmnt = "SELECT PCG, PCG_Name FROM epscons.EPS_OP_APPT,
    swanson.National_PCG
  WHERE PCG = PCG_Code "
  if APPT_varHA <> "All" then
  sqlstmnt = sqlstmnt & sqlwhere
  end if
  sqlstmnt = sqlstmnt & " group by PCG, PCG_Name ORDER
   BY PCG_Name ASC"
  set rsPCG = Server.CreateObject("ADODB.Recordset")
  rsPCG.ActiveConnection = "dsn= swanson "
  rsPCG.Source = sqlstmnt
  rsPCG.CursorType = 0
  rsPCG.CursorLocation = 2
  rsPCG.LockType = 3
  rsPCG.Open
  rsPCG_numRows = 0
  %><%
  Dim Repeat1_numRows
  Repeat1_numRows = -1
  Dim Repeat1_index
  Repeat1_index = 0
  APPT_numRows = APPT_numRows + Repeat1_numRows
  %>

[The colored lines above are one line. They have been split for formatting purposes.]

The last 12 lines are fairly standard stuff. The interesting bit is the creation of the SQL Select statement.

Instead of a line resembling rsPCG.Source = "SELECT PCG, PCG_Name FROM epscons.EPS_OP_APPT group by PCG, PCG_Name ORDER BY PCG_Name ASC" We have a line that reads rsPCG.Source = sqlstmnt This reads the Select statement from the variable "sqlstmnt".

The variable is initially specified as:

dim sqlstmnt
  sqlstmnt = "SELECT PCG, PCG_Name FROM epscons.EPS_OP_APPT,
    swanson.National_PCG
  WHERE PCG = PCG_Code "

[The colored lines above are one line. They have been split for formatting purposes.]

That's the beginning of a regular SQL statement, but it's incomplete. Now comes the conditional element:

if APPT_varHA <> "All" then
  sqlstmnt = sqlstmnt & sqlwhere

And earlier we specified the variable sqlwhere:

dim sqlwhere
  sqlwhere = "and HA_code = '" + Replace(APPT_varHA, "'",
  "''") + "'"

So the conditional part of our Select statement, containing a filter by HA, is only included if APPT_varHA is not equal to "All". In other words if the user has specified a health authority.

Now we finish off the statement:

sqlstmnt = sqlstmnt & " group by PCG, PCG_Name ORDER
 BY PCG_Name ASC"

[The colored lines above are one line. They have been split for formatting purposes.]

This completes the SQL statement irrespective of whether HA equals All or is specified and used as a filter. Note that each section is enclosed in double quotes.

It's a very useful conditional technique and some of our hospital ASP code contains three conditionals for a single recordset. A big advantage is that the conditional element is written in VBScript rather than SQL. The SQL itself is shortened or lengthened, but does not determine whether the condition is met. That removes most problems over SQL versions. As we'll see in a moment, with Case and Iif, SQL versions can be troublesome.

Incidentally, the original SQL Select statement was created by UltraDev - on the basis that the filter was included. Then it was split and assigned to variables through hand-coding.

Case and Iif - Page 2