Home & Office

Using SQL statements with ASP

Using ASP and SQL together can be confusing. Let these 12 SQL tips for ASP help ease the confusion.

Written by Amy Cowen, Contributor Nov. 29, 2004 at 1:14 a.m. PT

Using ASP and SQL together can be confusing. Help ease he confusion with the help of these 12 SQL tips for ASP.

For most Web builders, using databases goes no further than presenting data from Access. If you're using ASP, however, SQL statements can supply significant and sophisticated methods to get at the data. Both ASP and SQL are powerful tools for mining information, combining data from different sources, and developing Web applications.

Using ASP and SQL together can be confusing. There are so many different SQL products available that you might be tempted 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 in your ASP Web pages.

BuilderAU.com.au will teach you 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 hand--writing 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.

12 SQL tips for ASP

1. The SELECT statement
The basis of much of the work you do with SQL is the SELECT statement. If you we 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 mould the statement to fit your needs using traditional SQL query patterns and criteria.

For example, if we have a table named Products and we want to pull all of the records, we write:

SQL ="SELECT * FROM Products"

That pulls everything--all the records that make up the table. But, say we want to pull only a specific column, p_name, from the table. Instead of the * wildcard, we 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 we wanted to pull only p_name records that started with the letter w, we 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 our specifications. In this case, we 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--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 in the above 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.

3. LIKE and NOT LIKE and BETWEEN
You saw LIKE in use in the sample statement where we 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 _ stands for any character, but only a single character. So, by entering 1 _ _ _ 5, we 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 we want to pull the records between 1 and 10 in a given table. We can set that up using BETWEEN:

…WHERE ID BETWEEN 1 AND 10

Or we 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 an 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 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 smoosh 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

"Customers" represents the name of the table in the database you've opened.

Open 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 the example above, you see that SQL is the variable in which we've stored our 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 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 Response.Write yourVariable name to write the information to the screen. Each time you post an 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 above 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 () 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 we have a database with the following records:

The single-level ORDER BY we used above would spit the records out 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, we just need to add second-level ORDER BY criteria by incorporating the name of the next column we want to use next in our 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, we pull a random number between 1 and 500 (assume 500 is the total number of records in your database). Then, we go through each record testing the value of the ID to see if it matches RNumber. When it does, we complete the THEN chunk of code. If our 's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">

's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table=""> 's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">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.

's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table=""> 's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">From Random to there
's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">If you want to pull ten records (maybe a list of ten links that changes every time the page loads), you could use BETWEEN or a mathematical equation (LINK to earlier coverage) 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).

's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">'s="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">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 passed through the form? Depends on how big the database is. Even a simple homegrown GUI interface you set up to help increase your team's productivity can be bogged down by the loop-until-you-find-it routine. For example:

's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">'s="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">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 this:

's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">'s="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table=""> 10. Let users control data view
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.

's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">'s="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table=""> <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 %>

's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">'s="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table=""> The first line of the code is specific to the task at hand. At the top, we now have links allowing 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, we're going to use the value of the sort variable.

's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">'s="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table=""> 11. Counting records
Determining how "many" records are in your database, total, 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 below:

's="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table="">'s="" small="" database="" compared="" to="" more="" robust="" enterprise="" solution="" that="" may="" have="" thousands="" of="" records="" in="" a="" single="" table=""> But you can't. The "count" function we'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 our first SELECT statement above, 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.

12. Join
Anyone familiar with SQL and relational databases has met the numerous types of joins available. In simplest terms, a join allows you to combine the contents of two tables into one virtual table or recordset. If your table has been normalised 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 we keep records on our 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 our 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, we know that the release with a software_ID of 2 is x product.

Instead of going back and forth between the tables, we can use a join to combine the information. However, we don't just want to combine it; we want to merge the related information. So, wherever software_ID in the releases table matches the ID in the software table, we 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 we 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. We pulled * here so that we could focus on the rest of the SELECT line. But, as you can see from the image, we 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.

Editorial standards

Show Comments

Using SQL statements with ASP

Related

7 reasons I use Copilot instead of ChatGPT

Facebook's Meta AI is lying when it says you can disable it - but here's what you can do

The work laptop I recommend to most people is not made by Apple or Lenovo