The five-flowers of SQL products have to live, perhaps you don't have to open your sleeves. But if you want to use ASP and SQL, you may be dizzy. MySQL, SQL Server and MSQL are excellent SQL tools, but unfortunately, in the ASP environment you can't use them to create a practical SQL statement. However, you can use the Access knowledge and the corresponding Access skills, plus our tips and techniques, I believe you can successfully add SQL in your ASP page.
In the world of SQL, the most basic operation is the SELECT statement. Many people will be familiar with the following operations directly under the database tool:
Select What
From whichtable
WHERE CRITERIA
Executing the above statement will create a query that stores its results.
On the ASP page file, you can also adopt the above general grammar, but the situation is slightly different, when the ASP programming, the content of the SELECT statement is derived as a string:
SQL = "SELECT What from Whichtable Where Criteria"
Ok, I understand the way the SQL "talk" under the ASP, so that you can make your needs, the traditional SQL query mode and conditional query can be used.
For example, you may wish to assume that there is a data table in your database, the name is Products, now you want to take out all the records in this table. Then you have written the following code:
SQL = "SELECT * from Products"
The above code - the role of the SQL statement is to take all the data in the table - all records within the data table will be selected. However, if you just want to take a particular column from the table, such as p_name. Then you can't use * wildcard, you have to type the name of a particular column, the code is as follows:
SQL = "SELECT P_NAME FROM PRODUCTS"
After performing the above query, the contents of the P_Name column will be selected.
Sometimes you take out all database records may just meet your requirements, but in most cases we usually just get some records. How do I design inquiry? Of course, it will be more brainstorming, let alone this article also surprised you to use the Robs of the Raushzi.
For example, if you only plan to take out the p_name record, and these records must be headed in letter W, then you will use the WHERE clause:
SQL = "SELECT P_NAME FROM Products Where P_name Like 'W%'"
The after the WHERE keyword is followed by the conditions used to filter data. With these conditions, it will only be queried with data that satisfy certain standards. In the above example, the results of the query will only get the name of the name in the W header.
In the above example, the meaning of the percent symbol (%) is that the query returns all W letters and back is the record entry for any data or no data. So, when executing the above query, West and Willow will be selected from the Products table and store it in the query.
Just as you can see, just carefully design the SELECT statement, you can limit the amount of information returned in the Recordset, and ponder how to meet your requirements.
These, but it is just that SQL use is just starting. To help you gradually master the complex SELECT statement usage, let's take a look at the key standard terms: compare operators, these stations are often used when building their SELECT strings to get specific data. WHERE clause foundation
When you start creating a WHERE clause, the easiest way is to adopt a standard comparison symbol, which is <, <=,>,> =, <> and =. Obviously, you will soon understand the meaning of the following code and the specific operation:
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 numbers in the last example are added with single quotes. The reason is that the '4' in this example is a text type rather than a digital type. Because you will put the SELECT statement in the quotation mark to assign it to the variable as a value, you can also use quotation marks in the statement.
Compare operator
The comparison operator specifies the scope of the data from the table. You can use them to create filters to narrow the range of Recordset, which prompted to save only information you care about when you have given a given task.
You have seen the LIKE usage in the example of remaining the W header record. The Like decision is a very useful symbol. However, in many cases it uses it to bring you too much data, so it is best to open your mind before you use it. Suppose you want to take out the 5-digit SKU number, and the beginning is 1 end is 5, then you can use the underman (_) instead of% symbol:
SQL = "SELECT * from Products where p_sku like '1___5'"
The underscription indicates any character. So in the case of entering "1 _ _ _ 5", your search is limited to the 5-digit range that meets the specific mode.
If you want to be against it, you have to find out the SKU entry that does not match the "1_ _ _ 5" mode. Then you only need to add NOT in front of the LIKE in the examples just now.
Between
Suppose you want to take out some range of data, and you know the starting point and end point in advance, then you may wish to use BetWeen judgment. Let us now assume that you want to select the records between 1 and 10 in a given table. You can use BetWeen as follows:
... WHERE ID BETWEEN 1 and 10
Or you can also use the familiar mathematical judgment sentence:
... WHERE ID> = 1 and ID> = 10
We have relatively simple so far, and if you can reach the standard RECORDSET loop query, then these statements can also meet some more complex requirements. However, why bother to be on the basics of the shallow taste? You can add additional symbols, such as And, OR, and NOT to complete more powerful features.
The following SQL statement is:
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 have now, the above examples are not difficult to explain, but the above statement does not understand how to see how the conditional sentence is glued in a single SQL statement. Multi-line statement
In the case where the SQL statement is not understood, you may wish to decompose the entire statement into multi-line code, and then gradually increase the components of the query statement on the basis of existing variables and exist in the same variable:
SQL = "SELECT C_FIRSTNAME, C_LASTNAME, C_EMAILADDRESS, C_PHONE"
SQL = SQL & "from customer"
SQL = SQL & "WHERE C_FIRSTNAME LIKE 'A%' and C_Emailaddress Not Null"
SQL = SQL & "ORDER BY C_LASTNAME, C_Firstname"
At the last sentence, the SQL variable contains the following full SELECT statement:
"SELECT C_FIRSTNAME, C_LASTNAME, C_EMAILADDRESS, C_PHONE FROMERS
WHERE C_FIRSTNAME LIKE 'a%' and c_emailaddress no null order by c_lastname,
C_firstname
After the whole sentence is broken down, it is obviously more read! When debugging, you may be more pleasant to more than a few characters to make the program better read. But you have to remember, you need to increase space before closing quotes or after opening quotes, so that you don't have a few words when you connect.
After learning the construction and use of the SELECT statement, you should learn how to use it. Under the database tool you have, this may mean you have to press some buttons written by "execution". On the ASP page, you can perform SQL statements immediately or as a stored procedure call.
Once you have created a SQL statement, you have to try to access its query results. Obviously, the key here is ASP Recordset. Creating a Recordset code is usually as follows: Using non-SQL RECORDSET:
DIM ObjRec
Set objrec = server.createObject ("adoDb.recordset")
Objrec.open "Customers", Objconn, 0, 1, 2
If you are more familiar with the ASP, you can not be strange to you, you should know "customers" means that you open the name of a data table in the database.
Open Recordset
In order to make full use of your more familiar SQL skills, you need to adjust the most commonly used Recordset on the General ASP page:
DIM ObjRec
Set objrec = server.createObject ("adoDb.recordset")
Objrec.open SQL, Objconn, 0, 1, 2
The only modification here is in Objrec.Open, after which the variable containing the SQL statement is later replaced by the name of the data table to query.
One of this method is that you can specify a cursor type (as shown above 0, 1, 2).
Execute SQL
You can perform a SQL statement with a compact line of code to create a Recordset. The following is the syntax:
DIM ObjRec
Set objrec = Objconn.execute (SQL)
In the above example, the SQL you see is the variable you store your own SQL SELECT statement. The code line "run" SQL statement (or query the database), select the data and store the data in the Recordset, in the above example is the variable ObjRec. The main disadvantage of this method is that you can't choose the type of cursor you want to use. Instead, Recordset always opens forward to the cursor. Because of the cursor, you may intend to be familiar with two ways to create a Recordset. Direct execution query saves the time consumed by typing characters, but if you have the default cursor, it is possible to encounter a problem that you often have normal operation. No matter which method you specifically use, the biggest difference between the two is nothing more about code. Don't consider what fields do you have, what is your standard? Regardless of how you store data, use SQL RECORDSET in volume than the standard Recordset that is opened on the ASP, not to mention Easy. After all, by filtering data, you eliminate the time IF-THEN test and the possible loop.
Write tests SQL
There is a trick here, many professional ASP programmers are used to "writing" their own SQL statements when testing the web page. Do this helps you debug code because you can see the string that passes to the server. And what you have to do is adding response.writeYourvariable to display the information on the screen. You should attach this information when you submit questions related to SQL to the ASP discussion group.
Use SQL statements in ASP: Storage Inquiry
When your query is relatively simple, every time you start creating a SQL statement from scratch, it's different, but the complex queries are different, and you will have a lot of development errors from the head. Therefore, once you let SQL run smoothly, you'd better save them, call them again when needed. This, even if you are a simple query, you can use the query statement stored at any time.
Suppose you have a report to the team every week, pointing out the current business support issues, these data need to be selected from your database, and to select records according to the date, simultaneous sorting based on the support issues you used by your team . Once you have designed this inquiry, why have you repeated once a week? Don't create a query on your HTML page, you should use your database tool to create queries and save it. Then you can use the ActiveCommand property to insert queries into your ASP page. One or two times you may feel that there is no meaning, in fact, 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 adcmdStoredProc indicates that you have already included an adovbs.inc file on the page. This file defines an Access constant that you can access by name instead of numbers. Just need to contain the file on the page?! - # include ->), then you can use the adcmdstoredProc this name. This way, what is easier to understand when you see it again, what is the meaning of the above stored queries.
Use SQL statements in ASP: Random
Establishing a randomly generated HTML code is a fairly easy implementation ASP feature. You may have created "one post per day", scrolling ads, etc., just need to fettle, will make your website for a long time.
For data stored in the database, random number features give the above effects, but they may be too slow. You can't ask the ASP "to find a random number" and then print it out. In fact, the common solution is to establish a loop as follows: Randomize
RNumber = int (RND * 499) 1
While Not Objrec.eof
IF Objrec ("ID") = rnumber then
... Here is the execution script ...
END IF
ObjRec.movenext
Wend
This is easy to understand. First, you take a random number within 1 to 500 (assuming 500 is the total number of records within the database). Then, you traverse each record to test the value of the ID, check if it matches the rnumber. If you meet the conditions, you will execute the piece of code starting by the THEN keyword. If your RNumber is equal to 495, then you can loop the time of the data library flower. Although 500 this figure looks great, it is still a small database compared to a more stable business solution, and the latter usually contains thousands of records in a database. Isn't it died at this time?
With SQL, you can quickly find accurate records and open a Recordset containing the record, as shown below:
Randomize
RNumber = INT (RND * 499) 1
SQL = "SELECT * from customers where id =" & rnumber
Set objrec = Objconn.execute (SQL)
Response.writernumber & "=" & objrec ("id") & "& objrec (" c_email ")
You don't have to write RNumber and ID, you only need to check the matching situation. As long as you are satisfied with the above code, you can operate "random" records on demand. Recordset does not contain other content, so you can quickly find the record you need, greatly reduced processing time.
Talk about random
Now you are determined to squeeze the last drop of the Random function, then you may take a number of random records at a time or want to use a certain random range. The above standard Random sample can be used to use SQL to deal with both cases.
In order to take out several random selection records and store them in the same Recordset, you can store three random numbers, then query the database to get records that match these numbers:
SQL = "Select * from customers where id =" & rnumber & "ory =" & rnumber2 & "ory =" & rnumber3
If you want to select 10 records (perhaps a list of 10 links per page), you can select the first record and appropriate quantity increment records with BetWeen or Mathematical equation. This operation can be done in several ways, but the SELECT statement only shows a possibility (here the ID is the automatically generated number):
SQL = "SELECT * from Customers where id Between" & rnumber & "and" & rnumber & " 9"
Note: The implementation of the above code is not to check if there are 9 concurrent records in the database. If you need to guarantee that 10 records each time you have elected, then you must further design inquiry. Use SQL statements in ASP: Form operation
Removing information from a page form is a common problem in ASP programming. However, how long does it take to traverse the record passing through a form? This depends on the size of the database. Simple GUI interfaces may make the loop traversal operation cost too much time.
For example, suppose there is a team member logs in to the GUI screen to enter your own name and the name between the name: Amy.cowen. This value is submitted by form, and her current project list is taken from the database and displayed on the screen. In order to quickly remove the user's record to display on the screen, you can write the following code.
Suppose the following code is included on the HTML page:
