ASP common database connection method and skills
Reposted: SNOW Date: 2005-02-28 Popularity: 19
Reposted: SNOW Date: 2005-02-28 Popularity: 19
<% DIM CONN, MDBFILE MDBFILE = Server.mappath ("Database Name. MDB") set conn = server.createObject ("adodb.connection" conn.open "driver = {Microsoft Access Driver (* .mdb)}; uid = Admin; PWD = database password; dbq = "& mdbfile%>
2. ASP and SQL database connection:
<% DIM Conn Set Conn = Server.createObject ("AdoDb.Connection") Con -.open "provider = sqloledb; data source = SQL server name or IP address; UID = SA; PWD = database password
Database = Database Name%>
Establish record set objects:
SET RS = Server.createObject ("AdoDb.Recordset") RS.Open SQL statement, CONN, 3, 2
3. SQL common command usage method:
(1) Data Record Screening:
SQL = "SELECT * FROM DATA WHERE Field Name = Field Value ORDER BY Field Name" SQL = "SELECT * FROM DATA WHERE Field Name Like '% Field Value%' Order BY Field Name" SQL = "SELECT TOP 10 * from Data Sheet WHERE Field Name ORDER BY Field Name "SQL =" SELECT * FROM Datasheet WHERE Field Name in ('value 1', 'value 2', 'value 3') "SQL =" SELECT * FROM Datasheet WHERE field name BetWeen Value 1 AND Value 2 "
(2) Update data record:
SQL = "Update Data Table SET Field Name = Field Value WHERE Condition Expression" SQL = "Update Data Table Set Field 1 = Value 1, Field 2 = Value 2 ... Field N = Value N Where Conditions Expression
(3) Delete data records:
SQL = "delete from data table where condition expression" SQL = "delete from data table" (delete data tables)
(4) Add a data record:
SQL = "INSERT INTO Data Sheet (Field 1, Field 2, Field 3 ...) Valuess (value 1, value 2, value 3 ...)" SQL = "INSERT INTO target data table select * from" source data " Table record added to the target data sheet)
(5) Data logging statistics:
AVG (Field Name) gives a table bar average count (* | field name) Statistics on the number of data lines or the maximum value of the value of the value of a value for a column. MIN (Field Name) gets the smallest value of a table bar SUM (field name) adds the value of the data bar.
Citing the above function:
SQL = "SELECT SUM (field name) AS alias from Datasheet Where WHERE Condition Expression" SET RS = Conn.excute (SQL) The value of the value is obtained by RS ("alias", and other functions are using the same.
(5) Establishment and deletion of data sheets:
Create Table Data Sheet Name (Field 1 Type 1 (Length), Field 2 Type 2 (Length) ...)
Example: Create Table Tab01 (Name Varchar (50), DateTime Default now ())
DROP TABLE Datasheet Name (permanently delete a data sheet)
(6) Method of recording the object: rs.movenext Move the record pointer down the current location RS.MOVEPREVIOS to move the record pointer from the current position to the top RS.MoveFirst to move the record pointer to the data table RS .Movelast moves the record pointer to the last line RS.AbsolutePosition = n Move the record pointer to the data table Nth line rs.absolutepage = n Move the record pointer to the first line of the first line of the nth page RS.PageSize = n Set each Page N: RS.PageCount Returns the total number RS.RecordCount Returns the total number of record pointer to the record pointer over the data sheet header, true, whether false is rs.eof returns the record pointer is beyond The data sheet is said to be that false is not Rs.Delete deletes the current record, but the record pointer does not move the rs.addnew to add the RS.AddNew to the data table Rs.Update update data sheet record
Judging the filled data is a digital type
If not isnumeric (Request ("Field Name")) Then Response.write "Not Digital" Else Response.write "Digital" end if
Operations of the database are often used. Including connection code, SQL commands, etc., and never deliberate them (I am not willing.
Remember this stuff), so often go to book this time when you use it, turn over. Some relatively small databases are still not necessarily
If you come, so you now pay more attention to you, and provide you with reference. (The personal level is limited, there is a defect, welcome everyone to correct.)
1.An-LESS connection method for the Access database:
Set adocon = server.createObject ("adoDb.connection" adoconn.open "driver = {Microsoft Access Driver (*. mdb)}; dbq =" & _ server.mappath ("The path of the 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 where database is located)
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)}; 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 = {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 '"
1. Query data record (SELECT) Syntax: SELECT field serial from table where field = content example: I want to find all records of the author "Cancer" from the book table, the SQL statement is as follows: Select * from book where author = 'Cancer' "*" is a field that removes the Book table. If the field value of the query is a number, then the "content" will not be plus single quotes.
If the date, use (#) in Access, and 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)
Tip: Date function to_date is not a standard SQL text, not all database applications, so everyone should refer to the database specific syntax when using
In addition, if it is the incoming variable, the following:
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 statement is written: strsql = "select * from book = '" & intid & "'". But the character type must not be written according to the digital format, you need to pay attention.
2. Add Record Syntax: Insert Into Table (Field1, Field2, ....) Values (Value1, Value2, ....) Example: Add a record of "Cancer" into the Book table: Insert Into Book (Bookno, Author, Bookname "Values ('cf001', 'cancer",' Cancer No component
Sequence '), like this, if the variable is 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. Method for inserting data with the ADDNEW of the RecordSet object: Syntax:
RS.AddNew RS ("Field1"). Value = Value1 RS ("Field2"). Value = Value2 ... rs.Update
4. Modify Data Record (Update) Syntax: Update Table Set Field1 = Value1, Field2 = Value2, ... WHERE Fieldx = Valuex Example: Update Book Set Author = 'Babycrazy' Where Bookno = 'cf001' If used, the variable is as follows :
Strno = Request.form ("Bookno") strau = request.form ("author") strsql = "Update book set author = '" & strau & "' Where bookno = '" & strno "'"
5. Update method for theRecordset object: Syntax:
RS ("Field1"). Value = Value1 RS ("Field2"). Value = Value2 ... rs.Update
Note: When using syntax 3 and syntax 5, be sure to pay attention to the type of field (especially date type), otherwise the chance of error 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 = (" / can / 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 the 'If there is this record rs ("author"). value = strau rs.Update end if rs.close set = Nothing adocon.close set adocon = Nothing
6. Delete a record (Delete) Syntax: Delete Table Where Field = Value Example: Delete Book Table The author is a record of caverr
Delete Book Where Author = 'Cancer' (Note: If there are multiple records of the value of the Author field in the Book table, all author is recorded)
