Summary of the syntaxes for connecting ASP to 11 Databases

Database operations are frequently used. Including connections Code SQL commands and so on, and never deliberately remember them (I am not willing to remember this stuff), so I often look up books and turn them over when using them. Some of the less-used databases may not be able to be found smoothly, so I will summarize them here for your reference.
<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 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 upload Program ')
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.