The SQL Select Statement - Page 2

This is the most crucial element of any recordset. Here's a real example of a recordset containing, as most do, an SQL select statement.

<%
set rswebtotals = Server.CreateObject("ADODB.Recordset")
rswebtotals.ActiveConnection = "dsn=swanson;"
rswebtotals.Source = "SELECT sum(C11) as sumC11, sum(C12)
as sumC12 FROM QM08Summary WHERE DoH = '" +
Replace(rswebtotals__varDOH, "'", "''") + "' "
rswebtotals.CursorType = 0
rswebtotals.CursorLocation = 2
rswebtotals.LockType = 3
rswebtotals.Open
rswebtotals_numRows = 0
%>

[Lines 4 through 6 above are one line. They have been split for formatting purposes.]

The SQL element is the bit after rswebtotals.Source=. What it's doing in this case is summing the database columns C11 and C12 from the database table QM08Summary.

It's also filtering the data by DoH, which as mentioned earlier corresponds to a medical specialty. The filtering part follows the word WHERE. It's quite a complicated expression created by Dreamweaver containing lots of single quote marks and double quotes.

The best way to explain it is to start from a simple version and build up from there. Basically we want to extract data that meets the condition DoH =rswebtotals__varDOH. The variable rswebtotals__varDOH was created at the beginning of the page as we saw earlier. It extracts the DoH value from the URL string.

But we can't simply use DoH =rswebtotals__varDOH because that would be far too easy and, as we all know, coding has to retain its mystique and complexity otherwise everybody would be doing it and we wouldn't be able to charge outrageous fees. The closest we can get is DoH = '" & rswebtotals__varDOH & "'.

The complexity is down to a mysterious area of coding called string concatenation. Without getting into too much detail, the double quotes and ampersands tell SQL that we want to deal with the content of a VB variable, and the single quotes at either end are there to stop the double quotes being perceived as the end of our SQL statement. Our SQL statement started with a double quote (rswebtotals.Source = "SELECT ….) and if the single quotes weren't there, our double quotes would prematurely end that statement.

You may read alternative explanations for single and double quotes that are perfectly valid, but this is a relatively straightforward way of seeing them that usually makes sense.

So how about the even more complicated statement we saw UltraDev produce: DoH = '" + Replace(rswebtotals__varDOH, "'", "''") + "'. This is going one step further. It's anticipating that our variable could finish up with content that includes a single quote mark (apostrophe). Maybe we could imagine a medical speciality called Adolescents' Acne. Of course this doesn't exist and nor is it a code but it does illustrate the issue of single quotes getting into variables. SQL wouldn't appreciate this and would make a mess of the variable because it uses single quotes for its own purposes. That's why UltraDev goes to the trouble of replacing any single quotes within a variable with double quotes, neatly sidestepping the problem.

The only issue is that maybe it's too much trouble to go to if we're absolutely sure our variable will never contain an apostrophe or other use of a single quote mark. If you want to speed up your ASP pages, it can be worth replacing those long expressions with the shorter version we looked at before with simple ampersands and double quotes. In practice you might decide not to bother speeding up short pages, but make the effort on longer pages where there may be a speed problem.

The complete picture

We'll come back to SQL in a later article, with a more complicated example, but at this stage it's worth looking at a complete ASP page to see how all these elements fit together, and how the data returned can be neatly formatted in an HTML table. Our example is a start page that leads to a detail page — the detail page would use the DoH filtering code we just considered.

Note that the page is not optimised for speed. It's part of a batch of work that required a large number of ASP pages to be constructed, all of which will be accessed infrequently. It's the kind of job for which Dreamweaver UltraDev is ideal, because it allows lots of pages to be built quickly — so at low cost — and if the code runs a bit slower than optimal hand-coded VB, that's no big deal because the server loading is low.

Peripheral HTML like navigation and titles has been stripped out because it's all standard stuff, but the original table names and field names have been kept, even if they're a bit clumsy. This is definitely a real world example! Note that the page is for an Intranet using Internet Explorer only. It looks OK in Netscape but there are some stylesheet and width issues that would need to be handled differently if it was aiming for cross-browser compatibility.

Explanations for the code have been added in as comments — either VB comments beginning with an apostrophe or HTML comments beginning <!--. The amount of comment is exaggerated since this is a tutorial. VBScript is sensitive to line breaks and in some cases lines have been broken so they fit on the page. This is especially true of the SQL Select statements, which should go on a single line.

Here's how the stripped down page looks in a browser:

More ASP using Dreamweaver UltraDev
Coding the First page - Page 3