ASP and Database (i)

In the previous talk, we have learned the basic content of ASP, flexible use of this knowledge, you can use ASP to build a Web site. But in general, a real, the complete site is inseparable from the database, because a small amount of data, such as the number of visitors to the Web page can be stored in a text file, but the actual application, the need to save more than this point, and there are often links between these data, using the database to manage the data, can be very convenient to query and update. There are many types of databases, such as the Fox database (. dbf), Access database (. mdb), Informix, Oracle, and SQL Server, and so on, in this lecture, I will take the Microsoft Access database and SQL Server 7.0 is an example to illustrate how the ASP accesses the database. Why do you choose ASP? Statistics show that SQL Server 7.0 is the fastest, most cost-effective database in the current, while Www.8848.net, www.dell.com, Www.hotbot.com and many other large Web sites are created using ASP technology, so the ASP is relatively easy to master and very practical technology, you should be confident and courageous to try to approach it, understand it, and then use it.
What to do before you study further:
1. If you have not contacted the database, it is recommended that you install Microsoft Access in office to understand the basics of the database.
2. It is a good idea to install Microsoft SQL Server 7.0. Once installed, you can use it immediately, do not need any settings, note that the desktop version should be installed in windows9.x, and the previous version of 7.0 can only be installed in NT Server.
3. What follows is a component called MDAC (Microsoft Data Access components) that is already included in the ASP, and if you want to learn more about it or download the latest version, you can access the http:// Www.microsoft.com/data.

Establish a connection with the database
Before the formal start, introduce the Ado--activex Data Objects, just contact this concept, you just think of ADO as an ASP and the bridge between the database can be.
(i) Establishing a connection with a Microsoft Access database
Method One: Example wuf40.asp
<% @LANGUAGE = VBScript%>
<% ' wuf40.asp
Option Explicit
Dim Cnn, strcnn
' 1-Create a 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 Connection object's Open method
Cnn.open strcnn

Response.Write Server.MapPath ("\asp") & "\northwind.mdb;" & "<br>"
Response.Write "Database connection succeeded:" & cnn.state & "<br>"
' 3-Closes the connection using the Close method of the Connection object
Cnn.close
Response.Write "Database is closed:" & Cnn.state
' 4-Remove the connection object from memory to release the test? Br>set Cnn = Nothing
%>
Northwind.mdb is a sample database from Microsoft ACCESS97, typically located under the directory "Microsoft Office\Office\Samples", and you can find it yourself. Then copy the file to the appropriate directory on the Web server (in this case, under C:\InetPub\home\asp).
Here, the MapPath method of the server object converts the specified virtual path to a true file path, and the end result is similar to the following: C:\InetPub\home\asp\Northwind.mdb.
Method Two: Create a Microsoft Access connection with OLE DB, and the key sections are listed below.
Set Cnn = Server.CreateObject ("ADODB.") Connection ")
strcnn = "Provider = microsoft.jet.oledb.4.0; Data Source = C:\Inetpub\home\asp\Northwind.mdb "
Cnn.open strcnn

(ii) Establishing a connection with a SQL Server database
Method one: Connect SQL Server with OLE DB, as described in 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
Initial Catalog Specify the database name after data source is the machine name (that is, the network → attribute → The computer name in the identity) or the IP address (the DNS service is required to install).
Method Two:
Set Cnn = Server.CreateObject ("ADODB.") Connection ")
strcnn = "Driver={sql Server}"; SERVER=ICBCZJP; Uid=sa; pwd=;D Atabase=pubs "
Cnn.open strcnn

In addition to the two methods mentioned above, you can certainly use the most classic methods described in many articles and materials: using ODBC to generate connections. is to first establish a connection in the "ODBC Data Sources" of the Web server Control Panel, and then use a similar "STRCNN =" dsn=adocnn in the ASP; UID = sa; Pwd= the connection string;D atabase=pubs "". Not in detail here, this is not the best way, and the other data are introduced in this method.
Also, be interested in downloading routine wuf42.asp to see how to use the ConnectionTimeout and ConnectionString properties of the Connection object.

Second, the database starts from here--retrieves the data that already exists in the database
Now that you have the most basic database knowledge, you know how to use Microsoft Access to open the Shippers table in Northwind.mdb to see a few of the data stored in the table. The question now is how to use ASP to display this data in the browser, and here are three methods.
Special reminder: If your database base is thin, as long as you will use the second method on the line, must not try, lest the obsession, remember! Remember!

Method One: Use only the Connection object. Case wuf43.asp
<% @LANGUAGE = VBScript%>
<% ' wuf43.asp
Option Explicit
' This sentence is very important to ensure that the data you see is not cached in the client's data,
' But the latest data that the server has updated at any time
Response.Expires = 0

' Part One: establishing a connection

Dim Cnn, strcnn
Set Cnn = Server.CreateObject ("ADODB.") Connection ")
strcnn = "Provider = microsoft.jet.oledb.4.0; Data Source = C:\Inetpub\home\asp\Northwind.mdb "
Cnn.open strcnn

' Part two: Using the Connection object's Execute method to get the recordset

Dim strSQL, Rstest
' The following is a SQL statement (Structured query statement), which is not specifically described in this lecture
' Suggest looking for a book to see, basic use or relatively simple, a study will
' The meaning of this sentence is to select all the data from the shipping list
strSQL = "SELECT * from Freight forwarders"
Set rstest = Cnn.execute (strSQL)
%>
<HTML>
<BODY>
<% ' Part III: Display the resulting recordset to the browser

' Loop to the end of the record-one row, one record for a record ' display
Do as not rstest.eof

