Common ASP database connection methods and techniques

Dim Conn, mdbfile

Mdbfile = server. mappath ("database name. mdb ")

Set conn = server. Createobject ("ADODB. Connection ")

Conn. Open "driver = {Microsoft Access Driver (*. mdb)}; uid = admin; Pwd = Database Password; DBQ =" & mdbfile

%>

2. ASP and SQL database connection:

<%

Dim Conn

Set conn = server. Createobject ("ADODB. Connection ")

Con. Open "provider = sqloledb; Data Source = SQL server name or IP address; uid = sa; Pwd = Database Password; database = Database Name

%>

Create a record set object:

Set rs = server. Createobject ("ADODB. recordset ")

Rs. Open SQL statement, Conn, 3, 2

3. Common SQL commands:

(1) Data Record Filtering:

SQL = "select * from data table where field name = Field Value Order by field name"

SQL = "select * from data table where field name like '% Field Value %' order by field name"

SQL = "select top 10 * from data table where field name order by field name"

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 condition expression"

SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value n where condition expression"

(3) Delete data records:

SQL = "delete from data table where condition expression"

SQL = "delete from data table" (delete all data table Records)

(4) add data records:

SQL = "insert into data table (Field 1, Field 2, Field 3 ...) Valuess (value 1, value 2, value 3 ...) "

SQL = "insert into target data table select * from source data table" (add records of source data table to target data table)

(5) statistical functions of data records:

AVG (field name) returns the average value of a table column

Count (* | field name) statistics on the number of data rows or the number of data rows with values in a column

Max (field name) obtains the maximum value of a table column.

Min (field name) obtains the minimum value of a table column.

Sum (field name) adds the values in the data column

The method for referencing the above functions:

SQL = "select sum (field name) as Alias from data table where condition expression"

Set rs = conn. excute (SQL)

Use RS ("alias") to obtain the calculation value. Use the same method for other functions.

(5) Create and delete 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)

(6) method of record set object:

Rs. movenext moves the record pointer down a row from the current position

Rs. moveprevious transfers the record pointer from the current position to a row up

Rs. movefirst move the record pointer to the first row of the data table

Rs. movelast moves the record pointer to the last row of the data table

Rs. absoluteposition = n move the record pointer to the nth row of the data table

Rs. absolutepage = n move the record pointer to the first row of page n

Rs. pagesize = N set N records per page

Rs. pagecount the total number of pages returned Based on pagesize settings

Rs. recordcount total number of returned records

Rs. bof indicates whether the record pointer exceeds the first end of the data table. True indicates yes, and false indicates no.

Rs. EOF indicates whether the returned record pointer exceeds the end Of the data table. True indicates yes, and false indicates no.

Rs. Delete deletes the current record, but the record pointer does not move down

Rs. addnew add record to end of data table

Rs. Update update data table records

Determines that the entered data is Numeric.

