Using ASP to access various common type databases

Data | database

　　Summary:

This paper discusses the method and function realization of using ASP to access DBF, DBC, MDB, Excel, SQL Server data, and finally gives an example of accessing Excel database by using the function provided in this paper, which has strong practical meaning for enterprise data reuse and sharing.

　　Keywords:

ASP database access DBF DBC MDB Excel SQL Server

　　Objective

The Internet/intranet Web pages are filled with Internet feature modules such as message boards, discussion groups, online polls, online surveys, and chat rooms, which are implemented using ASP (Active Server Page) and database technology. Most of the databases used on Internet/intranet are MDB databases or sql< databases, but in enterprise intranet< networks, various versions of databases already exist such as dbf<, DBC, Excel, and text databases. These databases are generated by application software such as Office automation system, financial software and so on, if the ASP can directly use these types of databases, there is no need to re-establish the database, and further realize the enterprise data sharing, whether these databases can be directly used by ASP, the author after a long discussion summed up to give a positive answer

　　ASP Object Access database method

In ASP, the object used to access the database is called ADO (Active Data Objects), which contains three kinds of objects: Connection, Recordset, 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. For any database, you must have a corresponding OLE DB driver and ODBC driver,ado< to access the database. The access process can be described in the following figure:

As the above figure shows, ADO objects must be combined with a variety of drivers to access various types of databases, different databases require different drivers. What drivers are installed on the machine? You can verify which drivers are installed on our machine by verifying that the Drivers tab in the Windows9x or nt "start" → "settings" → "Control Panel" → "ODBC Data Source (32Bit)". There are approximately the drivers listed in the following table:

< driver	< applicable database types
Microsoft.Jet.OLEDB.4.0	Mdb
Microsoft Access Driver	Access
Microsoft DBase Driver	Dbase
Microsoft Excel Driver	Excel
Microsoft Visual FoxPro Driver	Dbc
Sqloledb.1	SQL Server7.0

　　Second, connect the database and open the data table

Different database connection methods are not the same (that is, the establishment of connection instance method is not the same), once the establishment of the connection instance, the use of Recordset objects to access data from the same method, the following for different types of data, write the corresponding connection function, The prototype is as follows:

1 Function creatmdbrecordset (database file name, datasheet file name, or SELECT statement); database object of MDB type

2 Function createsecuredmdbrecordset (database file name, datasheet file name or SELECT statement, password); Set up a database object with a password MDB type;

3 Function createdbfrecordset (directory name, DBF filename or SELECT statement), and establish DBF type database object;

4 Function createdbcrecordset (DBC database file name, data table name or SELECT statement), establish DBC type of database object;

5 Function createexcelrecordset (xls filename, sheet name), build database object of Excel type;

6 Function createsqlserverrecordset (computer name, user ID, user password, database name datasheet or view table or select instruction); Build SQL Server type database object;

　　Iii. List of procedures

' The following programs are written in VBScript

1 The establishment of Mdbrecordset object, MDB database is a complete database, the internal may contain several data tables, in this function, the role of connection is to connect the database, the role of 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 file name")
' Open the Database
Conn. Open Provider & DBPath

Set Createmdbrecordset = Server.CreateObject ("ADODB. Recordset ")
' Open data table, parameter two is connection object
Createmdbrecordset.open "datasheet name", Conn, 2, 2

End Function

2 Create a Recordset object with a password MDB database, which is built like a Recordset object with 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=Microsoft.Jet.OLEDB.4.0;"
DBPath = "Data source=" & Server.MapPath ("Database file name")
' Connect to the database, note the following line with password parameters
Conn. Open Provider & dbpath& "Jet oledb:database password=" &assword
Set Createsecuredmdbrecordset = Server.CreateObject ("ADODB. Recordset ")
' Open data table, parameter two is connection object
Createsecuredmdbrecordset.open "datasheet name", Conn, 2, 2

End Function

