Connection Database | String One, the principle of accessing the database
In ASP, the objects used to access the database are collectively called ADO objects (Active Data Objects), which contains three objects: Connection, Recordset, and command, where Connection is responsible for opening or connecting to the database. The recordset is responsible for accessing the data tables, command is responsible for executing the actions query (action queries) commands on the database and executing the stored Procedure of SQL Server. You must also have database-accessible drivers, OLE DB drivers, and ODBC drivers that rely on only these three objects or cannot access the database. An ADO object can access a database for any database that must have a corresponding OLE DB driver and ODBC driver.
ADO objects must be combined with a variety of drivers to access various types of databases, and different databases require different drivers. In Windows 9x/nt, "start" → "settings" → "Control Panel" → "ODBC Data Source (32Bit)" In the "Driver" tab, you can verify what drivers are installed on the machine.
Second, connect the database and open the data table
The methods of connecting different databases (that is, to establish connection instances are different), but the method of accessing data using the Recordset object is much the same when the connection instance is established. The following for different data types, write the corresponding connection function, in the program directly referenced.
The program is written in VB Script scripting language.
1. Establish the Mdbrecordset object. The MDB database is a complete database that may contain several data tables, in which the connection function is to connect the database, and the recordset is to open the datasheet.
Function createmdbrecordset (database file name, data table name, or SELECT statement)
Dim Conn,provider,dbpath
' Create a Connection object
Set conn = Server.CreateObject ("ADODB. Connection ")
Provider= "Provider=Microsoft.Jet.OLEDB.4.0;"
DBPath = "Data source=" & Server.MapPath ("Database filename")
' Open the Database
Conn. Open Provider & DBPath
Set Createmdbrecordset = Server.CreateObject ("ADODB. Recordset ")
' Open Data table
Createmdbrecordset.open "datasheet name", Conn, 2, 2
End Function
2. Create a Recordset object with a password MDB database. It is built like a Recordset object that creates an MDB database without a password, but one more password parameter, that is, the password information must be given when connecting to the database.
Function createsecuredmdbrecordset (database file name, data table name or SELECT statement, password)
Dim Conn,provider,dbpath
' Create a Connection object
Set conn = Server.CreateObject ("ADODB. Connection ")
Provider = "provider=microsof.jet.oledb.4.0;"
DBPath = "Data source=" & Server.MapPath ("Database filename")
' Connect to the database, note the password parameter
Conn. Open Provider & dbpath& "Jet oledb:database password=" &assword
Set Createsecuredmdbrecordset = Server.
CreateObject ("ADODB.") Recordset ")
' Open Data table
Createsecuredmdbrecordset.open "datasheet name", Conn, 2, 2
End Function
3.DBF file is not a standard database file, only equivalent to a data table in the standard database file, so in order to use DBF file, all DBF files can be placed in a directory, so that the directory name as a standard database, each DBF file corresponds to the standard database data table. The directory in the following function is the name of the catalog in which DBF resides.
Function createdbfrecordset (directory name, DBF filename, or SELECT statement)
Dim Conn,driver,sourcetype,dbpath
' Create a Connection object
Set conn = Server.CreateObject ("ADODB. Connection ")
Driver= "Driver={microsoft Visual foxprodriver};" SourceType = "SOURCETYPE=DBF;"
Dbpath= "sourcedb=" & Server.MapPath ("Directory Name")
' Invoke the Open method to open the database
Conn. Open Driver & sourcetype & DBPath
Set Createdbfrecordset = Server.CreateObject ("ADODB. Recordset ")
' Open DBF file
Createdbfrecordset.open "DBF filename or SELECT statement", Conn, 2, 2
End Function
4. The DBC database generated by FoxPro is similar to the MDB database, which is a database containing several data tables, so the access method for the DBC database is similar to that of the MDB database.
Function createdbcrecordset (DBC database file name, data table name, or SELECT statement)
Dim Conn,driver,sourcetype,dbpath
' Create a Connection object
Set conn = Server.CreateObject ("ADODB. Connection ")
Driver= "Driver={microsoft Visual FoxPro Driver};"
SourceType = "SOURCETYPE=DBC;"
DBPath = "sourcedb=" & Server.MapPath ("DBC database filename")
' Connect to the 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, 2
End Function
5. The xls file (book) generated by Excel is viewed as a database in which each worksheet (sheet) is treated as a database table.
Function createexcelrecordset (xls filename, sheet name)
Dim Conn. Driver,dbpath
' Create a Connection object
Set conn = Server.CreateObject ("ADODB. Connection ")
Driver= "Driver={microsoft Excel Driver (*.xls)};"
DBPath = "dbq=" & Server.MapPath ("xls filename")
' Invoke 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, 2
End Function
6.SQL server is a server-level database that is strictly required and must be entered with a username and password to be used.
Function createsqlserverrecordset (computer name, user ID, user password, database name data table or view table or select instruction)
Dim Params, Conn
Set creatsqlserverconnection = Nothing
Set conn = Server.CreateObject ("ADODB. Connection ")
Params = "Provider=SQLOLEDB.1"
Params = Params & ";D ata 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
