Taught you how to use SQL statements in ASP [Author: Anonymous Copy from: Site author Hits: 1148 Update Time: 2004-4-6 article entry: ywdong]
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. 1. SELECT statement in the world of SQL, the most basic operation is the SELECT statement. When using SQL directly under the database tool, many people will be familiar with the following: SELECT WhatFROM Whichtablewhere Criteria will create a query that stores its results. And on the ASP page file, you can also use 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 to a variable: SQL = "SELECT What from Whichtable where criteria" , Understand the SQL "speaking" in the ASP, next, as soon as the method, as long as you meet your needs, the traditional SQL query model 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 write 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 the specific column, the code is as follows: SQL = "SELECT P_NAME FROM PROMTS" Execute the above query, the contents of the P_Name column will be selected. 2. Setting the query condition with WHERE clause Some time to 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 following WHERE clause: SQL = "SELECT P_NAME from Products where p_name like 'w%' "After the WHERE keyword follows with the conditions used to filter data, there is these conditions for these conditions, and only data that satisfies a certain standard will be queried. 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. When the WHERE clause basis starts to create a WHERE clause, the simplest way is to adopt standard comparison symbols, which are <, <=,>,> =, <> and =. Obviously, you will soon understand the meaning of the following code and the specific operation: SELECT * from products where p_price> = 199.95select * from products where p_price <> 19.95select * from products where p_version = '4' Note: Here you will Note that the numbers in the last example are added to the 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. The comparison operator comparison operator specifies the scope of the content 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. 3. Like, NOT LIKE and BETWEEN You have seen the LIKE usage in the example of the W header record above. 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 a 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 symbol represents 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 assumes that 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 the Between: ... where id betWeen 1 and 10 or you can also use mathematical judgment sentences that have been familiar with: ... WHERE ID> = 1 and ID> = 10 4. Our SQL statement we talk about so far Relatively simple, if you can then query by standard RECORDSET, 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.
For example in the following SQL statement: SQL = "SELECT c_firstname, c_lastname, c_email FROM customers WHERE c_email ISNOT NULL AND c_purchase = '1' OR c_purchase = '2' AND c_lastname LIKE'A% '" currently available on your SQL Knowledge, the above examples are not difficult to explain, but the above statement does not understand how to see how the conditional sentences are glued in a single SQL statement. When the multi-line statement does not understand the SQL statement, you may wish to break down 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 customers "SQL = SQL &" WHERE c_firstname LIKE 'a%' and c_emailaddress NOT NULL "SQL = SQL &" ORDER BY c_lastname, c_firstname "to the last one, SQL variable contains the following complete SELECT statement: "SELECT c_firstname, c_lastname, c_emailaddress, c_phone FROM customersWHERE c_firstname LIKE 'a%' and c_emailaddress NO NULL ORDER BY c_lastname, c_firstname" after decomposition according to the above sentence read more clearly better! 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. 5. Begin the implementation of 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. When you use non-SQL Recordset, creating a Recordset code is usually as follows: Dim objRecset Objrec = Server.createObject ("AdoDb.Recordset") Objrec.Open "Customers", Objconn, 0, 1, 2 If you compare ASP Familiar with the above code is not stranger, you should know "customers" means you open the name of a data table in the database. Open RecordSet To take advantage of your more familiar SQL skills, you need to adjust the most commonly used Recordset on regular ASP page: DIM objRecset Objrec = Server.createObject ("AdoDb.Recordset") Objrec.Open SQL, Objconn, 0, 1 2 The unique modification here is in Objrec.Open, after which the variable containing the SQL statement is later instead of 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).