3 DBF file is not a standard database file, only corresponds to a data table in the standard database file, so in order to use DBF file, put all DBF files in a directory, so that the directory name as the standard Data database table, each DBF file corresponds to the standard database data table. In the following function directory is the directory name of DBF,

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 FoxPro Driver};"
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, parameter two is connection object
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, is a database contains several forms of data tables, so the access method for the DBC database is similar to 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 file name")
' Connect to the database
Conn. Open Driver & sourcetype & DBPath
Set Createdbcrecordset = Server.CreateObject ("ADODB. Recordset ")
' Open data table, parameter two is Connection object
Createdbcrecordset.open "Data table name or SELECT statement", Conn, 2, 2

End Function

5 The EXCEL97 or Excel2000 generated xls file (book) as a database, where each worksheet (sheet) 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, parameter two is connection object, because the Excel ODBC driver cannot open the sheet directly with the ' sheet name, so note the following SELECT statement
Createexcelrecordset.open "SELECT * FROM [" &sheet& "$]", Conn, 2, 2

End Function

6 SQL Server is a server-level database, the use of more stringent requirements, you must enter a username and password to use.

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

Using the above function can open a common type of database, and then use ADO Recordset object to add records, delete records, modify records and other functions can operate on the database. But to illustrate, in the actual development we found that, if the use of DBF, DBC, Excel database, the efficiency of the implementation of the MDB database is not efficient, it is best to use the MDB type database as much as possible.

If you use DBF, DBC, Excel database, the following points to note: Excel database can only read, add records, modify records, but not delete records; DBF, dbc< can read records, add records, delete records, modify records, but when adding records, None of the field values can be empty, which shows that the limitations are great, so use an MDB or SQL database as much as possible.

　　Iv. examples (accessing Excel data Tables)

To further illustrate the use of these functions, this article gives an example of accessing Excel data tables, and other functions are accessed in a similar way. Suppose you have an Excel file named 1.xls in Figure 2, which contains three worksheets, one, two, and three. Below you need to use the above function to access the worksheet "class" data.

Figure 2

Write an ASP file (file name 1.asp) to access this Excel file.

1.asp Source Code

ASP file <%

' Create a Connection object

Function Createexcelrecordset (Xlsfile,sheet)

Dim Rs,conn,driver,dbpath

Set conn = Server.CreateObject ("ADODB. Connection ")

Driver = "Driver={microsoft Excel Driver (*.xls)};"

DBPath = "dbq=" & Server.MapPath ("Xlsfile")

' Call the Open method to connect to the database

Conn. Open Driver & DBPath

Set createexcelrecordset = Server.CreateObject ("ADODB. Recordset ")

' Open sheet table, parameter two ' >connection object

Createexcelrecordset.open "SELECT * FROM [" &sheet& "$]", Conn, 2, 2

End Function

%>

<HTML>

<body bgcolor= "#FFFFFF" >

<table border=1>

<tr bgcolor= #00FFFF >

<%

' Bank is a key line, a reference to a function

' Set Rs=createexcelrecordset ("1.xls", "one Class")

Part I outputs "Table header name" and the background color of the header is #00ffff

' For i=0 to Rs. Fields.count-1

Response.Write "<TD>" & RS (i). Name & "</TD>"

Next

%>

</TR>

<%

"Contents" of the ' part ' > Output datasheet 

Rs. MoveFirst ' moves the current data record to the first item

While not Rs. EOF ' to determine if the last item was passed

Row = "<TR>"

For i=0 to Rs. Fields.count-1

row = row & "<TD>" & RS (i) & "</TD>"

Next

Response.Write Row & "</TR>"

Rs. MoveNext ' Move to the next item

Wend

%>

</TABLE></CENTER>

</BODY>

</HTML>

After using IE and PWS (Personal Web Server), browse the results as shown in Figure 3:

Figure 3

Reference documents:

[1] DataBase and the World Wide Web Marianne Winslett, University of Illinois

[2]http://www.aspobjects.com