Connect to the database (connection string) in ASP)

Connecting to a database in ASP  

I. Principles of Database Access

In ASP, the objects used to access the database are collectively called ADO objects (Active Data Objects). They mainly contain three types of objects: connection, recordset, and command. Connection is used to open or connect to the database, recordset is used to access data tables. command is used to execute action query commands on databases and stored procedure of SQL Server. Relying only on these three objects, you still cannot access the database. You must also have the database access DRIVER: ole db driver and ODBC driver. For any database, the corresponding ole db driver and ODBC driver must be available, and the ADO object can be used to access the database.

ADO objects must be combined with various drivers to access various types of databases. Different databases need different drivers. On the "drivers" tab in "start"> "Settings"> "Control Panel"> "ODBC Data Source (32bit)" of Windows 9x/NT, verify which drivers are installed on the machine.

2. Connect to the database and open the data table

The Connection Methods for different databases are different (that is, the methods for establishing a connection instance are different). However, after a connection instance is established, the methods for using the recordset object to access data are similar. The following describes the corresponding connection functions for different data types, which can be directly referenced in the program.

The program is written in the VB script language.

1. Create an mdbrecordset object. MDB is a complete database, which may contain several data tables. In this function, connection is used to connect to the database, and recordset is used to open the data table.

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 file name ")
'Open the database
Conn. Open provider & dbpath
Set createmdbrecordset = server. Createobject ("ADODB. recordset ")
'Open a data table
Createmdbrecordset. Open "data table name", Conn, 2, 2
End Function

2. Create A recordset object for the mdb database with a password. This method is similar to setting up a recordset object for an mdb database without a password. It only requires a password parameter while 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 file name ")
'Connect to the database. Be sure to include the password parameter.
Conn. Open provider & dbpath & "jet oledb: Database Password =" & assword
Set createsecuredmdbrecordset = server.
Createobject ("ADODB. recordset ")
'Open a data table
Createsecuredmdbrecordset. Open "data table name", Conn, 2, 2
End Function

3. the DBF file is not a standard database file and is only equivalent to a data table in the standard database file. To use the DBF file, you can put all the DBF Files in one directory, in this way, the directory name is regarded as a standard database, and each DBF file is equivalent to a data table in the standard database. Directory in the following function is the directory name of DBF.

Function createdbfrecordset (directory name, DBF file name 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 ")
'Call the Open Method to open the database
Conn. Open driver & sourcetype & dbpath
Set createdbfrecordset = server. Createobject ("ADODB. recordset ")
'Open the DBF File
Createdbfrecordset. Open "DBF file name or select statement", Conn, 2, 2
End Function

4. the DB database generated by FoxPro is similar to the mdb database. It is in the form of a database containing several data tables. Therefore, the access method for the DB database is similar to that for the mdb database.

Function createdbcrecordset (name of the DBC database file, 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 file name ")
'Connect to the database
Conn. Open driver & sourcetype & dbpath
Set createdbcrecordset = server. Createobject ("ADODB. recordset ")
'Open a data table
Createdbcrecordset. Open "data table name or select statement", Conn, 2, 2
End Function

5. Regard the xls file (book) generated by Excel as a database, and each Worksheet (sheet) in it as a database table.

Function createexcelrecordset (xls file name, 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 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, 2
End Function

6. SQL Server is a server-level database with strict usage requirements. You must enter the user name and password to use it.

Function createsqlserverrecordset (computer name, user ID, user password, database name data table or view table or SELECT command)
Dim Params, Conn
Set creatsqlserverconnection = nothing
Set conn = server. Createobject ("ADODB. Connection ")
Params = "provider = sqloledb.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