Using ASP and SQL together can be confusing. There are so many different SQL products available that you might be tempted to throw up your hands and forget about it. MySQL, SQL Server, mSQL—they're all excellent tools, but you don't need them to create practical SQL statements for use with ASP. You can use Access and your Access skills, along with our tips, to successfully incorporate SQL into your ASP Web pages.
This collection of tips will help you learn the ins and outs of integrating SQL calls into your ASP code. We'll show you how to mine your databases for the right data and how to present it to your users in the most efficient way. We'll also show you how to use SQL to make your Web pages more responsive to your users and ease the burden on you. Instead of doing things by handwriting and rewriting code and statements as needed, we'll give you useful instructions to make your job easier and make your Web pages easier to write.
1. The SELECT statement
The basis of much of the work you do with SQL is the SELECT statement. If you use SQL directly in your database tool, you'd just type in:
SELECT what
FROM whichTable
WHERE criteria
When you run the statement, a query is created to store the results.
With ASP, you'll use this same general syntax, but you'll store the contents of the SELECT statement in a variable:
SQL = "SELECT what FROM whichTable WHERE criteria"
Once you have the basic pattern down, you can mold the statement to fit your needs using traditional SQL query patterns and criteria.
For example, if you have a table named Products and you want to pull all the records, you write:
SQL ="SELECT * FROM Products"
That pulls everything—all the records that make up the table. But say you want to pull only a specific column, p_name, from the table. Instead of the * wildcard, you can use the column name:
SQL ="SELECT p_name FROM Products"
The contents of the p_name column in the Products table will be pulled when the query is executed.
2. Narrowing with WHERE
Sometimes, pulling all the records will suit your needs, but more often than not, you don't want everything but the proverbial kitchen sink in your recordset. So, why pull it? It just takes extra time, and you wind up with an unnecessarily bloated recordset.
If you wanted to pull only p_name records that started with the letter w, you would want to use the WHERE clause:
SQL ="SELECT p_name FROM Products WHERE p_name LIKE 'W%'"
You don't have to split hairs to see that the SELECT statement structure we went over at the beginning is in place here. WHERE is followed by the criteria that will help filter the data, yielding only data that matches your specifications. In this case, you want only p_name records that begin with w.
The percent symbol (%) specifies that the query return all entries that begin with w and are followed by any data or even no data. So, when executed, west and willow would be pulled from the Products table and stored.
As you can see, by carefully crafting your SELECT statement, you limit the amount of information returned in the recordset, honing it to just what you need to work with.
Becoming facile with ways to architect your query is part of getting really comfortable working with SQL. To help you get started working with more complex SELECT statements, let's look at some key criteria terms—comparison predicates—that you may frequently want to use when building your SELECT string to pull a specific slice of your data pie.
WHERE basics
Some of the easiest ways to begin creating WHERE clauses involve using standard equation patterns: <, <=, >, >=, <>, and =. Based on what you know about testing data in ASP, you can quickly see how the following statements work:
SELECT * FROM Products WHERE p_price >= 199.95
SELECT * FROM Products WHERE p_price <> 19.95
SELECT * FROM Products WHERE p_version = '4'
Note: You can see here that the final example puts 4 in between apostrophe symbols. That's because '4' in this case is of the type text and not a number. Since you'll be putting your SELECT statement in apostrophes to assign it as the value of a variable, you use apostrophes within the statement.
Comparison predicates
Comparison predicates enable you to specify what you are looking to pull from the table. You use them to create the filters that help narrow the recordset so that it holds only the information you care about at a given moment or for a given task.
3. LIKE and NOT LIKE and BETWEEN
You saw LIKE in use in the sample statement where you pulled names beginning with w. The LIKE predicate is a powerful one. At times, however, it may give you much more data than you want, so make sure you spend time thinking through what you are hoping to retrieve. If you really want to only pull five-digit SKU numbers that begin with 1 and end with 5, you would use the underscore (_) character rather than %:
SQL = "SELECT * FROM Products WHERE p_sku LIKE '1___5'"
The underscore stands for any character, but only a single character. So, by entering “1 _ _ _ 5”, you limit the search to five-digit entries that fit a specific pattern.
If you want to flip this operation on its head a bit, you might try to find all SKU entries that do not follow the “1_ _ _ 5” pattern. You can easily turn the criteria into an exclusionary filter by adding NOT before LIKE.
BETWEEN
If you need to pull a range of data and know the starting and ending points, you can use the BETWEEN predicate. Let's assume that you want to pull the records between 1 and 10 in a given table. You can set that up using BETWEEN:
…WHERE ID BETWEEN 1 AND 10
Or you can use familiar mathematical equations:
…WHERE ID >= 1 AND ID >= 10
4. Combination statements
The SQL statements we've dealt with so far are decidedly simple, even though they are a powerful improvement over pulling the same information by looping through a standard recordset. Don't let the samples short-circuit your creative grip of SQL, however. You can stack a SQL statement mighty high by adding additional predicates, joined by AND, OR, and NOT.
Take the following SQL statement as an example:
SQL ="SELECT c_firstname, c_lastname, c_email FROM customers WHERE c_email IS
NOT NULL AND c_purchase = '1' OR c_purchase = '2' AND c_lastname LIKE
'A%'"
With what you've learned so far, this isn't tough to decipher, but it does give you a good look at how the levels of criteria get glued together in the single SQL statement.
Multilined statements
Since SQL statements can get a bit unwieldy, you can break the pieces of your statement out on separate lines and just concatenate the value by adding the existing variable contents to the new pieces of the query and storing it in the same variable name:
SQL = "SELECT c_firstname, c_lastname, c_emailaddress, c_phone"
SQL = SQL & " FROM customers"
SQL = SQL & " WHERE c_firstname LIKE 'A%' and c_emailaddress NOT NULL"
SQL = SQL & " ORDER BY c_lastname, c_firstname"
At the end of all that, SQL contains the full SELECT statement:
"SELECT c_firstname, c_lastname, c_emailaddress, c_phone FROM customers
WHERE c_firstname LIKE 'A%' and c_emailaddress NO NULL ORDER BY c_lastname,
c_firstname"
It's a bit easier to read when it's broken out! When it's time to debug, you might be glad you spent the few extra keystrokes that the more readable version takes. Just remember that you may need to add spaces before the closing quotations or after the opening quotations to make sure you don't shove several words together as the string is concatenated.
5. 1,2,3, execute!
After you've crafted your SELECT statement, you have to somehow run it. In your database tool, you'd hit some form of Go button. On your ASP pages, you'll tap the SQL statement by either executing it on the spot or calling up a stored query.
Once you've set up the SQL statement, you have to somehow access the results of the query. Not surprisingly, the key is the ASP recordset. When you use a non-SQL recordset, you create the recordset using code that resembles the following:
Dim objRec
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "customers", objConn, 0, 1, 2
If you've gone through Introduction to ASP, you're familiar with this format, and you know that "customers" represents the name of the table in the database you've opened.
Opening the recordset
To take advantage of your leaner SQL-selected data, you'll need to tweak the recordset you were using with your regular ASP pages:
Dim objRec
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open SQL, objConn, 0, 1, 2
The only change here is that instead of listing the "table" after objRec.Open, you list the variable that holds your SQL statement.
One of the immediate advantages of this method is that you can specify the cursor type (as shown by the 0, 1 ,2 above).
Executing the SQL
As an alternative, you can, in a conveniently tight line, execute the SQL statement, thereby creating the recordset. Here's the syntax:
Dim objRec
set objRec = objConn.Execute(SQL)
In this example, you see that SQL is the variable in which you've stored your SQL SELECT statement. This line "runs" the statement (or performs the query on the database), pulls the data, and stores it in a recordset with, in this case, the variable name objRec. The main drawback to this method is that you can't select the cursor type you need to use. Instead, the recordset is always opened with a forward-only cursor.
Because of the cursor issue, you'll want to be familiar with both methods of creating the recordset. The Execute approach saves a bit of typing, but then you are stuck with the default cursor, and there will be many times when that won't work. Whichever method you use, the big difference between this recordset and the one you may have been using yesterday is refinement. Regardless of what fields you pulled, what your criteria was, or how you sorted the data, chances are good that your SQL-derived recordset will be less bulky than a standard recordset opened in ASP, not to mention far easier to manipulate. After all, by filtering the data to pull just what you need, you eliminate the time-consuming if-then tests and loops you may have come to rely upon.
Writing your SQL for testing
A tip you'll find that many expert ASP coders advocate is "writing" your SQL statement when you're testing your pages. This can help you debug your code because it allows you to see the string that is being passed to the server to execute. All you need to do is add the Response.Write yourVariable name to write the information to the screen. Each time you post a SQL-related question to an active ASP list, make sure you include this information.
6. Stored queries
Creating SQL statements from scratch each time isn't too taxing when your query is relatively straightforward, but complicated queries can quickly develop bugs. So, once you get them running smoothly, you may find it saner to store them and call them up when needed. Thus, you can use a stored query even for simple queries.
Let's say that once a week, you run a report for your team that shows the current number of open support issues, pulled from your database, stamped by the date opened, and sorted into the categories of support issues your team uses. Once you've set up this query, there's no reason to do it again. Instead of creating the query on your HTML page, build the query in your database tool and save it.
You can then tap into it from your ASP page using the ActiveCommand property. This might feel a little awkward the first time or two, but there are really only a few lines involved:
Set objSQ = Server.CreateObject ("ADODB.Command")
objSQ.ActiveConnection = "databaseName"
objSQ.CommandText = "storedQueryName"
objSQ.CommandType = adCmdStoredProc
set objRec = objSQ.Execute
Note that using adCmdStoredProc indicates that you have included the adovbs.inc file on your page. This file defines Access constants you can access by name rather than number. Simply "include" it on your page (<!--#INCLUDE -->), and then you are able to use names such as adCmdStoredProc instead of a number. It helps make the code easier to understand when you look at it later.
7. ORDER BY
One of the most frustrating things about pulling records from an Access database is that they come out in the order you entered them in. Even if you use Sort By in Access to change the view of the records when you're working within the Access environment, the order of the records in the table is not altered.
If you're just using an ASP recordset to spit your records back to the page, you know how painful it can be to have everything appear out of order. But you may have shrugged away the problem since no easy solution exists. ORDER BY makes short work of the problem, and once you see it in action, you'll be ready to commit to SQL calls for life.
To order your results, just tack ORDER BY to the end of the SELECT statement and specify what column you'll be ordering by. So, if you want to order the Customers table by the customer's last name, then you might have the following:
SQL = "SELECT c_lastname, c_firstname, c_email FROM Customers ORDER BY c_lastname"
That's all there is to it! Once you establish the recordset and start writing the results to the screen, you'll see that they are neatly ordered alphabetically.
Ordered to the X degree
You aren't limited to first-level ordering with your SQL statement. In fact, more times than not, you'll want to specify the pattern for ordering the data two to three levels deep. Let's assume you have a database with the following records:
 |
The single-level ORDER BY you used earlier would spit out the records in this order:
Absurdly Assured
absurd@assured.com
Absolutely Assured
absolutely@assured.com
Crazed Coder
crazy@coder.net
Loosely Fringe
loose@fringe.to
Lunatic Fringe
lune@fringe.to
Hands On
hands@yes.org
You can see that the ORDER BY worked. In the actual table structure, Absurdly Assured is the final entry, but it came out here on top. And Hands On came out last since the O is the deepest letter in the alphabet used in this list. The orderly and precise among you are probably chomping at the bit, since Absolutely should really come before Absurdly in a nice, neat, alphabetical list. To ensure that happens, you just need to add second-level ORDER BY criteria by incorporating the name of the next column you want to use next in your sort, preceded by a comma:
SQL = "SELECT c_lastname, c_firstname, c_email FROM Customers ORDER BY
c_lastname, c_firstname"
The results will now sort first on the c_lastname column and then on the c_firstname column. If your table has a large number of records, fine-tuning your sorting in this way can really streamline your results.
Putting it to use
If you're like most coders, rolling up your sleeves, flexing your fingers, and starting to peck away at well-worn keys is the best way to make a new technique sink in. Switching from doing things the long way with just ASP to speeding things up with SQL is no exception. In the next section, we'll look at some common problems you might be solving with ASP and how integrating SQL statements can make them more efficient.
8. Randomly speaking
Setting up a randomly generated HTML chunk is an easy-to-implement ASP feature with a big punch. Whether you've created a tip of the day, a featured blurb rotation, or even a manual banner rotation system (instead of using the built-in Ad Rotator Component), rotations are fun. With minimal effort, you can make your site look constantly fresh.
For data stored in a database, random features give the desired effect, but they can be slow. You can't just tell ASP to "look for RNumber" and print it. Instead, a common solution is to set up a loop that resembles the following:
Randomize
RNumber = Int(Rnd*499) +1
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... script here ...
end if
objRec.MoveNext
Wend
This is pretty easy to understand. First, you pull a random number between 1 and 500 (assume that 500 is the total number of records in your database). Then, you go through each record testing the value of the ID to see if it matches RNumber. When it does, you complete the THEN chunk of code. If your RNumber is 495, it's going to take a long time to cycle through the database. Although 500 seems like a lot, it's a small database compared to a more robust enterprise solution that may have thousands of records in a single table.
Using SQL, you can quickly find the exact record and open a recordset with just that record, as shown here:
Randomize
RNumber = Int(Rnd*499) + 1
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
set objRec = ObjConn.Execute(SQL)
Response.Write RNumber & " = " & objRec("ID") & " " & objRec("c_email")
You wouldn't have to write out the RNumber and ID; you can just verify that things are matching up. Once you're satisfied that the call works, you can manipulate the "random" record in any way you need to. The recordset doesn't hold anything else, so you've quickly honed in on the record you need, significantly trimming the processing time.
Random plus
If you're maximizing the power of the Random function, you might be either pulling multiple random records at a time or using a span of records that are contingent upon the random record. Expanding upon our standard Random example, you can handle both of these situations with SQL.
To pull several randomly selected records into the same recordset, you could store three random numbers and then query the database for the records matching those numbers:
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3
From random to there
If you want to pull 10 records (maybe a list of 10 links that changes every time the page loads), you could use BETWEEN or a mathematical equation to pull the first record and the appropriate number of incremental records. This could be set up a number of ways, but this SELECT statement shows one possibility (ID is of the automatic number type here):
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"
Note: This doesn't test to see if there are nine subsequent records left in the database. If you want to ensure that you pulled 10 records every time, you would have to further tailor the query.
9. Formally speaking
Pulling information from a form is something you do every day in ASP, right? But how long does it take you to cycle through records to find the one that passed through the form? It depends on how big the database is. Even a simple homegrown GUI interface that you set up to help increase your team's productivity can be bogged down by the loop-until-you-find-it routine.
For example, suppose a team member logs in on the main screen of your GUI by entering her first and last name joined by a period: amy.cowen. That value is passed via the form, and her current list of projects is pulled up and displayed on the screen. To quickly isolate that user's record so that it can be spit to the screen, you can simply do the following.
Assume the HTML page contains this chunk of code:
<FORM ACTION="login_post.asp" METHOD="POST">
<INPUT TYPE="text" NAME="dotname">
<INPUT TYPE="submit">
</FORM>
Within your login_post.asp code, you would want to read the value of the dotname field and find the matching record in your database:
SQL = "SELECT dotname, ID FROM team WHERE dotname = '" & Request.Form("dotname") & "'"
If you employ the GET method and need to compare the database records to the value of a field in the QueryString, you use the same basic approach:
SQL = "SELECT dotname, ID FROM team WHERE dotname = '" & Request.QueryString("dotname") & "'"
You could also, of course, assign the value of the form field to a variable and plug it into your SQL statement:
dotname = Request.QueryString("dotname")
SQL = "SELECT dotname, ID FROM team WHERE dotname = '" & dotname &"'"
10. View by
For pages that have many records, there are times when being able to sort the information in descending order is more effective than always having an ascending alphabetical view. Putting what you know about the ORDER BY clause to use, you can easily add this functionality to your pages and give the users control of how they view the records.
Assume that you have a page called customers.asp that pulls all the data from the Customers table and sorts it alphabetically by the customer's last name. We looked at how to set that code up earlier. To sort backwards, you just need to add desc after the ORDER BY statement:
SQL = "SELECT c_lastname, c_firstname, c_email FROM Customers ORDER BY c_lastname desc"
That's good when you're in static mode. But if you want to allow users to specify this on the fly, you need to tweak this code just a bit to offer our new GUI functionality:
<A HREF="customers.asp ">Sort Ascending</A> | <A HREF="customers.asp?sort=desc">Sort Descending</A>
<P>
<%
SQL = "SELECT c_lastname, c_firstname, c_email FROM Customers ORDER BY c_lastname " & sort & " "
set objRec = objConn.Execute(SQL)
While Not objRec.EOF
Response.Write objRec("c_lastname") & ", " & objRec("c_firstname") & "
" & objRec("c_email") & "<P>"
objRec.MoveNext
Wend
%>
At the top, links allow the user to change the view of the page. You can see that this information is going to reload the same page, but in the case of the Sort Descending link, is passing along a value for the sort variable. Then, in the SQL statement, instead of specifying desc, you're going to use the value of the sort variable.
The only thing left is to read in the value of sort at the top of the page.
<%
sort = Request.Querystring("sort")
%>
This line picks up the value for sort, if available, from the URL.
Without taxing yourself too much, you can tweak this same page to allow users to sort by specific columns, too. A simple SELECT menu form control listing SORT BY options could be a winning touch. You know, just enough glitz to keep them thinking you're working hard!
11. Counting records
Determining how many records are in your database, or how many meet certain criteria, is easily done with ASP. If you use the correct cursor type, you can use the RecordCount property to retrieve the number and still use the recordset. But if you really just want to know the total and don't care about manipulating or displaying records based on your query, you can use count(*) in your SELECT statement, as shown here:
SQL = "SELECT count(*) FROM Customers"
or
SQL = "SELECT count(*) FROM Customers WHERE c_lastname LIKE 'A%'"
You might be tempted to try and do something like this, pulling a set of related records and a count:
SQL = "SELECT c_firstname, c_lastname, count(*) FROM Customers WHERE c_lastname LIKE 'A%'"
But you can't. The "count" function you've used is really an aggregate function or a set function, meaning that it returns only a single row of information: the answer to the question you've posed. For the first SELECT statement, that question is, "How many records are there in the customers table?" The query returns a single value in response, so it can't be combined with your regular query. If you want the other data, you'll need to use RecordCount.
Aggregate functions other than "count" include AVG, MIN, MAX, and SUM.
For more information on aggregate functions, see James Hoffman's coverage in Introduction to Structured Query Language.
12. Joins
Anyone familiar with SQL and relational databases has met the numerous types of joins available. In the simplest terms, a join allows you to combine the contents of two tables into one virtual table or recordset. If your table has been normalized effectively, you will often want to pull up specific information from one table and pull along associated information from another table. Doing so will often require a simple "equijoin."
To see the join in action, let's assume you keep records on your hot software line in a general database. One table (Software) contains the product name, the version of the software, and other related details:
 |
Another table (Releases) houses information on your release history, including date of the release and status of that release (e.g., beta, current, obsolete):
 |
This table also contains a column that refers to the ID numbers used in the software table. So, by cross-referencing the software table, you know that the release with a software_ID of 2 is Rome.
Instead of going back and forth between the tables, you can use a join to combine the information. However, you don't just want to combine it; you want to merge the related information. So wherever software_ID in the releases table matches the ID in the software table, you want to put that information together in a single record.
Here's the code:
SQL = "SELECT * FROM Software, Releases WHERE software.ID = releases.softwareID"
Dissecting this statement, you first notice that two table names are listed after FROM. Depending on the kind of join you are using, you may see this syntax vary (or the type of join explicitly named), but this basic syntax shows you how the tables are being selected. The WHERE clause is then being used here to compare the specific ID values. In the Software table, the column ID exists. Similarly, software_ID is in the Releases table. To identify which values you are comparing in the WHERE clause, you use the table name as a prefix, followed by a period (.).
When you're ready to use the information gathered by the query, the columns from both tables are available:
 |
Note: When you are creating your own joins, make sure you think through what columns you want to pull. You pulled * here so that you could focus on the rest of the SELECT line. But, as you can see from the image, you definitely didn't want to pull the softwareID column since it has no added value as part of the recordset. It served its purpose by being available for the WHERE clause.
