ASP Lecture 8: ASP and Database (3)

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

In the last two lectures, we explained the basic use of databases in ASP. Today we will introduce several very practical technologies.

1. Paging technology We have introduced how to retrieve data and output it to the browser. For a small amount of data, such simple output processing is completely acceptable. However, if the amount of data is large, with hundreds or even thousands of items, It is unrealistic to output so much data to the client at one time. Firstly, the page stretches very long from top to bottom. Secondly, the client waits too long. Thirdly, the load on the server is too heavy. So it is very necessary to take paged output.
Requirement: Output the data in the "Product" table of Northwind.mdb to the browser, and display 10 items on each page.
Take wuf60.asp as an example. This code is still a bit difficult. You need to read more and experience it more. AdoAccess.asp was mentioned in the lecture.
Note: This routine incorporates good parts from some books, and hereby declares.
<%@ LANGUAGE="VBSCRIPT" %>


<%
Dim RecordPerPage, absPageNum, TotalPages, absRecordNum, rsTest, StrSQL
'absPageNum - What page is the current page?
'TotalPages - Total number of pages
'absRecordNum - the serial number of a record in the current page, such as 1-10

RecordPerPage = 10 'Number of records displayed per page

' Get the current page number of the output data
If Request.ServerVariables("CONTENT_LENGTH") = 0 Then
'If the data submitted by the form is not received (such as when the page is loaded for the first time), it will be displayed from page 1
absPageNum = 1
Else
'Get the page number when pressing the button
absPageNum = CInt(Request.Form("PressPageNum"))
'If you press the previous page, the page number is -1, if you press the next page, the page number +1
If Request.Form("Submit") = "Previous page" Then
absPageNum = absPageNum - 1
ElseIf Request.Form("Submit") = "Next page" Then
absPageNum = absPageNum + 1
End If
End If

'Create recordset object
Set rsTest = Server.CreateObject("ADODB.Recordset")

rsTest.CursorLocation = adUseClient 'This setting can reduce the database load
rsTest.CursorType = adOpenStatic 'The cursor needs to move forward and backward and cannot be set to forward only
rsTest.CacheSize = RecordPerPage 'Setting this option will improve performance

StrSQL = "SELECT * FROM productOrder By productID"
rsTest.Open StrSQL, Cnn, , , adCmdText

rsTest.PageSize = RecordPerPage 'Set the number of records per page

If Not(rsTest.EOF) Then
rsTest.AbsolutePage = absPageNum
End If

TotalPages = rsTest.PageCount
%>

<% 'The following part outputs the data of the current page to the browser%>
<Html><Boby>
<table colspan=8 cellpadding=5 border=0>
<tr>
<td align=CENTER bgcolor="#800000" width="109"> <font style="ARIAL NARROW" color="#ffffff" size="2">Unit price</font></td>
<td align=CENTER width=459 bgcolor="#800000"> <font style="ARIAL NARROW" color="#ffffff" size="2">Product name</font></td>
</tr>
<% 'Use a loop to output 10 pieces of data on the current page
For absRecordNum = 1 to rsTest.PageSize
%>
<tr>
<td bgcolor="f7efde" align=CENTER> <font style="ARIAL NARROW" size="2"><%= rsTest("unit price")%></font></td>
<td bgcolor="f7efde" align=CENTER> <font style="ARIAL NARROW" size="2"><%= rsTest("Product Name")%></font></td>
</tr>
<%
rsTest.MoveNext
If rsTest.EOF Then
Exit For ' If the end of the record has been reached, exit - if the last page of data is not full.
End If
Next

rsTest.Close : Cnn.Close
Set rsTest = Nothing : Set Cnn = Nothing
%>
</table>

<% 'The lower part is two buttons "Previous Page" "Next Page" %>
<Form Action = "<%= Request.ServerVariables("SCRIPT_NAME") %>" Method="Post">
<Input Type="Hidden" Name="PressPageNum" Value="<%= absPageNum%>">
<%
If absPageNum > 1 Then 'If the current page is not the first page, display the previous page button%>
<Input Type="Submit" Name="Submit" Value="Previous Page">
<% End If
If absPageNum <> TotalPages Then 'If the current page is not the last page, display the next page button%>
<Input Type="Submit" Name="Submit" Value="Next page">
<% End If %>
</Form>
<P><Center> [Page<font color="#CC0033"><%= absPageNum %></font>,
Total<font color="#CC0033"><%= TotalPages %></font> pages] </Center></P>
</BODY></HTML>
analyze:
1. Some useful properties of the Recordset object:
l rsTest.CursorLocation = adUseClient: You can also omit this sentence, but doing so can reduce the database load;
l rsTest.CacheSize = RecordPerPage: The CacheSize attribute is used to determine how much data the client obtains from the database server each time;
l rsTest.PageSize: The PageSize attribute is used to set the number of records on each page;
l rsTest.AbsolutePage: The AbsolutePage property sets the absolute number of pages of the current data in the Recordset object;
l rsTest.PageCount: The PageCount property is used to obtain the total number of pages in the record set.
2. This example Form uses a hidden field PressPageNum to pass the page when the button is clicked.

