Essence: Complete explanation of all kinds of database ASP syntax summary

Essence | data | database | grammar

< a >. How the database is connected:

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 >. The four commonly used SQL commands:

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 in the Book table, such as the query's field value is a number, then the "content" will not need to add single quotes,

As a date, it is included in Access (#) and included in SQL Server ('), 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 a standard SQL, not all databases apply, so you need to refer to the database when using the specific

Grammar

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)

Syntax: 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)

Syntax: 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)
Syntax: 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)