11 methods for connecting to the database through ASP

Edit Note: After learning these things, you can perform all ASP operations on the database independently.

11 methods for connecting to a database using ASP -- This article summarizes the methods for connecting to various databases using ASP:

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 used to retrieve all the fields in the book table. If the queried field value is a number, no single quotation marks are required for the subsequent "content". If it is a date, in access, (#) is used, while in SQL Server, (') includes,

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 uploadProgram')

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)

Well, I learned how to use these operations. When you use ASP to operate databases, there is no problem.