2. Error handling During the execution of the code, errors may occur due to various reasons, such as: problems with the code itself, network disconnection, etc., so it is very necessary to set up error capture and processing in the program. In ASP, we can obtain the error or warning information that occurs when the code is running through the Errors data collection of the Connection object. The usage method is as follows:
1. Use it directly on the Connection object:
Set Errs = Cnn.Errors
or
Cnn.Errors
2. After establishing the Recordset object or Command object, use the Connection object through its ActiveConnection property:
Set Errs = rsTest.ActiveConnection.Errors
or
rsTest.ActiveConnection.Errors
It sounds too crude to say, so let’s give an example: wuf61.asp
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>

<%
Response.Expires = 0
'The following sentence guarantees: Even if the script encounters an error, it will continue to execute the next sentence
On Error Resume Next

Dim Cnn, rsTest, Errs, I
Set Cnn = Server.CreateObject("ADODB.Connection")
'CommandTimeout - the maximum waiting time for connecting to the database, the default is 15 seconds
Cnn.CommandTimeout = 5
'You can detect errors in the following three situations - taking SQL Server as an example
'1 - completely correct; 2 - initial database is not set; 3 - database name is wrongly pvbs

Cnn.Open "Provider=sqloledb; User ID=sa; Password=; Initial Catalog=pubs; Data Source=ICBCZJP"
'Cnn.Open "Provider=sqloledb; User ID=sa; Password=; Initial Catalog=; Data Source=ICBCZJP"
'Cnn.Open "Provider=sqloledb; User ID=sa; Password=; Initial Catalog=pvbs; Data Source=ICBCZJP"

For I = 0 To Cnn.Errors.Count - 1
'Source attribute indicates the source of the error
Response.Write "[ " & Cnn.Errors(I).Source & " ] "
'Description attribute indicates the reason or description of the error
Response.Write Cnn.Errors(I).Description & "<br>"
Next

If Cnn.Errors.Count > 0 Then
Response.Write "Occurred while connecting" & Cnn.Errors.Count & " errors" & "<br>"
End If

Set rsTest = Server.CreateObject("ADODB.Recordset")
rsTest.Open "jobs",Cnn,adOpenForwardOnly,adLockReadOnly,adCmdTable

If rsTest.ActiveConnection.Errors.Count > 0 Then
Set Session("Errs") = rsTest.ActiveConnection.Errors
Response.Redirect "ErrorHandle.asp"
End If

Cnn.Close
Set rsTest = Nothing : Set Cnn = Nothing
%>
ErrorHandle.asp code:
<%
Dim I
For I = 0 To Session("Errs").Count - 1
Response.Write "[ " & Session("Errs")(I).Source & " ] "
Response.Write Session("Errs")(I).Description & "<br>"
Next
%>
analyze:
In this case, the error may have occurred while connecting, or the connection may have been correct, but an error occurred while using the Recordset object.
In addition, in the following piece of code, the error collection is put into a session object so that it can be called between pages (when an error is encountered, turn to the error handling page ErrorHandle.asp).
In fact, you can also assign the Recordset object to the Session object to implement the call of the recordset between pages.

3. The concept of using transactions is very simple and important. In order to illustrate its use, let us first assume the following situation: For example, in e-commerce, when conducting currency transfers online, a certain amount must be subtracted from an account. amount and add its equivalent amount to another account. No matter which one of the updates fails, it will lead to an imbalance in the balance of the account (either there is a deduction here, but there is no increase there; or there is no deduction here, but there is an increase there). If you use a transaction to make these changes, you ensure that you can only choose to make all changes or make no changes (either completely correct or completely canceled).
Transactions belong to the Connection object, which has three transaction-related methods:
l BeginTrans starts a new transaction.
l CommitTrans saves all changes and ends the current transaction.
l RollbackTrans cancels any changes made in the current transaction and ends the transaction, often called "rollback".
We might as well look at an example wuf62.asp.
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>

