How to use SQL statements in ASP

There are so many SQL products that you may have to worry about. However, if you want to use ASP and SQL at the same time, you may feel dizzy. MySQL, SQL Server, and msql are both excellent SQL tools. Unfortunately, in the ASP environment, you do not need them to create practical SQL statements. However, you can use your access knowledge and corresponding access skills, coupled with our tips and skills, I believe you will be able to successfully add SQL to your asp web page.

1. SELECT statement

In the world of SQL, the most basic operation is the SELECT statement. When using SQL directly using database tools, many people will be familiar with the following operations:
Select what
From whichtable
Where criteria

Execute the preceding statement to create a query that stores the result.

On the ASP page file, you can also use the above general syntax, but the situation is slightly different. during ASP programming, the content of the SELECT statement should be assigned to a variable as a string:
SQL = "select what from whichtable where criteria"

Now, I understand the way SQL is "spoken" in ASP. The next step is the method. As long as you meet your needs, the traditional SQL query mode and conditional query can be used.

For example, assume that your database has a data table named products. Now you want to retrieve all the records in this table. Then you write the following code:
SQL = "select * from products"

The preceding code -- SQL statement is used to retrieve all the data in the table. After execution, all records in the data table are selected. However, if you only want to retrieve a specific column from the table, such as p_name. Then you cannot use the * wildcard. enter the name of a specific column. The Code is as follows:
SQL = "select p_name from products"

After the above query is executed, all content in the p_name column in the products table will be selected.

2. Use the WHERE clause to set query Conditions

Sometimes retrieving all the database records may meet your requirements. However, in most cases, we only need to obtain some records. In this way, how can we design a query? Of course, it will be a little more time-consuming. Besides, this article does not want you to use the recordset.

For example, if you only want to retrieve the p_name record, and the names of these records must start with a letter W, you need to use the following where clause:
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 criteria can be queried. In the preceding example, only the p_name record with the name of W is obtained.

In the preceding example, the percent sign (%) indicates that the query returns all records with W letters headers and any data or even no data. Therefore, when executing the preceding query, west and Willow will be selected from the products table and stored in the query.

As you can see, by carefully designing select statements, you can limit the amount of information returned in recordset.

These are just the first steps to grasp the use of SQL. To help you gradually understand 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 string to obtain specific data.

Where clause Basics
When creating a where clause, the simplest method is to use standard comparison symbols, which are <, <=,>, >=, <>, and =. Obviously, you will soon be able to understand the meaning and specific running 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: here you will notice that the number 4 in the last example is enclosed in single quotes. The reason is that '4' in this example is the text type rather than the number type. Because you will put the SELECT statement in quotation marks and assign it to the variable as a value, you can also use quotation marks in the statement.

Comparison Operators
The comparison operator specifies the content range of the data retrieved from the table. You can use them to create filters to narrow the scope of the recordset, so that they only store the information you are concerned about under a given task.

3. Like, not like, and

You have seen the like usage in the example of getting the W-header record above. Like is a very useful symbol. However, in many cases, using it may bring you too much data, so before using it, you 'd better start thinking about what data you want. Assume that you want to retrieve the SKU number with five digits and start with "1" and end with "5", you can use the underscore (_) to replace the "%" symbol:
SQL = "select * from products where p_sku like '1 ___ 5 '"

The delimiter represents any character. Therefore, when "1 _ 5" is entered, your search will be limited to a 5-digit range that meets the specific mode.

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

Between
If you want to retrieve data within a certain range, and you know the start and end of the range in advance, you may wish to use between to determine the word. Now let's assume that you want to select a record with a range of 1 and 10 in a given table. You can use between as follows:
... Where ID between 1 and 10

Or you can use the familiar mathematical judgment words:
... Where ID> = 1 and ID> = 10

4. Union statement

The SQL statements we have mentioned so far are relatively simple. If we can use standard recordset for loop query, these statements can also meet more complex requirements. However, why do we have to stick to the basic level of simplicity? You can add other symbols, such as and, or, and not, to complete more powerful functions.

The following SQL statement is used 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 far as your current knowledge of SQL is concerned, the above examples are not difficult to explain, but the above statements do not clearly show you how condition statements are glued into a single SQL statement.

