ASP Lecture 6: ASP and Database (1)

Author：Eve Cole Update Time：2009-05-30 19:55:05

In the previous lectures, we have learned the basic content of ASP. Using this knowledge flexibly, you can already use ASP to build a Web site. But generally speaking, a real and complete website cannot be separated from a database, because a small amount of data, such as the number of web page visitors, can be stored in text files, but in actual applications, the data that needs to be saved is much more than this. Bit by bit, and these data are often related, using a database to manage these data can be easily queried and updated. There are many kinds of databases, such as: Fox database (.dbf), Access database (.mdb), Informix, Oracle and SQL Server, etc. In this lecture, I will use Microsoft Access database and SQL Server 7.0 as examples to illustrate ASP How to access the database. Why choose ASP? Statistics show that SQL Server 7.0 is currently the fastest and most cost-effective database, and many large websites such as www.8848.net, www.dell.com, www.hotbot.com are created using ASP technology, so ASP It is a relatively easy to master and very practical technology. You should try to approach it, understand it, and then use it with confidence and courage.
What preparations should be made before further study:
1. If you have never come into contact with a database, it is recommended to install Microsoft Access in Office first and learn the basic knowledge of databases.
2. It is best to install Microsoft SQL Server 7.0. Once installed, it can be used immediately without any settings. Note that the DeskTop version should be installed in Windows 9.x, while versions before 7.0 can only be installed in NT Server.
3． The content discussed next requires a component called MDAC (Microsoft Data Access Components). This component is already included in ASP. If you want to know more about the relevant content, or download the latest version, you can visit http:// www.microsoft.com/data.

1. Establishing a connection with the database Before officially starting, let me introduce ADO - ActiveX Data Objects. If you are new to this concept, you only need to think of ADO as a bridge between ASP and the database.
(1) Method 1 to establish a connection with Microsoft Access database: Example wuf40.asp
<% @LANGUAGE = VBScript %>
<% ' wuf40.asp
Option Explicit
Dim Cnn, StrCnn
' 1 - Create Connection object
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("asp") & "NorthWind.mdb;"
'2 - Open the database using the Open method of the Connection object
Cnn.Open StrCnn

Response.Write Server.MapPath("asp") & "NorthWind.mdb;" & " "
Response.Write "Database connection successful: " & Cnn.State & " "
'3 - Close the connection using the Close method of the Connection object
Cnn.close
Response.Write "The database has been closed: " & Cnn.State
'4 - Delete the Connection object from memory to release resources
Set Cnn = Nothing
%>
Northwind.mdb is a sample database that comes with Microsoft Access97. It is usually located in the directory "Microsoft OfficeOfficeSamples". You can find it yourself and then copy this file to the corresponding directory of the web server (in this example, place it in C :InetPubhomeasp).
Here, the MapPath method of the Server object converts the specified virtual path to a real file path, and the final result is similar to: C:InetPubhomeaspNorthwind.mdb.
Method 2: Use OLE DB to create a Microsoft Access connection. The key parts are listed below.
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:InetpubhomeaspNorthwind.mdb"
Cnn.Open StrCnn

(2) Method 1 to establish a connection with the SQL Server database: Use OLE DB to connect to SQL Server. For details, see the routine wuf41.asp.
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Provider=sqloledb; User ID=sa; Password=; Initial Catalog=pubs; Data Source=ICBCZJP"
Cnn.Open StrCnn
Specify the database name after Initial Catalog, and the machine name after Data Source (that is, the computer name in Network → Properties → Identity) or IP address (the DNS service needs to be installed).
Method two:
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Driver={SQL Server};Server=ICBCZJP;UID=sa;PWD=;Database=pubs"
Cnn.Open StrCnn

In addition to the above two methods, of course you can also use the most classic method introduced in many articles and materials: using ODBC to generate a connection. That is to first establish a connection in "ODBC Data Sources" in the Web server control panel, and then use a connection string similar to "StrCnn ="DSN=ADOCnn; UID = sa;PWD=;Database=pubs"" in ASP. I won’t introduce it in detail here. Firstly, this is not the best method. Secondly, other materials have introduced this method.
In addition, if you are interested, you can download the routine wuf42.asp to see how to use the ConnectionTimeout and ConnectionString properties of the Connection object.