If not isnumeric (Request ("field name") then

Response. Write "not a number"

Else

Response. Write "Number"

End if

Database operations are frequently used. Including connection code, SQL commands, and so on, and never deliberately remember them (I am not willing to remember this stuff), so I often go to books and flip them. Some of the less-used databases may not be able to be found smoothly, so I will summarize them here for your reference. (Personal skills are limited. If you have any defects, please let us know .)

<1>. Database Connection method:

1. Access database DSN-less connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adoconn. Open "driver = {Microsoft Access Driver (*. mdb)}; DBQ = "&_

Server. mappath ("database path ")

2. Access ole db connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "provider = Microsoft. Jet. oledb.4.0 ;"&_

"Data Source =" & server. mappath ("database path ")

3. SQL server connection method:

Set adocon = server. Createobject ("ADODB. recordset ")

Adocon. Open "driver = {SQL Server}; server = (local); uid = ***; Pwd = ***;"&_

"Database = database name ;"

4. SQL Server OLE DB connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "provider = sqloledb.1; Data Source = ritant4 ;"&_

"User ID = ***; Password = ***;"&_

"In‑catalog = database name"

5. Oracle connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "driver = {Microsoft ODBC for Oracle}; server = oraclesever. World; uid = admin; Pwd = pass ;"

6. Oracle ole db connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "provider = oraoledb. Oracle; Data Source = dbname; user id = admin; Password = pass ;"

7. DBASE connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "driver = {Microsoft DBASE Driver (*. DBF)}; driverid = 277; DBQ = ------------;"

8. MySQL connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "driver = {MySQL}; database = yourdatabase; uid = username; Pwd = yourpassword; option = 16386 ;"

9. Visual FoxPro connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "driver = {Microsoft Visual FoxPro driver}; sourcetype = dBc; sourcedb = *. dBc; exclusive = no ;"

10. Ms text connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "driver = {Microsoft text Driver (*. txt; *. CSV)}; DBQ = -----;"&_

"Extensions = ASC, CSV, tab, txt; persist securityinfo = false ;"

11. Ms text ole db connection method:

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "provider = microsof. Jet. oledb.4.0; Data Source = your_path ;"&_

"Extended properties 'text; FMt = delimited '"

<2>. Four Common SQL commands:

1. query data records (select)

Syntax: Select field serial from table where field = content

Example: If you want to find all the records whose author is "cancer" from the book table, the SQL statement is as follows:

Select * from book where author = 'cancer'

"*" Is to retrieve all the fields in the book table. If the queried field value is a number, then the "content" after the query does not need to be enclosed by single quotation marks,

If it is a date, it is included in access (#), while in SQL Server,

For example:

Select * from book where id = 1

Select * from book where pub_date = #2002-1-7 # (ACCESS)

Select * from book where pub_date = '2014-1-7 '(SQL Server)

Tip:

The date function to_date is not a standard SQL document and is not applicable to all databases. Therefore, you should refer to the specific database syntax when using it.

In addition, if the input variable is queried, it is as follows:

Strau = request. Form ("author ")

Strsql = "select * from book where author = '" & strau &"'"

If the query is a number, then:

Intid = request. Form ("ID ")

Strsql = "select * from book where id =" & intid

In many databases, such as Oracle, the preceding statements can be written as follows:

Strsql = "select * from book where id = '" & intid.

However, the numeric type cannot be written in numeric format.

2. Insert)

Syntax: insert into table (field1, field2,...) values (value1, value2 ,....)

Example: Add a "cancer" record to the book table:

Insert into book (bookno, author, bookname) values ('cf001', 'cancer', 'cancer no-component upload ')

Similarly, the variables used are as follows:

Strno = request. Form ("bookno ")

Strau = request. Form ("author ")

Strname = request. Form ("bookname ")

Strsql = "insert into book (bookno, author, bookname) values ('" & strno & "', '" & strau & "', '" & strname &"')"

3. Use addnew of the recordset object to insert data:

Syntax:

Rs. addnew

RS ("field1"). value = value1

RS ("field2"). value = value2

...

Rs. Update

4. modify data records (update)

Syntax: Update table set field1 = value1, field2 = value2,... where fieldx = valuex

Example: Update book set author = 'babycrazy 'Where bookno = 'cf001'

The variables are as follows:

Strno = request. Form ("bookno ")

Strau = request. Form ("author ")

Strsql = "Update book set author = '" & strau & "'where bookno ='" & strno "'"

5. Update method of the recordset object:

Syntax:

RS ("field1"). value = value1

RS ("field2"). value = value2

...

Rs. Update

Note: When syntax 3 and syntax 5 are used, note that the field type (especially the date type) is the same, otherwise the error probability is very high.

Example:

Strno = request. Form ("bookno ")

Strau = request. Form ("author ")

Set adocon = server. Createobject ("ADODB. Connection ")

Adocon. Open "driver = {Microsoft Access Driver (*. mdb)}; DBQ = "&_

Server. mappath = ("/cancer. mdb ")

Strsql = "select * from book where bookno = '" & strno &"'"

Set rs = server. Createobject ("ADODB. recordset ")

Rs. Open strsql, adconn, 1, 3

If not Rs. EOF then 'if this record exists

RS ("author"). value = strau

Rs. Update

End if

Rs. Close

Set rs = nothing

Adocon. Close

Set adocon = nothing

6. delete a record)

Syntax: Delete table where field = Value

Example: delete a cancer record from the book table.

Delete book where author = 'cancer'

(Note: if there are multiple records whose author field value is cancer in the book table, all records whose author is cancer will be deleted)