<%
Response.Expires = 0
On Error Resume Next

DimCnn, StrSQL, rsTest
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.Open "Provider=sqloledb; User ID=sa; Password=; Initial Catalog=pubs; Data Source=ICBCZJP"

'Start a transaction
Cnn.BeginTrans
StrSQL = "Insert jobs(job_desc, min_lvl, max_lvl) Values('Finance',16,86)"
Cnn.Execute StrSQL

'The first sentence below is wrong, the second sentence is correct
StrSQL = "Update jobs_err SET job_desc = 'Transaction' Where job_id = 14"
'StrSQL = "Update jobs SET job_desc = 'Transaction' Where job_id = 14"
Cnn.Execute StrSQL

If Cnn.Errors.Count > 0 Then
Response.Write "An error occurred, the system restores the state at the beginning of the transaction, neither new additions nor modifications will be made" & "<br>"
Cnn.RollbackTrans
Else
Response.Write "No errors, save changes to the database, add a new piece of data, modify a piece of data" & "<br>"
Cnn.CommitTrans
End If

Set rsTest = Cnn.Execute("Select * From jobs where job_id>=14")
While Not rsTest.EOF
Response.Write rstest(0) & rstest(1) & rstest(2) & rstest(3) & "<br>"
rsTest.MoveNext
Wend

'This example is only for testing, so restore the original data of the database
Cnn.Execute "Update jobs SET job_desc = 'Designer' Where job_id = 14"
Cnn.Execute "DELETE jobs Where job_id > 14"

Cnn.Close: Set Cnn = Nothing
%>
In this example, Insert and Update either occur at the same time or neither occurs. There will be no situation where a piece of data is added but the modification does not occur due to a statement error. Using transactions is a very good habit when programming databases.

4. Processing of multiple record sets Sometimes we need to obtain data from two tables at the same time. If returned in one SQL statement, network transmission can be reduced and operating efficiency improved.
Take wuf64.asp as an example. This example also explains how to use a loop to output field values (in the past, we used stupid methods like "rsTest(0) & rsTest(1) &..." to output. If there are only two or three fields , this method is obviously more concise), if you can’t understand it at the moment, please download the simpler wuf63.asp, remember! .
<%@ LANGUAGE="VBSCRIPT" %>
<%
Option Explicit
Response.Expires = 0

Dim Cnn, StrSQL, rsTest, I
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.Open "Provider=sqloledb; User ID=sa; Password=; Initial Catalog=pubs; Data Source=ICBCZJP"

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

'Retrieve multiple recordsets
StrSQL = "Select COUNT(*) AS 'Number of employees' From employee; Select * From jobs"
rsTest.Open StrSQL, Cnn ', , ,adCmdText

While Not rsTest Is Nothing
Response.Write "<Table Border = 2><tr>"

'rsTest.Fields.Count - Number of fields in the recordset
For I = 0 To rsTest.Fields.Count - 1
'rsTest(I).Name - the field name of the I field
Response.Write "<td>" & rsTest(I).Name & "</td>"
Next
Response.Write "</tr>"

While Not rsTest.EOF
Response.Write "<tr>"
'Use a loop to output the value of each field
For I = 0 To rsTest.Fields.Count - 1
Response.Write "<td>" & rsTest(I) & "</td>"
Next
Response.Write "</tr>"
rsTest.MoveNext
Wend

'Read the next Recordset object
Set rsTest = rsTest.NextRecordset
Wend

Cnn.Close
Set rsTest = Nothing: Set Cnn = Nothing
%>
Note: The SQL Server database supports multiple record sets, but the Access database does not.

5. Close the connection as early as possible to release resources
In the previous examples, the connection was closed last. However, the Connection object takes up resources. In fact, according to the method provided by wuf65.asp below, the connection can be closed earlier.
<% @LANGUAGE = VBScript %>


<% ' wuf65.asp
Dim StrSQL, rsTest

StrSQL = "Select * From shipper"
Set rsTest = server.CreateObject("ADODB.Recordset")
'You must use a client cursor, otherwise it won't work
rsTest.CursorLocation = adUseClient
rsTest.Open StrSQL,Cnn,,,adCmdText

'Remove the recordset's dependence on the Connection object
Set rsTest.ActiveConnection = Nothing
'Close the connection as early as possible
Cnn.close: Set Cnn = Nothing

Do While Not rsTest.EOF
Response.Write rsTest(0) & " " & rsTest(1) & " " & rsTest(2) & " " & "<BR>"
rsTest.MoveNext
Loop

Set rsTest = Nothing
%>