2. The database starts here - retrieval of data that already exists in the database. Now it is assumed that you already have the most basic database knowledge and know how to use Microsoft Access to open the "Shipper" table in Northwind.mdb and see the data stored in the table. A few pieces of data. Now the question is how to use ASP to display this data in the browser. Here are three methods.
Special reminder: If your database foundation is weak, you only need to be able to use the second method. Don't be greedy to avoid going too far, so remember! Remember!

Method 1: Only use the Connection object. Example wuf43.asp
<% @LANGUAGE = VBScript %>
<% ' wuf43.asp
Option Explicit
'This sentence is very important, it can ensure that the data you see is not the data cached on the client.
'But the latest data updated at any time on the server side
Response.Expires = 0

'Part 1: Establishing a connection

Dim Cnn, StrCnn
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:InetpubhomeaspNorthwind.mdb"
Cnn.Open StrCnn

'Part 2: Use the Execute method of the Connection object to obtain the recordset

Dim StrSQL, rsTest
'The following is a SQL statement (structured query statement), which will not be introduced in detail in this lecture.' It is recommended to read this book. The basic use is relatively simple, and you can learn it once you learn it.
'This sentence means to select all data from the carrier table
StrSQL = "Select * From shipper"
Set rsTest = Cnn.Execute(StrSQL)
%>
<HTML>
<BODY>
<% 'Part 3: Display the obtained record set on the browser

'Loop to the end of the record - display line by line, record by record
Do While Not rsTest.EOF

'The following two lines have the same effect, that is, rsTest("Shipper ID") is equivalent to rsTest(0)
Response.Write rsTest("Shipper ID") & " " & rsTest("Company Name") & " " & rsTest("Telephone") & " " & " "
'Response.Write rsTest(0) & " " & rsTest(1) & " " & rsTest(2) & " " & " "

'Move to the next record - this sentence must not be omitted, otherwise it will fall into an infinite loop
rsTest.MoveNext
Loop

'Part 4: Cleaning up the battlefield
Cnn.close
Set rsTest = Nothing: Set Cnn = Nothing
%>
</BODY>
</HTML>
This example is very simple. The third part shows the data in the typical output style of the database. You can refer to the knowledge you have learned before and add tables and colors to beautify the output results.
Non-junior users can refer to wuf44.asp to see the complete use of the Execute method.

Method 2: By creating a Recordset object - remember that beginners only need to master this method.
Example: wuf45.asp, other parts are the same as wuf43.asp, the key lies in the second part of the program.
'Part 2: Get the recordset by creating a RecordSet object
Dim StrSQL, rsTest
'Create Recordset object
Set rsTest = Server.CreateObject("ADODB.Recordset")

StrSQL = "Select carrier ID, phone number, company name From carrier Where phone number = '(503) 555-9931'"
'Attach the Recordset object to the connection Cnn
Set rsTest.ActiveConnection = Cnn
'Open the recordset using the Open method of the Recordset object
rsTest.Open StrSQL
Now let us look at the complete usage of the Open method of the Recordset object, for example wuf48.asp.
<% @LANGUAGE = VBScript %>
<% ' wuf48.asp
Option Explicit
Response.Expires = 0
%>

<%
'Part 1: Establishing a connection
Dim Cnn, StrCnn
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:InetpubhomeaspNorthwind.mdb"
Cnn.Open StrCnn

'Part 2: Get the recordset by creating a RecordSet object
Dim rsTest
Set rsTest = Server.CreateObject("ADODB.Recordset")

rsTest.Open "Shipper",Cnn,adOpenForwardOnly,adLockReadOnly,adCmdTable
%>
<HTML>
<BODY>
<% 'Part 3: Display the obtained record set on the browser
Do While Not rsTest.EOF
Response.Write rsTest(0) & " " & rsTest(1) & " " & rsTest(2) & " " & " "
rsTest.MoveNext
Loop

