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)