Introduction
This article explains the most basic and critical part of ASP programming, "database file calling", and talks about some techniques for database file calling in ASP programming.
Introduction to ASP
ASP (Active Server Pages) is a Web application development technology launched by Microsoft in 1996. It is a combination of scripting language, ActiveX components and HTML language. Microsoft describes it as "a server scripting environment. Here you can build and run dynamic, interactive, high-performance web server applications." Its main function is to provide a powerful way or technology for generating dynamic and interactive Web server applications. Its characteristic is that commands and scripts are interpreted and executed in the server, and then the content sent to the client browser is just a standard HTML page. The advantage is that the program design is simple and easy to understand, and it is convenient and fast. ASP has been widely used in China, and a considerable number of dynamic websites use ASP technology.
Although different systems may install different database drivers for
calling ASP database files
, general server systems basically support IIS4.0/5.0, so the server system will at least have Microsoft Access Driver, Microsoft ODBC for Oracle, SQL Server, etc.3 database drivers. Therefore, the server system can use at least three databases such as Access, Oracle, and SQL Server to engage in ASP web page database design. Since Microsoft Access 97/2000 belongs to one of Microsoft's Office series, and the interface is similar to the Office series software, there is basically no obstacle to learning. Therefore, this article will use Access database files for ASP programming from the perspective of beginners.
Create a database file (friend.mdb) as shown in Figure 1 through the Access application
Figure 1
Then, there are two ways to call the database file in ASP programming. One is to manually set it directly on the "Data Source (ODBC)" of the "Control Panel"; the other is to program and call the database through a relative path. file, this method can be applied to any server without further configuration. The first method is simpler and safer, and this article talks about the second method.
Listed below are the general methods for reading table records in database files in ASP programming:
01: <HTML><BODY>
02： ＜!--#include file="adovbs.inc"-->
03：＜%
04: 'Use ASP's Connection object to open the database. The database file is <Friend.mdb> in the picture above'
05: Dim objConn
06: Set objConn=Server.CreateObject("ADODB.Connection")
07: objConn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" &_
08: "Data Source=" & Server.MapPath("Friend.mdb")
09: objConn.Open
10: Read the records of the "data" table and store them in the Record set object
11: Dim objRS
12: Set objRS=Server.CreateObject("ADODB.Recordset")
13: ObjRS.Open "data",objConn,adOpenKeyset,adLockOptimistic,adCmdTable
14: 'Display the record pointed to by the current pointer on the browser
15: If Not objRS. EOF then
16: Response.Write "Number:"&objRS("Number")& "＜BR＞"
17： Response. Write "Name:"&objRS("Name")& "＜BR＞"
18： Response. Write "Gender:"&objRS("Gender")& "＜BR＞"
19:Else
20: Response.Write "The end of the database has been reached and all matching records have been displayed"
21: End If
22: 'Close the database connection and release the object instance
23: ObjRS. Close
24: Set objRS=Nothing
25: ObjConn.Close
26: Set objConn=Nothing
27: %>
28: ＜/BODY＞＜/HTML＞
The above code is the design steps for opening Access database files in ASP programming.
Tips for calling database files
(1) In fact, no matter which Access database you switch to, the steps for opening a database connection and reading table records are the same. The variables are the name of the database file and the name of the table, so the above program can be Rewrite lines 3 to 13 in the form of functions and save them in a file such as: ADOFunctions.asp. If you want to open a database file in the future, include the file ADOFunctions.asp. The code is as follows:
<%
Dim objConn
'The variable Filename is the database file name, the variable Table Name is the table name Function GetRecordset(FileName,TableName)
'Use ASP's Connection object to open the database Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=" & Server.MapPath("Filename")
objConn.Open
'Read the records of the table and store them in the Record set object "objRS"
Dim objRS
Set objRS=Server.CreateObject("ADODB.Recordset")
ObjRS.Open TableName,objConn,adOpenKeyset,adLockOptimistic,adCmdTable
End Function
%>
As can be seen from the above code, the function name is GetRecordSet, and its return value is a Record set object instance that stores table records. The file name is ADOFunctions.asp. Now, you can use this file to read the records of any database file. For example, the programming to read the database can be simplified as follows:
<HTML> <BODY>
<! --#Include file="adovbs.inc"-->
<! --#include file="ADOFunctions.asp"-->
<%
'Call the GetRecordset function to obtain a Record set object instance, and then assign it to the variable objRS
Dim objRS
Set objRS=GetRecordset("Friend.mdb","data")
'Display the record pointed to by the current pointer on the browserIf Not objRS.EOF Then
Response.Write "Number:"&objRS("Number")& "＜BR＞"
Response.Write "Name:"&objRS("Name")& "＜BR＞"
Response.Write "Gender:"&objRS("Gender")&"＜BR＞"
Else
Response.Write "The end of the database has been reached and all matching records have been displayed"
End If
'Close the database connection and release the object instance ObjRS. Close
Set objRS=Nothing
ObjConn.Close
Set objConn=Nothing
%>
＜/BODY＞＜/HTML＞
Therefore, as long as you change the database name and table name in this code Set objRS=GetRecordset("Friend.mdb", "data"), you can call any Access database file. Of course, you must pay attention to What's more, the field names of each table in the subsequent database must match.
(2) In addition, no matter which Access database you switch to, the steps for opening a database connection and filtering table records are the same. The variables include the SQL statement (such as: "SELECT * FROM data"), the name of the database file and the name of the table. . Therefore, in the same way, you can use these three variables as parameters of the function, write the GetSQLRecordset function, and save it as a file named ADOSQLFunctions.asp. If you want to use it in the future, just include this file at the front of the program and you can use GetSQLRecordset The function opens a database connection and also filters table records. The return value of this function stores a Record set object instance that conforms to the SQL statement.
The code is as follows:
<%
Dim objConn
Dim GetSQLRecordset
Function GetSQLRecordset(strSQL,FileName,TableName)
'Use ASP's Connection object to open the database
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=" & Server.MapPath("Filename")
objConn.Open
'Read records that match the SQL statement from the table and store them in the Record set object
Set GetSQLRecordset=Server.CreateObject("ADODB.Recordset")
GetSQLRecordset.Open_ strSQL,objConn,adOpenKeyset,adLockOptimistic,adCmdText
End Function
%>
The function name in the above code is: GetSQLRecordset, and the file name is ADOSQLFunctions.asp.
Now, you can use this file to call the connection to any Access database and filter the table records at the same time. Taking the Friend.mdb file as an example, list all the records in the table data. The program code is as follows:
<HTML> <BODY>
<!--#include file="adovbs.inc"-->
<!--#include file="ADOSQLFunctions.asp"-->
<%
Dim objRS
Set objRS=GetSQLRecordset("SELECT number, name, gender from_ data", "Friend.mdb", "data")
Do While Not objRS.EOF
Response.Write "Number:"&objRS("Number")&"＜BR＞"
Response.Write "Name:"&objRS("Name")&"＜BR＞"
Response.Write "Gender:"&objRS("Gender")&"＜BR＞"
Loop
objRS.Close
Set objRS=Nothing
ObjConn.Close
Set objConn=Nothing
%>
＜/BODY＞＜/HTML＞
Summary
In ASP programming, making good use of functions can often make our program code simple and clear, and the reading structure is easy to maintain. At the same time, it can also avoid a large number of repetitive and complicated codes. Like the above situation, if you just simply connect to the database, use the first situation and include the file ADORecordset.asp. If you want to filter the records of a certain table in the database or use other SQL statements Operation, use the second case, just load the file ADOSQLRecordset.asp.