The use of SQL statements in ASP tutorial (ii)

6. Store the query

When your query is relatively simple, it doesn't take much time to create SQL statements from scratch, but complex queries are different, and there are many development errors from scratch each time. So, once you have SQL running smoothly, you'd better save them and call them when you need them. In this way, even a simple query you can always use the stored query statement.

Let's say you have a weekly report to the team, pointing out the current business support issues that need to be selected from your database, and to select records by date, and sort by the category of support issues that your team uses. Once you have designed this query, why should you rewrite it every week? Instead of creating a query on your HTML page, you should create a query with your database tools and save it.

You can then insert the query into your ASP page using the ActiveCommand attribute. The first one or two of the time you might think it's no fun, but it's 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 Adovbs.inc files 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 adCmdStoredProc names. This will make it easier to understand what the above stored query means in the future when you see it.

7. ORDER BY

Selecting records from an Access database is one of the most frustrating things, in what order they are entered in the database. Even if you use sort by to change the view of a record in an access environment, the order of records in the datasheet does not change.

If you're using Asprecordset to write records on a Web page, you probably know how painful the order of jumbled is. But you may have to face the problem often, because there is no simple and easy solution. The good news is that the order by can simplify the puzzle.

To sort your results, simply add an order by to the end of the SELECT statement, and then specify the reference columns you want to sort. Therefore, if you want to sort the Customers table according to 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"

So, as soon as you set up the recordset and start writing the results to the screen, you'll see that the data is sorted in alphabetical order.

Multilevel sorting
In fact, not only can the SQL statements in the first order. In fact, in many cases, you might want to specify two to three levels of data ordering. Suppose you have the following data table, which reads as follows:

The previously used single order by sort takes out data in the following sequence:
Absurdly assured
Absurd@assured.com

Absolutely assured
Absolutely@assured.com

Crazed coder
Crazy@coder.net

Loosely Fringe
Loose@fringe.to

Lunatic Fringe
Lune@fringe.to

Hands on
hands@yes.org

Obviously The order is acting as it should. Under the actual table structure, the absurdly assured is the last entry, but it is ranked at the top of the search results. Hands on record last because O is at the end of the alphabet in the list above. Obviously, the absolutely is best ranked before absurdly by the alphabet. To do this, you need to take the 2nd level order by sort criteria and refer to column 2nd:
SQL = "Select C_lastname, C_firstname, c_email from Customers
C_lastname, C_firstname "

The results are sorted first by the c_lastname column and then by the c_firstname column. If your datasheet contains more records, careful design of the sort will make the output layout more reasonable.

Put into use
If you like most programmers, you love to make your own code and indulge in the frenzy of mastering new technology. Why don't you try the SQL code from an ASP's lengthy code? Here we will discuss the common problems of ASP programming and how to make efficient use of SQL statements in ASP.

11. Record statistics

It is not difficult to determine how many records are in a database, or how many records meet certain standards. If you use the correct cursor type, you can use the RecordCount property to get the number of records, of course, with the recordset. But there's a simpler way to do this is to take count (*) in your own SELECT statement, and the code looks like this:
SQL = "SELECT count (*) from Customers"

Or
SQL = "SELECT count (*) from Customers WHERE c_lastname like ' a% '"

For example, the following code selects some records and the total number of these records:
SQL = "Select C_firstname, C_lastname, COUNT (*) from Customers WHERE c_lastname like ' a% '"

But you can't achieve your purpose. The "Count" function here is actually an aggregate function, meaning to return only one line of information: Answer the question you asked. For the 1th SELECT statement, the question is "how many records are there in the Customer table?" "The query returns a single value as a response, so it cannot be combined with your regular queries." If you want to get other data, you need to use RecordCount.

Aggregate functions include AVG, MIN, Max, and sum, in addition to count.

12. Connection

Anyone familiar with SQL and relational databases has encountered a large number of connection types. In the simplest sense, a join (join) combines the contents of two tables into a single virtual table or recordset. If the data table is effectively regulated, you may often select specific information from one table and then select the associated information from another table. This requires a simple "equivalent connection (equijoin)".

To understand the actual connection operation, let's now assume that there are records of some kind of software stored in a database. A table (Software) contains the name of the SOFTWARE product, the version of the software, and other relevant details:

The other table (releases) stores information about the history of the software release, including the release date and publication status (such as beta, current, obsolete, and so on):

The table also contains a column that points to the ID number taken in the Software table. So, by the way you index the software table, you know that the software that software_id equals 2 in the publishing table is Rome.

You use a combination of connections so that you don't have to toss back and forth between the two tables. However, in addition to the combination of information, you can merge related information through a connection. In this way, as soon as the software_id in the publication table matches the IDs in the software table, you put the matching information together in a record.

The code is as follows:
SQL = "SELECT * from Software, releases where software.id = Releases.softwareid"

With a careful analysis of the above statements, first notice that two tables are listed behind from. Depending on the connection you are using, you may also find that the syntax changes (or the type of connection varies) in the future, but the above syntax is the most basic and shows how the data is combined. The where clause here is used to compare a specific ID value. In the Software table, an ID column exists. Similarly, there is a software_id column in thereleases table. To clarify the value you want to compare in the where clause, you prefix with the table name followed by a dot number (.).

The following is the result of the connection selection data:

Note: When creating a connection, you should carefully consider the column that selects the data. The above code uses the * wildcard character to keep the reader focused on other parts of the SELECT line of code. However, as you can see from the above figure, you cannot select the softwareid column because the column has no added value as part of the recordset. Its function is to use the WHERE clause.