Asp classic Getting Started Guide using SQL statement page 1/2 in ASP

MySQL, SQL Server, and mSQL are both excellent SQL tools. Unfortunately, in the ASP environment, you do not need them to create practical SQL statements. However, you can use your Access knowledge and corresponding Access skills, coupled with our tips and skills, I believe you will be able to successfully add SQL to your ASP web page.

1. SELECT statement

In the world of SQL, the most basic operation isSELECTStatement. When using SQL directly using database tools, many people will be familiar with the following operations:
SELECT what
FROM whichTable
WHERE criteria

Execute the preceding statement to create a query that stores the result.

In ASP page files, you can also use the above general syntax, but the situation is slightly different, when ASP programming,SELECTThe statement content should be assigned to a variable as a string:
SQL = "SELECT what FROM whichTable WHERE criteria"

Now, I understand the way SQL is "spoken" in ASP. The next step is the method. As long as you meet your needs, the traditional SQL query mode and conditional query can be used.

For example, assume that your database has a data table namedProductsNow you want to retrieve all records in this table. Then you write the following code:
SQL = "SELECT * FROM Products"

The preceding code -- SQL statement is used to retrieve all the data in the table. After execution, all records in the data table are selected. However, if you only want to retrieve a specific column from the table, for exampleP_name. Then you cannot use the * wildcard. enter the name of a specific column. The Code is as follows:
SQL = "SELECT p_name FROM Products"

After the preceding query is executedProductsTable,P_nameAll column content will be selected.

2. Use the WHERE clause to set query Conditions

Sometimes retrieving all the database records may meet your requirements. However, in most cases, we only need to obtain some records. In this way, how can we design a query? Of course, it will be a little more time-consuming. Besides, this article does not want you to use the recordset.

For example, if you only want to retrieveP_nameRecords, and the names of these records must be lettersWHeaders, then you need to use the followingWHEREClause:
SQL = "SELECT p_name FROM Products WHERE p_name LIKE 'W % '"

WHEREKeywords are followed by conditions used to filter data. With the help of these conditions, only data that meets certain criteria can be queried. In the preceding example, the query results only get the nameWHeadersP_nameRecord.

In the preceding example, the percent sign (%) indicates that the query returns allWStart with a letter and follow any records with or without data. Therefore, when performing the preceding query,WestAndWillowFromProductsThe table is selected and stored in the query.

As you can see, just design it carefully.SELECTStatement, you can limit the amount of information returned in the recordset.

These are just the first steps to grasp the use of SQL. To help you gradually master complexSELECTThe usage of statements. Let's take a look at the key standard terms: Comparison operators. You are building your own things.SELECTString is often used to obtain specific data.

WHERE clause Basics

Start creatingWHEREWhen using clauses, the simplest method is to use standard comparison symbols, which are <, <=,>,> =, <>, and =. Obviously, you will soon be able to understand the meaning and specific running results of the following code:
SELECT * FROM Products WHERE p_price> = 199.95
SELECT * FROM Products WHERE p_price <> 19.95
SELECT * FROM Products WHERE p_version = '4'

Note:Here you will notice that the number 4 in the last example is enclosed in single quotes. The reason is: in this example'4'Is a text type, not a number type. Because you willSELECTThe statement is enclosed in quotation marks and assigned to the variable as a value. You can also use quotation marks in the statement.

Comparison Operators
The comparison operator specifies the content range of the data retrieved from the table. You can use them to create filters to narrow the scope of the recordset, so that they only store the information you are concerned about under a given task.

3. LIKE, not like, and

You have taken outWAs shown in the example of LoggingLIKE.LIKEA word is a very useful symbol. However, in many cases, using it may bring you too much data, so before using it, you 'd better start thinking about what data you want. Assume that you want to retrieve the SKU number with five digits and start with "1" and end with "5", you can use the underscore (_) to replace the "%" symbol:
SQL = "SELECT * FROM Products WHERE p_sku LIKE '1 ___ 5 '"

The delimiter represents any character. Therefore, when "1 _ 5" is entered, your search will be limited to a 5-digit range that meets the specific mode.

If you want to do the opposite, you need to find all SKU entries that do not match the "1 _ 5" mode. Then you only needLIKEAddNOTYou can.

BETWEEN
If you want to retrieve data within a certain range, and you know the start and end of the range in advance, you may wish to useBETWEENJudgment word. Now let's assume that you want to select a record with a range of 1 and 10 in a given table. You can useBETWEEN:
... Where id between 1 AND 10

Or you can use the familiar mathematical judgment words:
... Where id> = 1 and id> = 10

4. Union statement

The SQL statements we have mentioned so far are relatively simple. If we can use standard recordset for loop query, these statements can also meet more complex requirements. However, why do we have to stick to the basic level of simplicity? You can add other symbols, suchAND,ORAndNOTTo complete more powerful functions.

The following SQL statement is used 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 % '"

