Very useful learning of commonly used codes for ASP page 2/2

11. Use the hidden type to pass variables.
 < % form method="post" action="" > 
< % for each item in  % > 
< input namee="< %=item% >" type="HIDDEN" 
value="< %=((item)) % >" > 
< % next % > 
< /form > 

12. Organize commonly used SQL statements.

(1) Data record filtering:

sql="select * from Data table where field name=field value order by field name [desc]"

sql="select * from Data table where field name like ''%field value%'' order by Field name [desc]"

sql="select top 10 * from Data table where field name order by field name [desc]”

sql="select * from Data table where field name in ('value 1'',''value 2'',''value 3'')"

sql="select * from Data table where field name between value 1 and value 2”

(2) Update data records:

sql="update Data table set Field name=field value where conditional expression”

sql="update Data table set Field 1=value 1, Field 2=value 2… Field n=value n where conditional expression"

(3) Delete data records:

sql="delete from Data table where conditional expression"

sql="delete from Data table" (Delete all records in the data table)

(4) Add data records:

sql="insert into Data table (field 1, field 2, field 3…) values (value 1, value 2, value 3…)”

sql="insert into target data table select * from source data table" (Add the record of the source data table to the target data table)

(5) Data record statistics function:

AVG (field name) gets the average value of a table column
COUNT(*|field name) Statistics on the number of data rows or statistics on the number of data rows with values ​​in a certain column
MAX (field name) Get the maximum value of a table column
MIN (field name) Get the smallest value of a table column
SUM (field name) adds the values ​​of the data column

Methods for citing the above functions:

sql="select sum(field name) as alias from data table where conditional expression"
set rs=(sql) 

Use rs("alias") to obtain the statistical calculation value, and other functions are used the same as above.

(5) Establishment and deletion of data tables:

CREATE TABLE Data table name (field 1, type 1 (length), field 2, type 2 (length)…)

Example: CREATE TABLE tab01(name varchar(50), datetime default now())

DROP TABLE Data table name (permanently delete a data table)