' The following two lines function the same, that is, rstest ("Freight forwarder ID") is equivalent to Rstest (0)
Response.Write rstest ("Freight forwarder ID") & "" & Rstest ("Company name") & "& Rstest (" tel ") &" & "&LT;BR&G t; "
' Response.Write rstest (0) & "& Rstest (1) &" & Rstest (2) & "" & "<BR>"

' Move to the next record--this sentence must not be less or fall into the cycle of death
Rstest.movenext
Loop

' Part four: cleaning the battlefield
Cnn.close
Set rstest = Nothing:set Cnn = Nothing
%>
</BODY>
</HTML>
This example is very simple, the third part of the display data is a typical database output style, you can refer to the previous knowledge of the table and color to beautify the output results.
Non-novice users can refer to wuf44.asp to see the full use of the Execute method.

Method Two: By creating a Recordset object--it is enough to remember that beginners only have to master this method.
Example: Wuf45.asp, the other part with wuf43.asp, the key is the second part of the program.
' Part two: Getting a Recordset by creating a Recordset object
Dim strSQL, Rstest
' Create a Recordset object
Set rstest = Server.CreateObject ("ADODB. Recordset ")

strSQL = "Select Forwarder ID, telephone, company name from shippers Where phone = ' (503) 555-9931 '"
' Attach a Recordset object to the connection Cnn
Set rstest.activeconnection = Cnn
' Open a Recordset with the Recordset object's Open method
Rstest.open strSQL
Now let's take a look at the complete use of the Recordset object open method, example wuf48.asp.
<% @LANGUAGE = VBScript%>
<% ' wuf48.asp
Option Explicit
Response.Expires = 0
%>
<!--#include file= "Adovbs.inc"-->
<%
' Part One: establishing a connection
Dim Cnn, strcnn
Set Cnn = Server.CreateObject ("ADODB.") Connection ")
strcnn = "Provider = microsoft.jet.oledb.4.0; Data Source = C:\Inetpub\home\asp\Northwind.mdb "
Cnn.open strcnn

' Part two: Getting a Recordset by creating a Recordset object
Dim Rstest
Set rstest = Server.CreateObject ("ADODB. Recordset ")

Rstest.open "Freight Forwarders", cnn,adopenforwardonly,adlockreadonly,adcmdtable
%>
<HTML>
<BODY>
<% ' Part III: Display the resulting recordset to the browser
Do as not rstest.eof
Response.Write rstest (0) & "" & Rstest (1) & "& Rstest (2) &" "&" <BR> "
Rstest.movenext
Loop

' Part four: cleaning the battlefield
RsTest.Close:Cnn.Close
Set rstest = Nothing:set Cnn = Nothing
%>
</BODY>
</HTML>
Analysis:
1. First look at the key sentence:
Rstest.open "Freight Forwarders", cnn,adopenforwardonly,adlockreadonly,adcmdtable
The first parameter can be a table name (such as a freight forwarder) or an SQL statement (such as wuf45.asp).
The second parameter specifies the current connection.
The third parameter indicates CursorType, which determines the type of cursor that the provider should use when opening the Recordset. This takes a forward-only cursor.
The fourth parameter indicates LockType, which determines the type of lock (concurrency) that the provider should use when opening the Recordset. This is specified as read-only.
The fifth parameter is related to the first parameter, and if the first argument in this example is the table name, then the fifth parameter uses adCmdTable, and the first argument is adCmdText, if it is an SQL statement, for example:
Rstest.open "SELECT * from Shippers", Cnn,adopenforwardonly,adlockreadonly,adcmdtext
I do not think that large people can really understand the meaning of these five parameters, it does not matter, just copy will be used on the line, we will continue to contact, cooked will be able to make a coincidence.
The two parameters, CursorType and LockType, will be described in detail, and only the data can be retrieved in this example.
2. These five parameters can also be set using the properties of the Recordset object, as in example wuf46.asp.
' Part two: Getting a 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 "Freight Forwarders",,,, adCmdTable
3. Then look at this sentence:<!--#include file= "Adovbs.inc"-->.
(1) Because of the use of constants such as Adopenforwardonly,adlockreadonly,adcmdtext, it is necessary to include adovbs.inc in this file.
(2) Adovbs.inc This file is generally located under the directory Program Files\Common Files\System\ado, you can copy it to the Web server in the current directory (this is the download package also has the file).
(3) Use Notepad to open this file to see why you need this file.

Method Three: By introducing the Command object--This should be done on the premise that you have the ability.
<% @LANGUAGE = VBScript%>
<% ' wuf47.asp
Option Explicit
Response.Expires = 0
%>
<!--#include file= "Adovbs.inc"-->
<%
' Part One: establishing a connection
Dim Cnn, strcnn
Set Cnn = Server.CreateObject ("ADODB.") Connection ")
strcnn = "Provider = microsoft.jet.oledb.4.0; Data Source = C:\Inetpub\home\asp\Northwind.mdb "
Cnn.open strcnn

' Part Two: Get a 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 an 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 III: Display the resulting recordset to the browser

Do as not rstest.eof
Response.Write rstest (0) & "<BR>"
Rstest.movenext
Loop

' Part four: cleaning the battlefield

RsTest.Close:Cnn.Close
Set rstest = nothing:set Cmdtest=nothing:set Cnn = Nothing
%>
</BODY>
</HTML>
The use of command objects will also be highlighted in the in-depth programming of the database.

This talk mainly introduces how to connect with the database and retrieve database data, beginners will inevitably a bit 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 a database by creating a Recordset object.