Multi-line statements
When SQL statements are hard to understand, you may want to break down the entire statement into multiple lines of code, and then gradually add the components of the query statement based on the existing variables and store them in the same variable:
SQL = "select c_firstname, c_lastname, c_emailaddress, c_phone"
SQL = SQL & "from MERs"
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 MERs
Where c_firstname like 'a % 'and c_emailaddress no null order by c_lastname,
C_firstname"

The entire sentence is obviously much read after being decomposed! During debugging, you may be more comfortable typing a few more characters to better read the program. However, you must remember that you need to add spaces before or after the quotation marks are closed to ensure that the strings are not joined together.
5. Start execution

After learning the construction and usage of the SELECT statement, you should learn how to use it. Under the database tool you have mastered, this may mean that you have to press a button that says "execute. On the ASP Webpage, you can immediately execute SQL statements or call them as stored procedures.

Once an SQL statement is created, you have to try to access its query results. Obviously, the key here is ASP recordset. When a non-SQL recordset is used, the code for creating a recordset is usually as follows:
Dim objrec
Set objrec = server. Createobject ("ADODB. recordset ")
Objrec. Open "MERs", objconn, 0, 1, 2

If you are familiar with ASP, the above Code is no stranger to you. You should know that "MERs" indicates the name of a data table in the database.

Open recordset
To make full use of the SQL skills you are more familiar with, you need to adjust the recordset most commonly used on 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, and then replace the name of the data table to be queried with the variable containing the SQL statement.

One of the advantages of this method is that you can specify the cursor type (as shown in the preceding 0, 1, 2 ).

Execute SQL
You can use a compact line of code to execute SQL statements to create A recordset. Syntax:
Dim objrec
Set objrec = objconn. Execute (SQL)

In the preceding example, the SQL statement you see is the variable where you store your SQL SELECT statement. The code line "runs" an SQL statement (or queries the database), selects the data and stores the data in the recordset. In the preceding example, the variable objrec is used. The main disadvantage of this method is that you cannot select the cursor type you want. On the contrary, recordset always opens with a forward cursor.

Because of the cursor, you may be familiar with the two methods for creating recordset. Directly executing a query saves the time required for typing characters, but you have to use the default cursor. This may cause frequent failures. No matter which method you adopt, the biggest difference between the two is no more than whether the code is refined or not. Without considering what fields you obtain and what your standards are, no matter how much data you store, SQL-based recordset is much smaller in size than the standard recordset opened on ASP, let alone the simplicity of the operation. After all, by filtering data, you eliminate the time-consuming if-then test and the loops that may be used.

Write test SQL
Here is a tip. Many professional ASP programmers are used to "writing" their own SQL statements when testing web pages. This can help you debug the code, because you can see the strings passed to the server for execution. What you need to do is to add response. writeyourvariable to display relevant information on the screen. This information should be attached when you submit SQL-related questions to the ASP discussion group.

6. Storage Query

When your query is relatively simple, it does not take much time to create an SQL statement from the beginning. However, complicated queries are different. Every time you create an SQL statement from the beginning, many development errors will occur. Therefore, once the SQL statements run smoothly, you 'd better save them and call them as needed. In this way, even for a simple query, you can use the stored query statement at any time.

Assume that you have to submit a report to the team every week to point out the existing business support problems. The data needs to be selected from your database and recorded by date, at the same time, it is sorted by the categories of support questions adopted by your team. Once you have designed this query, why do you write it again every week? Do not create a query on your HTML page. You should use your database tool to create a query and save it.

Then you can use the activecommand attribute to insert queries to your ASP Webpage. The first one or two times you may feel boring, but there are just a few lines of code:
Set objsq = server. Createobject ("ADODB. Command ")
Objsq. activeconnection = "databasename"

Objsq. commandtext = "storedqueryname"
Objsq. commandtype = adw.storedproc

Set objrec = objsq. Execute

