In ASP, you can access the object of the database to collect the ADO object (Active Data Objects), which mainly contains three objects: Connection, RecordSet, and Command, where the connection is responsible for opening or connecting the database, the recordset is responsible for accessing the data table, Command is responsible for The database executes the action query command and the Stored ProCedure that executes SQL Server. Only by relying on these three objects or cannot access databases, they must also have data inventory-taking drivers: OLE DB drivers and ODBC drivers. For any database, there must be a corresponding OLE DB driver and an ODBC driver, and the ADO object can access the database.
Connect the database and open the data sheet
The connection method of different databases is different (that is, the method of establishing a Connection instance is not the same), but after establishing the Connection instance, the method of accessing data using the RecordSet object is similar. The corresponding connection function is written for different data types, and can be referenced directly in the program.
The program is written in VB Script scripting language.
1. Establish a MDBRecordset object.
The MDB database is a complete database, which may contain several data tables. In this function, the role of Connection is to connect the database, and the RECORDSET's role is to open the data table.
Function CreatemdbRecordset (Database file name, data table name, or SELECT statement)
DIM CONN, Province, dbpath
'Establish Connection object
Set conn = server.createObject ("adoDb.connection")
Provider = "provider = microsoft.jet.o
Ledb.4.0; "
Dbpath = "data source =" & server.mappath ("Database File Name")
'Open the database
Conn.open provider & dbpath
SET CREATEMDBRECORDSET = Server.createObject ("AdoDb.Recordset")
'Open data sheet
CreatemdbRecordset.open "Data Name", CON, 2, 2
END FUNCTION
2. Create the RECORDSET object with a password MDB database. Its establishment mode is similar to the Recordset object that establishes the MDB database that does not have a password, just a password parameter, that is, password information must be given when connecting to the database.
Function CreateSecuredMdbRecordset (Database file name, data table name, or SELECT statement, Password)
DIM CONN, Province, dbpath
'Establish Connection object
Set conn = server.createObject ("adoDb.connection")
Provider = "provider = microsof.jet.
OLEDB.4.0; "
Dbpath = "data source =" & server.mappath ("Database File Name")
'Connecting to the database, pay attention to password parameters
Conn.open Provider & Dbpath & "Jet OLEDB: Database Password =" & AsswordSet CreateSecuredMdbRecordset = Server.createObject ("AdoDb.Recordset")
'Open data sheet
CreateSecuredMdbRecordset.open "Data Name", CON, 2, 2
END FUNCTION
3. DBF file is not a standard database file, which is equivalent to a data table in a standard database file, so in order to use the DBF file, you can put all DBF files in a directory so that the directory name as a standard database. Each DBF file is equivalent to a data sheet in a standard database. Directory in the following function is the directory name where DBF is located.
Function CREATEDBFRECORDSET (directory name, DBF file name, or SELECT statement)
DIM CONN, DRIVER, SOURCEPE, DBPATH
'Establish Connection object
Set conn = server.createObject ("adoDb.connection")
Driver = "driver = {Microsoft Visual FoxProdriver};" SourceType = "SourceType = DBF;"
Dbpath = "SourceDB =" & Server.MAppath ("Directory Name")
'Call the OPEN method to open the database
Conn.open Driver & SourceType & Dbpath
SET CREATEDBFRECORDSET = Server.createObject ("AdoDb.Recordset")
'Open DBF file
CreatedBfrecordSet.open "DBF file name or SELECT statement", conn, 2, 2
END FUNCTION
4. DBC database generated by FoxPro and
The MDB database is similar, all of which contain several data tables, so similar to the MDB database for the access method of the DBC database.
Function CREATEDBCRECORDSET (DBC Database File Name, Data Table Name, or SELECT statement)
DIM CONN, DRIVER, SOURCEPE, DBPATH
'Establish Connection object
Set conn = server.createObject ("adoDb.connection")
Driver = "driver = {Microsoft Visual FoxPro Driver};"
SourceType = "SourceType = DBC;"
Dbpath = "SourceDB =" & Server.MAppath ("DBC Database File Name")
'Connect to the database
Conn.open Driver & SourceType & Dbpath
SET CREATEDBCRECORDSET = Server.createObject ("AdoDb.Recordset")
'Open data sheet
CreatedbcRecordset.open "Data Table Name or SELECT Statement", CON, 2, 2
END FUNCTION
5. Look with the XLS file (Book) generated by Excel as a database, where each of the worksheets (Sheet) see a database table. Function CreateExcelRecordset (XLS file name, Sheet name)
DIM Conn.driver, dbpath
'Establish Connection object
Set conn = server.createObject ("adoDb.connection")
Driver = "driver = {Microsoft Excel Driver (* .xls)};"
Dbpath = "dbq =" & server.mappath ("XLS file name")
'Call the OPEN method to open the database
Conn.open Driver & Dbpath
Set createExcelRecordset = Server.createObject ("AdoDb.Recordset")
'Open Sheet
CreateExcelRecordset.open "Select * from [" & Sheet & "$], CON, 2, 2
END FUNCTION
6. SQL Server is a Server-level database. The requirements are strict when using it, and you must enter the username and password to use.
Function CreatesqlServerRecordset (Computer Name, User ID, User Password, Database Name Data Table, or View Table or SELECT Directive)
Dim params, conn
Set creatsqlserverConnection = Nothing
Set conn = server.createObject ("adoDb.connection")
Params = "provider = SQLO
LEDB.1 "
PARAMS = params & "; data source =" & computer
PARAMS = params & "; user id =" & userid
Params = params & "Password =" & password
PARAMS = params & ".initial catalog =" & database name
Conn Open Paras
Set createSqlServerRecordset = Server.
CreateObject ("AdoDb.Recordset")
CreatesqlServerRecordset.open Source, CONN, 2, 2
END FUNCTION
