11 Methods of ASP connection database and related syntax

Connection Database | syntax

　　Edit Note: Learn these things, you will basically be able to complete the ASP on the database of all operations independently.

ASP Connection Database 11 ways--This article summarizes the use of ASP linked to a variety of database methods:

　　1.Access Database Dsn-less Connection method:

Set Adocon=server.createobject ("Adodb.connection")

Adoconn. Open driver={microsoft Access Driver (*.mdb)};D bq= "& _

Server.MapPath ("Path to Database")

　　2.Access OLE db connection method:

Set Adocon=server.createobject ("Adodb.connection")

Adocon.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data source=" & Server.MapPath ("Path to Database")

　　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=***; " & _

"Inital 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)};d riverid=277;dbq=------------;"

　　8.mySQL Connection method:

Set Adocon=server.createobject ("Adodb.connection")

Adocon.open "Driver={mysql};d atabase=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)};d bq=-----;" &_

"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 ' "

　　< two > commonly used SQL commands in four types:

　　1. Query data records (SELECT)

Syntax: Select field serial from table Where field = Content

Example: To find all the records of the author as "Cancer" from the Book table, the SQL statement reads as follows:

SELECT * FROM book where author= ' cancer '

"*" is to remove all the fields from the Book table, such as the query's field value is a number, then the "content" will not need to add single quotes, as the date, in Access in (#) included, and in SQL Server (') is included,

Such as:

SELECT * FROM book where id=1

SELECT * FROM book where pub_date= #2002 -1-7# (Access)

SELECT * FROM book where pub_date= ' 2002-1-7 ' (SQL Server)

Tips:

Date function to_date is not standard SQL, not all databases are applicable, so you should refer to the database syntax when using

In addition, if you are querying incoming variables, the following are:

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 above statements can be written as:

Strsql= "SELECT * from book where id= '" &intID& ""

But character types must not be written in digital format, and need to be noted.

　　2. Add record (Insert)

Grammar:

Insert into table (Field1,field2,....) Values (value1,value2,....)

Example: Add an author is a "cancer" record in Book table:

Insert into book (Bookno,author,bookname) VALUES (' CF001 ', ' cancer ', ' Cancer no component Upload program ')

Similarly, if you use a variable 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. Methods of inserting data using the AddNew of a Recordset object:

Grammar:

Rs.addnew

RS ("Field1"). value=value1

RS ("Field2"). Value=value2

...

Rs.update

　　4. Modify data records (Update)

Grammar:

Update table set field1=value1,field2=value2,... where Fieldx=valuex

Example:

Update book set author= ' Babycrazy ' where bookno= ' CF001 '

If you use a variable as follows:

Strno=request.form ("Bookno")

Strau=request.form ("author")

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

　　The Update method for the 5.Recordset object:

Grammar:

RS ("Field1"). value=value1

RS ("Field2"). Value=value2

...

Rs.update

Note: When using Syntax 3 and syntax 5, be sure to note that the type of field (especially the date type) is consistent, otherwise the chances of error are very high.

Example:

Strno=request.form ("Bookno")

Strau=request.form ("author")

Set Adocon=server.createobject ("Adodb.connection")

Adocon.open "Driver={microsoft Access Driver (*.mdb)};D bq=" & _

Server.mappath= ("/cancer/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 you have this record

RS ("author"). Value=strau

Rs.update

End If

Rs.close

Set rs=nothing

Adocon.close

Set adocon=nothing

　　6. Delete a record (delete)

Grammar:

Delete table where Field=value

Example: Delete Book table in which the author is a cancer record

Delete book where author= ' cancer '

(Note: If there are more than one record for the author field in the Book table cancer, all records author to Cancer will be deleted)

Well, learned to use these operations, everyone in the ASP to operate the database, it is no problem.