Note that using adcmdstoredproc indicates that you have included the adovbs. inc file on the page. This file defines the access constant that you can access by name rather than number. You only need to include this file on the page (<! -- # Include -->), and then you can use names such as adcmdstoredproc. In this way, you will be more likely to understand the meaning of the stored query in the future.

7. Order

Selecting records from an Access database is the most frustrating thing. They are input to the database in the order they are entered. Even if you use sort by in the access environment to change the record view, the record order in the data table has not changed.

If you are using asprecordset to write records on the web page, you may know that the order of disorder is so painful. However, you may have to face this problem frequently, because there is no simple and convenient solution. Fortunately, order by can simplify this problem.

To sort your results, you only need to add order by at the end of the SELECT statement and specify the reference columns to be sorted. Therefore, if you want to sort the MERs 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 to write the results to the screen, you will see the data in alphabetical order.

Multi-level sorting
In fact, it is not only possible to perform level-1 sorting in SQL statements. In fact, in many cases, you may want to specify two to three levels of deep data sorting. Assume that you have the following data tables:

Previous single-levelOrderSort the data in the following order:
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

ApparentlyOrderIt plays an appropriate role. In the actual table structure, absurdly assured is the last entry, but it is placed at the top of the search result. Hands on records are listed last because o is listed at the end of the alphabet. Obviously, absolutely is better placed before absurdly according to the alphabet. To do this, you need to take level 2ndOrderSort by column:
SQL = "select c_lastname, c_firstname, c_email from customers order
C_lastname, c_firstname"

The result will first followC_lastnameSort the columns and then followC_firstnameColumn sorting. If your data table contains a large number of records, designing and sorting carefully will make the output result arrangement more reasonable.

Put into use
If you like most programmers to write code by yourself, indulge in the craze for new technologies. Why don't I try SQL encoding from the lengthy ASP encoding? Next we will discuss the common problems of ASP programming and how to efficiently use SQL statements in ASP.

11. Record statistics

It is not difficult to determine how many records exist in the database or determine how many records meet certain standards. If you use the correct cursor type, you can useRecordcountYou can also use recordset to obtain the number of records. However, there is a simpler method, which is in its ownSelectStatementCount (*)The Code is as follows:
SQL = "select count (*) from MERs"

Or
SQL = "select count (*) from MERs 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 MERs where c_lastname like 'a % '"

However, you cannot achieve your goals. Here, the "count" function is actually a set function, meaning that only the single row information is returned: To answer your question. For the 1stSelectThe 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 query. If you want to get other data, you need to useRecordcount.

In addition to "count", Aggregate functions include AVG, Min, Max, and sum.

12. Connection

Anyone familiar with SQL and relational databases has met many connection types. In short, join combines the content of two tables into a virtual table or recordset. If the data table is effectively normalized, you may often select specific information from a table and then select association information from another table. To do this, you need a simple "equijoin )".

To understand the actual connection operations, let's assume that some software records are stored in the memory of a database. A table (SoftwareIncluding the software product name, software version, and other relevant details:

Another table (ReleasesIt stores information about the software release history, including the release date and status (such as the test version, current version, and outdated version ):

 

The table above also contains a column that points to the ID number used in the software table. Therefore, using this Index Software Table method, you will know that the publishing tableSoftware_idThe software equal to 2 is Rome.

You use the join combination information, so you do not need to go back and forth between two tables. However, in addition to combining information, you can also combine relevant information through connections. In this way, as long asSoftware_idMatch the ID in the software table, and you put the matching information together in a record.

The Code is as follows:
SQL = "select * from software, releases where software. ID = releases. softwareid"

After carefully analyzing the preceding statements, we first notice that the two tables are listed inFrom. Based on the connections used, you may also find that the syntax will change (or the connection type will change) in the future, but the above syntax is the most basic, displays how data is selected. HereWhereClause is used to compare specific ID values. InSoftwareThe table exists.IDColumn. Similarly,ReleasesThere isSoftware_idColumn. To make it clear that you areWhereThe value to be compared in the clause. You use the table name as the prefix, followed by a dot (.).

The following is the result after the selected data is connected:

Note:When creating a connection, you must carefully consider selecting columns for data. The above Code uses the * wildcard to attract the reader's attentionSelectOther parts of the code line. However, as you can see, you cannot selectSoftwareidColumn because this column is not added to the recordset part. Its function isWhereClause.