'Part 4: Cleaning up the battlefield
rsTest.Close: Cnn.Close
Set rsTest = Nothing: Set Cnn = Nothing
%>
</BODY>
</HTML>
analyze:
1. First look at the key sentences:
rsTest.Open "Shipper",Cnn,adOpenForwardOnly,adLockReadOnly,adCmdTable
The first parameter can be a table name (such as: freight forwarder) or a SQL statement (such as wuf45.asp).
The second parameter specifies the current connection.
The third parameter indicates the CursorType, which determines the type of cursor the provider should use when opening the Recordset. This uses a cursor that only moves forward.
The fourth parameter indicates the LockType, which determines the type of lock (concurrency) the provider should use when opening the Recordset. Specify read-only here.
The fifth parameter is related to the first parameter. If the first parameter in this example is the table name, then the fifth parameter uses adCmdTable. If the first parameter is a SQL statement, the first parameter is adCmdText, such as:
rsTest.Open "Select * From carrier",Cnn,adOpenForwardOnly,adLockReadOnly,adCmdText
I don’t think most people can really understand the meaning of these five parameters. It doesn’t matter. At the beginning, just copy them and use them. In the future, we will continue to be in contact, and we will become perfect as we get used to them.
The two parameters CursorType and LockType will be explained in detail below. Just to retrieve data, you only need to set them according to this example.
2. These five parameters can also be set using the properties of the Recordset object, such as wuf46.asp.
'Part 2: Get the recordset by creating a RecordSet object
Dim rsTest
Set rsTest = Server.CreateObject("ADODB.Recordset")
rsTest.ActiveConnection = Cnn
rsTest.CursorType = adOpenForwardOnly
rsTest.LockType = adLockReadOnly
rsTest.Open "Shipper", , , ,adCmdTable
3． Look at this sentence again: .
(1) Because constants such as adOpenForwardOnly, adLockReadOnly, and adCmdText are used, the file adovbs.inc needs to be included.
(2) The adovbs.inc file is generally located in the directory Program FilesCommon FilesSYSTEMADO. You can copy it to the current directory of the web server (this file is also included in the download package for this lecture).
(3) Open this file with Notepad and take a look, and you will understand why this file is needed.

Method three: By introducing the Command object - this should be studied under the premise that you are capable.
<% @LANGUAGE = VBScript %>
<% ' wuf47.asp
Option Explicit
Response.Expires = 0
%>

<%
'Part 1: Establishing a connection
Dim Cnn, StrCnn
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:InetpubhomeaspNorthwind.mdb"
Cnn.Open StrCnn

'Part 2: Get the recordset by creating a Command object
Dim StrSQL, rsTest, cmdTest
'Create Command object
Set cmdTest = Server.CreateObject("ADODB.Command")

StrSQL = "SELECT MAX(quantity) FROM order details"
cmdTest.CommandText = StrSQL
cmdTest.CommandType = adCmdText 'Indicates that the command type is a SQL statement
Set cmdTest.ActiveConnection = Cnn
'Use the Execute method of the Command object to get the recordset.
Set rsTest = cmdTest.Execute
%>
<HTML>
<BODY>
<% 'Part 3: Display the obtained record set on the browser

Do While Not rsTest.EOF
Response.Write rsTest(0) & " "
rsTest.MoveNext
Loop

'Part 4: Cleaning up the battlefield

rsTest.Close: Cnn.Close
Set rsTest = Nothing: Set cmdTest=Nothing: Set Cnn = Nothing
%>
</BODY>
</HTML>
The use of the Command object will be explained in the in-depth programming of the database in the future.

This lecture mainly introduces how to connect to the database and retrieve database data. Beginners will inevitably be a little confused. Remember, you only need to learn the following knowledge points:
1. How to use OLE DB to establish a connection with a Microsoft Access database;
2. How to use OLE DB to establish a connection with a Microsoft SQL Server database;
3． How to retrieve data from database by creating Recordset object.