Example to explain the use of SQL statements in ASP Dynamic Web page making

sql| News | web | statement

In the SQL world, the most fundamental operation is the SELECT statement. When SQL is used directly under Database Tools, many people are.

　　Using SQL statements in ASP 1: Select query

Be familiar with the following actions:

SELECT what
From whichtable
WHERE CNWEBJX

Executing the above statement creates a query that holds its results.

In the ASP page file, you can also use the above general syntax, but the situation is slightly different, ASP programming, the contents of the SELECT statement to be assigned as a string to a variable:

SQL = "Select what from whichtable WHERE cnwebjx"

OK, understand the ASP under the SQL "speak" way, then follow the same pattern, as long as you meet your needs, the traditional SQL query mode and conditional query can be useful.

For example, suppose you have a data table in your database, the name is products, and now you want to take out all the records in the list. Then you write the following code:

SQL = "SELECT * FROM Products"

The function of the above code--SQL statement is to take out all the data in the table--after execution, all the records in the datasheet 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, the code is as follows:

SQL = "Select P_name from Products"

After executing the above query, the contents of the Products table and the P_name column will be all selected.

Using SQL statements in ASP 2: Setting query conditions with a WHERE clause

Sometimes taking out all of the database records may be exactly what you want, but in most cases we usually just have to get some records. So how do you design the query? Of course it will be a bit more brain, and this article also deliberately do not want to let you use that what them recordset.

For example, if you're only going to take out P_name records, and the names of those records must start with the letter W, you'll need to use the following WHERE clause:

SQL = "Select P_name from the products WHERE p_name like ' w% '"

Where keywords are followed by the conditions used to filter the data, and with the help of these conditions, only data that satisfies a certain standard will be queried. In the above example, the result of the query will only get the P_name record with the first name in W.

In the example above, the percent sign (%) means that the query returns a record entry that starts with all the W letters and is followed by any data or even no data. Therefore, in the execution of the above query, West and Willow will be selected from the Products table and stored in the query.

As you can see, as long as you carefully design the SELECT statement, you will be able to limit the amount of information returned in the recordset, and think about how it always satisfies your requirements.

These are just the beginning of mastering the SQL use. To help you learn more about the use of complex SELECT statements, let's take a look at the key standard terms: comparison operators, which are often used when you build your own select string to get specific data.

WHERE clause basics

The simplest way to start creating a WHERE clause is to use the standard comparison notation, which is <, <=, >, >=, <>, and =. Obviously, you will soon be able to understand the meaning of the following code and the specific results of the operation:

SELECT * FROM Products WHERE P_price >= 199.95
SELECT * FROM Products WHERE P_price <> 19.95
SELECT * from the 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 literal type, not a numeric type. Because you put the SELECT statement in quotes to assign it as a value to the variable, you can also use quotes in the statement.

Comparison operators

The comparison operator specifies the range of content from which the data is fetched. You can use them to create filters to narrow the recordset so that it saves only the information you care about under a given task.

Use SQL statements in ASP 3:like, not like and BETWEEN

You've seen the use of like in the example above where you've taken the W-heading record. The like decision word is a very useful symbol. However, in many cases it may give you too much data, so it's a good idea to think about what data you want to get before you use it. Suppose you want to take out a 5-digit SKU number, and it starts with 1 at the end of 5, you can use the caret (_) instead of the% symbol:

SQL = "SELECT * WHERE p_sku like ' 1___5 '"

The following stroke denotes any one character. So in the case of the input "1 _ _ _ 5", your search will be limited to the 5-digit range that meets the specific pattern.

If you want to do the opposite, find all SKU entries that do not match the "1_ _ 5" mode. Then you just need to add not to the like in the example in the statement just now.

BETWEEN

If you want to take out a range of data, and you know the starting and ending points of the range, then you might as well use a between judgment word. Now let's assume that you want to select a record within a given table that ranges between 1 and 10. You can use between as follows:

... WHERE ID BETWEEN 1 and 10

Or you can use the already familiar mathematical judgment words:

... WHERE ID >= 1 and ID >= 10

Using SQL statements in ASP 4: Federated statements

The SQL statements we've talked about so far are relatively simple, and if you can go through a standard recordset loop query, these statements can also meet some more complex requirements. But why do we have to stick to the basic standards of taste? You can add additional symbols, such as and, or, and not to accomplish 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% '.

As for your current knowledge of SQL, the above examples are not difficult to explain, but the above statement does not make it very clear to see how the conditional words are glued to a single SQL statement.

Multi-line statement

When the SQL statement is not understood, you might as well break the whole statement into multiple lines of code, and then incrementally add the parts of the query statement to the same variable based on the existing variables:

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"

In 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 "

The whole sentence according to the decomposition after the obvious good read much! When you are debugging, you may be more willing to knock a few more characters to change the program to read better. Keep in mind, however, that you need to add more space before closing the quotes or after opening the quotes so that you don't get a few words together when the strings are connected.

Using SQL statements in ASP 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.

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?