A rchive Date
[ 24-03-2002 ]
Category
[ Information Technologies ]
sub-Categoy
[ Microsoft ]
|
[
http://builder.com.com/article_guest.jhtml?id=u00120011026gcn01.htm
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.
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. |
|