There are so many different kinds of SQL products that you may just throw off your sleeves and get started without taking care of the rest. But if you want to use ASP and SQL at the same time, you may get dizzy.
MySQL, SQL Server, and mSQL are all excellent SQL tools, but unfortunately, you can't use them to create practical SQL statements in an ASP environment. However, you can use your own Access knowledge and corresponding Access skills, coupled with our tips and tricks, I believe you will be able to successfully add SQL to your ASP web page.
1. SELECT statementIn the world of SQL, the most basic operation is the SELECT statement. When using SQL directly under a database tool, many people will be familiar with the following operations:
SELECT what
FROM whichTable
WHERE criteria
Executing the above statement creates a query that stores its results.
On ASP page files, you can also use the above general syntax, but the situation is slightly different. When programming in ASP, the content of the SELECT statement must be assigned to a variable as a string:
SQL = "SELECT what FROM whichTable WHERE criteria"
Okay, now that you understand the way SQL "speaks" under ASP, you can follow the same method. As long as it meets your needs, traditional SQL query modes and conditional queries can be used.
For example, suppose you have a data table in your database named Products, and now you want to retrieve all the records in this table. Then you write the following code:
SQL ="SELECT * FROM Products"
The above code - the function of the SQL statement is to retrieve all the data in the table - after execution, all records in the data table will be selected. However, if you only want to retrieve a specific column from the table, such as p_name. Then you cannot use the * wildcard character. You have to type in the name of a specific column. The code is as follows:
SQL ="SELECT p_name FROM Products"
After executing the above query, all the contents of the p_name column in the Products table will be selected.
2. Use WHERE clause to set query conditionsSometimes retrieving all database records may just meet your requirements, but in most cases we usually only need to get part of the records. How should we design the query in this case? Of course it will take a little more thinking, not to mention that this article deliberately doesn't want you to use that crappy recordset.
For example, if you only plan to retrieve p_name records, and the names of these records must begin with the letter w, then you will use the following WHERE clause:
SQL ="SELECT p_name FROM Products WHERE p_name LIKE 'W%'"
The WHERE keyword is followed by conditions used to filter data. With the help of these conditions, only data that meets certain criteria will be queried. In the above example, the query result will only get p_name records whose names start with w.
In the above example, the meaning of the percent symbol (%) is to instruct the query to return all record entries that begin with the letter w and are followed by any data or even no data. Therefore, when executing the above query, west and willow will be selected from the Products table and stored in the query.
As you can see, with careful design of your SELECT statement, you can limit the amount of information returned in the recordset, and more thought will always satisfy your requirements.
These are just the beginning of mastering the use of SQL. To help you gradually master the complex use of SELECT statements, let's take a look at key standard terms: comparison operators. These things are often used when you build your own SELECT string to obtain specific data.
WHERE clause basics
When starting to create a WHERE clause, the easiest way is to use the standard comparison symbols, which are <, <=, >, >=, <>, and =. Obviously, you will quickly understand the meaning and specific 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: You will notice here that the number 4 in the last example sentence has single quotes around it. The reason is this, '4' in this example is a text type and not a numeric type. Because you enclose the SELECT statement in quotes to assign it as a value to a variable, you can also use quotes within the statement.
comparison operator
Comparison operators specify the content range of data to be retrieved from the table. You can use them to create filters to narrow the recordset to only the information you care about for a given task.
3. LIKE, NOT LIKE and BETWEENYou have seen the use of LIKE in the above example of removing records starting with w. The LIKE predicate is a very useful symbol. However, using it may give you too much data in many cases, so it is best to use your brain to think more about what data you want to get before using it. Suppose you want to extract a 5-digit SKU number that starts with 1 and ends with 5, then you can use the underscore (_) instead of the % symbol:
SQL = "SELECT * FROM Products WHERE p_sku LIKE '1___5'"
The underscore represents any character. So if you enter "1 _ _ _ 5", your search will be limited to 5 digits that match the specific pattern.
If you want to do the opposite, find all SKU entries that do not match the "1_ _ _ 5" pattern. Then you only need to add NOT in front of LIKE in the statement example just now.
BETWEEN
Suppose you want to retrieve data within a certain range, and you know the starting point and end point of the range in advance, then you might as well use the BETWEEN judgment word. Now let us assume that you want to select records in the range between 1 and 10 in a given table. You can use BETWEEN as follows:
…WHERE ID BETWEEN 1 AND 10
Or you can use the mathematical judgment words you are already familiar with:
…WHERE ID >= 1 AND ID >= 10
4. Union statementThe SQL statements we have talked about so far are relatively simple. If they can be queried through a standard recordset loop, then these statements can also meet some more complex requirements. However, why do you have to stick to the basic level of just trying it? You can add other symbols, such as AND, OR and NOT to complete more powerful functions.
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%'"
Based on your current knowledge of SQL, the above example is not difficult to explain, but the above statement does not clearly allow you to see how the conditional statements are glued together in a single SQL statement.
multi-line statement
When the SQL statement is difficult to understand, you may wish to decompose the entire statement into multiple lines of code, and then gradually add each component of the query statement based on the existing variables and store it in the same variable:
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"
By the last sentence, the SQL variable contains the following complete 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"
After the whole sentence is broken down as shown above, it is obviously much easier to read! When debugging, you may prefer to type a few more characters to make the program more readable. But you have to remember that you need to add spaces before closing the quotation marks or after opening the quotation marks, so as to ensure that you do not put several words together when the strings are connected.
5. Start execution
After learning the structure and purpose of the SELECT statement, it's time to learn how to use it. Depending on the database tool at your disposal, this might mean pressing a button that says "Go." On an ASP web page, SQL statements can be executed immediately or called as stored procedures.
Once you create the SQL statement, you still have to access its query results. Obviously, the key here is the ASP recordset. When using a non-SQL recordset, the code to create the recordset usually looks like this:
Dim objRec
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "customers", objConn, 0, 1, 2
If you are familiar with ASP, the above code will be familiar to you. You should know that "customers" means the name of a data table in the database you open.
Open recordset
To take full advantage of your more familiar SQL skills, you'll need to adapt the recordset most commonly used on regular ASP web pages:
Dim objRec
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open SQL, objConn, 0, 1, 2
The only modification here is in objRec.Open, after which the name of the data table to be queried is replaced with a variable containing the SQL statement.
One of the advantages of this approach is that you can specify the cursor type (shown as 0, 1, 2 above).
Execute SQL
You can create a recordset by executing a SQL statement with a compact line of code. Here is the syntax:
Dim objRec
set objRec = objConn.Execute(SQL)
In the above example, the SQL you see is the variable in which you store your own SQL SELECT statement. This line of code "runs" an SQL statement (or query the database), selects data, and stores the data in a recordset, in this case the variable objRec. The main disadvantage of this approach is that you can't choose the type of cursor you want to use. In contrast, recordsets are always opened with a forward cursor.
Because of cursors, you may want to familiarize yourself with two methods of creating a recordset. Executing the query directly saves the time spent typing characters, but then you have to use the default cursor, which may encounter problems that often do not work properly. No matter which method you use, the biggest difference between the two is simply whether the code is concise or not. Regardless of what fields you obtain, what your standards are, or how you store the data, the SQL-style recordset will be much smaller in size than the standard recordset opened on ASP, let alone easy to operate. Simplicity. After all, by filtering the data, you eliminate time-consuming if-then tests and possible loops.
Write test SQL
Here is a trick. Many professional ASP programmers are used to "writing" their own SQL statements when testing web pages. Doing this can help you debug your code because you can see the string being passed to the server for execution. And all you have to do is add Response.WriteyourVariable to display the relevant information on the screen. You should include this information when you submit a SQL-related question to the ASP discussion group.
6. Storage queryWhen your query is relatively simple, it doesn't take much effort to create a SQL statement from scratch every time. However, the situation is different with complex queries. Starting from scratch every time will produce a lot of development errors. Therefore, once you have the SQL running smoothly, you'd better save them and call them when needed. In this way, even for a simple query, you can use the stored query statement at any time.
Suppose you have to make a weekly report to your team, pointing out the current business support issues. This data needs to be selected from your database, and records must be selected according to date, and sorted according to the support issue category adopted by your team. . Once you design this query, why would you need to rewrite it every week? Instead of creating the query on your HTML page, you should use your database tool to create the query and save it.
You can then use the ActiveCommand attribute to insert the query into your ASP page. You may think it’s not interesting the first time or two, but it’s actually just a few lines of code:
Set objSQ = Server.CreateObject ("ADODB.Command")
objSQ.ActiveConnection = "databaseName"
objSQ.CommandText = "storedQueryName"
objSQ.CommandType = adCmdStoredProc
set objRec = objSQ.Execute
Note that using adCmdStoredProc means that you have included the adovbs.inc file on the page. This file defines Access constants that you can access by name rather than by number. Just include the file on the page (<!--#INCLUDE -->) and then you can use a name like adCmdStoredProc. In this way, it will be easier for you to understand what the above stored query means when you see it in the future.