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 that says "Execute". 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 have to try to access its query results. Obviously, the key here is ASP recordset. When using non-SQL recordsets, the code to create a recordset is usually as follows:
Dim objRec
Set objRec = ("")
"customers", objConn, 0, 1, 2
If you are familiar with ASP, you are not unfamiliar with you. You should know that "customers" means the name of a data table you open in the database.
Open 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:
Dim objRec
Set objRec = ("")
SQL, objConn, 0, 1, 2
The only modification here is to replace the name of the data table to be queried with a variable containing the SQL statement.
One of the advantages of this approach is that you can specify the cursor type (as shown in 0, 1, 2 above).
Execute SQL
You can create recordsets using a compact line of code to execute SQL statements. The following is the syntax:
Dim objRec
set objRec = (SQL)
In the above example, the SQL you see is a variable that you store your own SQL SELECT statement. This line of code "runs" the SQL statement (or querying the database), selects data and stores the data in recordset. In the above example, the variable objRec. 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 simplicity of operation. After all, by filtering 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 will help you debug your code, because you can see the string passed to the server for execution. And all you have to do is add the relevant information to display on the screen. You should attach this information when you submit SQL-related issues to the ASP discussion group.
6. Storage 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 record by date, and sort by 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 web page. You may find it meaningless in the first or two, but in fact it is just a few lines of code:
Set objSQ = ("")
= "databaseName"
= "storedQueryName"
= adCmdStoredProc
set objRec =
Note that using adCmdStoredProc means that you have included the 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 (<!--#INCLUDE -->), and then you can use names like 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 recording in the data table has not changed.
If you are using ASPrecordset to write records on a web page, then you may know how painful it is to be 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 at 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.
Multi-level sorting
In fact, it is not only possible to sort first-levelly in SQL statements. In fact, in many cases, you may want to specify a data sort of two to three levels of depth. Suppose you have the following data table, which looks like this:
The previously used single-level ORDER BY sorting data is retrieved in the following order:
Absurdly Assured
absurd@
Absolutely Assured
absolutely@
Crazed Coder
crazy@
Loosely Fringe
loose@
Lunatic Fringe
lune@
Hands On
hands@
Previous page123Next pageRead the full text