Recommended: Use SQL-DMO to create backup and verification ASP applications Overview SQL Distributed Management objects (SQL-DMO) provide developers with methods to perform ordinary tasks using program and scripting languages, thus extending the functionality of SQL Server. This article talks about how to cleverly use SQL-DMO to create
1. SELECT statement
In the SQL world, the most basic operation is the SELECT statement. When using SQL directly under the database tool, many people will be familiar with the following operations:
| The following is the quoted content: SELECT what FROM whichTable WHERE criteria |
Executing the above statement will create 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 ASP, the content of the SELECT statement should be assigned to a variable as a string:
| The following is the quoted content: SQL = SELECT what FROM whichTable WHERE criteria |
Okay, I understand how SQL speaks under ASP, and then do it the same way. As long as you meet your needs, the traditional SQL query mode and conditional query can be useful.
For example, you might as well assume that there is a data table in your database, namely Products, and now you want to retrieve all the records in this table. Then you wrote the following code:
| The following is the quoted content: SQL =SELECT * FROM Products |
The function of the above code-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 remove a specific column from the table, such as p_name. Then you can't use the * wildcard character. You have to type the name of a specific column, and the code is as follows:
| The following is the quoted content: SQL =SELECT p_name FROM Products |
After executing the above query, all the contents in the Products table and the p_name column will be selected.
2. Set query conditions for WHERE clause
For example, if you only plan to take out the p_name records, and the names of these records must be started with the letter w, then you have to use the following WHERE clause:
| The following is the quoted content: SQL =SELECT p_name FROM Products WHERE p_name LIKE 'W%' |
The WHERE keyword is followed by the conditions used to filter data. With the help of these conditions, only data that meets certain standards will be queried. In the above example, the query result will only get a p_name record with the name starting with w.
In the above example, the percentage symbol (%) means that the query returns all record entries that start with w letters 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, just design the SELECT statement carefully, you can limit the amount of information returned in the recordset, and thinking more will always meet your requirements.
These are just the beginning of mastering SQL usage. To help you gradually master the usage of complex SELECT statements, let's take a look at the key standard terms: comparison operators. These things are often used when you build your own SELECT strings to obtain specific data.
WHERE clause basics
When you start creating a WHERE clause, the easiest way is to use standard comparison symbols, which are < , <= , > , >= , <> and =. Obviously, you will soon understand the meaning and specific results of the following code:
| The following is the quoted content: 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 surrounded by single quotes. The reason is that in this example, 4 is a text type rather than a numeric type.
3. Comparison operators: LIKE, NOT LIKE and BETWEEN
The comparison operator specifies the content range for which data is retrieved from the table. You can use them to create filters to narrow down the scope of the recordset, causing it to save only the information you care about under a given task.
You have seen the usage of LIKE in the example where you took out the header record of w above. The LIKE determinant is a very useful symbol. However, in many cases, using it may bring you too much data, so it is best to use your brain and think more about what data you want to obtain before using it. Suppose you want to take out the SKU number of 5 digits, and its beginning is 1 and ending is 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 when you enter 1___5, your search will be limited to the 5-digit range that satisfies a specific mode.
If you want to do the opposite, find out all SKU entries that do not match the 1___5 mode. Then you just need to add NOT to the LIKE in the statement example just now.
BETWEEN
Suppose you want to extract 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's assume that you want to select records with a 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 familiar mathematical judgment sentences:
…WHERE ID >= 1 AND ID >= 10
4. Joint statement
The SQL statements we have talked about so far are relatively simple. If they can pass the standard recordset loop query, 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 out? You can add some other symbols, such as AND, OR and NOT, to complete more powerful functions.
The following SQL statement is an example:
| The following is the quoted content: 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 the SQL knowledge you currently have, the above examples are not difficult to explain, but the above statement does not clearly allow you to see how the conditional sentences are glued into a single SQL statement.
Multiple line statements
If SQL statements are not easy to understand, you might as well decompose the entire statement into multiple lines of code, and then gradually add the various components of the query statement based on the existing variables and store it in the same variable:
| The following is the quoted content: 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:
| The following is the quoted content: 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 decomposing the whole sentence above, it is obviously easier to read! When debugging, you may be more willing to type a few more characters to change the program and read it better. But you have to remember that you need to add spaces before closing quotes or after opening quotes, so that you don’t put a few words together when the string is connected.
5. Start execution
After learning the construction and use of SELECT statements, it is time to learn how to use them. Under the database tools you have, this may mean you have to press a button with the word "Execution". On an ASP web page, SQL statements can be executed immediately or they can be called as stored procedure.
Once the SQL statement is created, you also have to try to access its query results. Obviously, the key here is ASP recordset. To make the most of your SQL skills you are more familiar with, you need to adjust the recordsets that are most commonly used on regular ASP web pages:
| The following is the quoted content: Dim rs Set rs = Server.CreateObject (ADODB.Recordset) rs.Open SQL,Conn,1,2 |
Here Conn is the database connection declaration, and the only modification is to use a variable containing the SQL statement to replace the name of the data table to be queried.
One of the advantages of this approach is that you can specify the cursor type (as shown in 1 and 2 above).
Execute SQL
You can also create recordsets using a compact line of code to execute SQL statements. The following is the syntax:
Dim rs
set rs = Conn.Execute(SQL)
In the example above, the SQL you see is a variable that you store your own SQL SELECT statement. This line of code runs SQL statements (or query the database), selects data and stores the data in recordset, in the above example, the variable rs. The main disadvantage of this approach is that you cannot choose the type of cursor you want to use. Instead, recordset is always opened with a forward cursor.
Because of cursors, you may be familiar with two ways to create recordsets. Executing the query directly saves the time spent typing characters, but in that case you have to use the default cursor, which may often fail to function normally. No matter which method you use, the biggest difference between the two is nothing more than whether the code is refined or not. Without considering what fields you get or what your standards are, and regardless of how you store data, using SQL-style recordset will be much smaller in size than the standard recordset opened on ASP, not to mention the ease of operation. After all, by filtering data, you eliminate time-consuming if-then tests and possible loops.
6. Store query
When your query is relatively simple, it doesn't take much effort to create SQL statements from scratch every time. However, complex queries are different. Every time you start from scratch, you will generate many development errors. So once SQL runs smoothly, you'd better save them and call them if needed. In this way, even a simple query can be used to store the query statement at any time.
Suppose you have to report to your team once a week to indicate the current business support issues, which need to be selected from your database, and records should be selected by date, and sorted according to the category of support issues you are using. Once you have designed this query, why do you have to rewrite it once a week later? Don't create queries on your HTML page, you should create queries with your database tools and save them. Then you can use the ActiveCommand property to insert the query into your ASP page. You may find it meaningless in the first or two, but in fact it is just a few lines of code:
| The following is the quoted content: 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 the Access constants that you can access by name rather than by number. Just include the file on the page), and then you can use adCmdStoredProc. In this way, it will be easier to understand what the stored query above means when you see it in the future.
7. ORDER BY
Select the most disgusting things from the Access database, and they are entered into the database in the order they are entered. Even if you use Sort By in the Access environment to change the record view, the order of records in the data table has not changed.
If you are using ASP recordset to write records on a web page, then you may know how painful it is to get in the mess. But you may have to face this problem often because there is no simple and convenient solution. Fortunately, ORDER BY can simplify this problem.
To sort your results, just add ORDER BY to the end of the SELECT statement and specify the reference column you need to sort. So, if you want to sort the Customers table by the customer's last name, you can write the following query statement:
SQL = SELECT c_lastname,c_firstname,c_email FROM Customers ORDER BY c_lastname
In this way, as long as you create a recordset and start writing the results to the screen, you will see that the data are arranged alphabetically.
The following are the operations on the table:
Only copy the structure of table a to table b (no copying data)
| The following is the quoted content: sql = select * into b from a where 1<>1 sql = select * into b from a where 1=0 |
Copy the structure and data of table a to table b
| The following is the quoted content: sql = select * into b from a |
Share: ASP 3.0 Advanced Programming (45) Asynchronous execution refers to retrieving data in the background, and the data you have obtained can be used on the web page before all the data is returned. Although all data may be needed, asynchronous work can start processing data at least in advance. It can also allow users to see certain content first, which makes the Web site