Connect the database in ASP
First, the principle of accessing the database
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.
The ADO object must be combined with various drivers to access various types of databases, different databases require different drivers. On the "Start" → "Settings" → "Settings" → "Control Panel" → "ODBC Data Source (32bit", you can verify which drivers do you have on the machine.
Second, 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, table name or Select statement) Dim conn, Provider, DBPath 'establish a Connection object Set conn = Server.CreateObject ( "ADODB.Connection") Provider = "Provider = Microsoft.Jet.OLEDB.4.0; "Dbpath =" data source = "& server.mappath (" Database File Name ") 'Opens Database Conn.open Provider & Dbpath Set CreatemdbRecordset = Server.createObject (" AdoDb.Recordset ")' Open Data Sheet CreatemdbRecordSet.Open" data Table name, CONN, 2, 2END 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, table name or Select statement, password) Dim conn, Provider, DBPath 'establish a Connection object Set conn = Server.CreateObject ( "ADODB.Connection") Provider = "Provider = Microsof.Jet.OLEDB. 4.0; "dbpath =" data source = "& server.mappath (" Database File Name ") 'Connecting Database, Note To Password Parameters Conn Provider & Dbpath &" Jet Oledb: Database Password = "& Assword Set CreatecuredMdbRecordset = Server CreateObject ("AdoDb.Recordset") 'Opens Data Table CreateSecuredMdbRecordSet.Open "Data Table Name", CONN, 2, 2END FUNCTION3.DBF files are not a standard database file, just equivalent to a data table in the standard database file, So in order to use the DBF file, all DBF files can be placed in a directory so that the directory name is regarded as a standard database, each DBF file 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, SourceType, DBPath 'establish a 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, 2nd function
4. The DBC database generated by FoxPro is similar to the MDB database, which is a database that contains several data tables, so the access method of the DBC database is similar to the MDB database.
Function CreateDbcRecordset (DBC database file name, table name or Select statement) Dim conn, Driver, SourceType, DBPath 'establish a 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 ") 'Connecting Database Conn.open Driver & SourceType & Dbpath Set CreateDbcRecordset = Server.createObject (" AdoDB. Recordset ") 'Open Data Table CreateDbcRecordset.open" Data Table Name or SELECT Statement ", CONN, 2, 2END FUNCTION5. Look at the XLS file (Book) generated by Excel as a database, where each worksheet (Sheet) A database table.
Function CreateExcelRecordset (XLS file name, Sheet name) Dim conn.Driver, DBPath 'establishment 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 &" $], conn, 2, 2nd 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.