As far as your current knowledge of SQL is concerned, the above examples are not difficult to explain, but the above statements do not clearly show you how condition statements are glued into a single SQL statement.

Multi-line statements
When SQL statements are hard to understand, you may want to break down the entire statement into multiple lines of code, and then gradually add the components of the query statement based on the existing variables and store them in the same variable:
SQL = "SELECT c_firstname, c_lastname, c_emailaddress, c_phone"
SQL = SQL & "FROM MERs"
SQL = SQL & "WHERE c_firstname LIKE 'a % 'and c_emailaddress NOT NULL"
SQL = SQL & "ORDER BY c_lastname, c_firstname"
In the last sentence, the SQL variable contains the following completeSELECTStatement:
"SELECT c_firstname, c_lastname, c_emailaddress, c_phone FROM MERs
WHERE c_firstname LIKE 'a % 'and c_emailaddress no null order by c_lastname,
C_firstname"

The entire sentence is obviously much read after being decomposed! During debugging, you may be more comfortable typing a few more characters to better read the program. However, you must remember that you need to add spaces before or after the quotation marks are closed to ensure that the strings are not joined together.

5. Start execution

After learningSELECTAfter the statement is constructed and used, you should learn how to use it. Under the database tool you have mastered, this may mean that you have to press a button that says "execute. On the ASP Webpage, you can immediately execute SQL statements or call them as stored procedures.

Once an SQL statement is created, you have to try to access its query results. Obviously, the key here is ASP recordset. When a non-SQL recordset is used, the code for creating a recordset is usually as follows:
Dim objRec
Set objRec = Server. CreateObject ("ADODB. Recordset ")
ObjRec. Open "MERs", objConn, 0, 1, 2

If you are familiar with ASP, the above Code is no stranger to you. You should know that "MERs" indicates the name of a data table in the database.

Open recordset
To make full use of the SQL skills you are more familiar with, you need to adjust the recordset most commonly used on ASP Web pages:
Dim objRec
Set objRec = Server. CreateObject ("ADODB. Recordset ")
ObjRec. Open SQL, objConn, 0, 1, 2

The only modification here is inObjRec. OpenAnd then replace the name of the data table to be queried with the variable containing the SQL statement.

One of the advantages of this method is that you can specify the cursor type (such as the above0, 1, 2).

Execute SQL
You can use a compact line of code to execute SQL statements to create A recordset. Syntax:
Dim objRec
Set objRec = objConn. Execute (SQL)

In the preceding example, what you see is that you store your own SQLSELECTStatement variable. The code line "runs" an SQL statement (or queries the database), selects data and stores the data in the recordset. In the preceding example, the variable is used.ObjRec. The main disadvantage of this method is that you cannot select the cursor type you want. On the contrary, recordset always opens with a forward cursor.

Because of the cursor, you may be familiar with the two methods for creating recordset. Directly executing a query saves the time required for typing characters, but you have to use the default cursor. This may cause frequent failures. No matter which method you adopt, the biggest difference between the two is no more than whether the code is refined or not. Without considering what fields you obtain and what your standards are, no matter how much data you store, SQL-based recordset is much smaller in size than the standard recordset opened on ASP, let alone the simplicity of the operation. After all, by filtering data, you eliminate the time-consuming if-then test and the loops that may be used.

Write test SQL
Here is a tip. Many professional ASP programmers are used to "writing" their own SQL statements when testing web pages. This can help you debug the code, because you can see the strings passed to the server for execution. What you need to do is increaseResponse. WriteYourVariableDisplays related information on the screen. This information should be attached when you submit SQL-related questions to the ASP discussion group.

6. Storage Query

When your query is relatively simple, it does not take much time to create an SQL statement from the beginning. However, complicated queries are different. Every time you create an SQL statement from the beginning, many development errors will occur. Therefore, once the SQL statements run smoothly, you 'd better save them and call them as needed. In this way, even for a simple query, you can use the stored query statement at any time.

Assume that you have to submit a report to the team every week to point out the existing business support problems. The data needs to be selected from your database and recorded by date, at the same time, it is sorted by the categories of support questions adopted by your team. Once you have designed this query, why do you write it again every week? Do not create a query on your HTML page. You should use your database tool to create a query and save it.

Then you can useActiveCommandProperty to insert the query to your ASP Webpage. The first one or two times you may feel boring, but there are just a few lines of code:
Set objSQ = Server. CreateObject ("ADODB. Command ")
ObjSQ. ActiveConnection = "databaseName"

ObjSQ. CommandText = "storedQueryName"
ObjSQ. CommandType = adw.storedproc

Set objRec = objSQ. Execute

Note thatAdCmdStoredProcIndicates that you have includedAdovbs. incFile. This file defines the Access constant that you can Access by name rather than number. You only need to include this file on the page (<! -- # INCLUDE -->), And then you can useAdCmdStoredProcThis type of name. In this way, you will be more likely to understand the meaning of the stored query in the future.