In the last two lectures, we explained the basic use of databases in ASP, and today we will introduce several very practical techniques.
First, paging technology
We described how to retrieve data and output it to the browser side. For a small amount of data, that simple output processing is completely OK, but if the data volume is very large, there are hundreds of or even thousands, once so much data output to the client is not realistic, one page from top to bottom pull very long, The client waits too long, and the load on the three server is too large. So it is necessary to take paging output.
Requirements: Output Northwind.mdb the data in the Products table to the browser, displaying 10 per page.
Example Wuf60.asp, this piece of code is still a bit difficult, want to see more experience, adoaccess.asp mentioned in the last lecture.
Note: This routine absorbs the good parts of some books and is hereby declared.
<%@ language= "VBSCRIPT"%>
<!--#include file= "adoaccess.asp"-->
<!--#include file= "Adovbs.inc"-->
<%
Dim recordperpage, Abspagenum, TotalPages, Absrecordnum, Rstest, strSQL
' Abspagenum-The current page is the first few pages
' TotalPages-Total number of pages
' Absrecordnum-The ordinal 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 you do not receive the data submitted by the form, such as when the page is first loaded, show from page 1th
Abspagenum = 1
Else
' Remove the page number when you press the button
Abspagenum = CInt (Request.Form ("Presspagenum"))
' If you press the previous page page number-1, press the next page, then page +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 a Recordset object
Set rstest = Server.CreateObject ("ADODB. Recordset ")
Rstest.cursorlocation = adUseClient ' This setting reduces database load
Rstest.cursortype = adOpenStatic ' cursor needs to move back and forth, cannot be set to forward only
Rstest.cachesize = Recordperpage ' Set this option will improve performance
strSQL = "SELECT * from product ORDER by Product ID"
Rstest.open strSQL, Cnn,,, adCmdText
Rstest.pagesize = Recordperpage ' Sets the number of records per page
If not (rstest.eof) Then
Rstest.absolutepage = Abspagenum
End If
TotalPages = Rstest.pagecount
%>
<% ' The following section outputs the current page data 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>
<% ' 10 data in the current page with a circular output
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 you have been to the end of the record, exit--such as when the last page of data is dissatisfied
End If
Next
RsTest.Close:Cnn.Close
Set rstest = Nothing:set Cnn = Nothing
%>
</table>
<% ' The following section is two buttons "prev" "Next"%>
<form Action = "<%= request.servervariables (" Script_name ")%>" method= "Post >
<input type= "Hidden" name= "Presspagenum" value= "<%= abspagenum%>" >
<%
If Abspagenum > 1 Then ' If the current is not the first page, the previous page button is displayed%>
<input type= "Submit" name= "Submit" value= "prev" >
<% End If
If Abspagenum <> totalpages Then ' If the current page is not the last page, the next page button is displayed%>
<input type= "Submit" name= "Submit" value= "next Page" >
<% End If%>
</Form>
<P><Center> [<font color= "#CC0033" ><%= abspagenum%></font> page,
Total <font color= "#CC0033" ><%= totalpages%></font> page] </Center></P>
</BODY></HTML>
Analysis:
1. Some useful properties for the Recordset object:
L rstest.cursorlocation = adUseClient: Also can not this sentence, but this can reduce the database load;
L rstest.cachesize = recordperpage:cachesize attribute is used to determine how much data each client obtains from the database server;
L The RsTest.PageSize:PageSize property is used to set the number of records per page;
The L RsTest.AbsolutePage:AbsolutePage property sets the absolute number of pages of the current data in the Recordset object;
The L RsTest.PageCount:PageCount property is used to get the total number of pages in the recordset.
2. In this example form, an implied field is used to presspagenum the page when the click button is passed.
Second, error handling
During code execution, errors can occur for a variety of reasons, such as a problem with the code itself, network disconnection, and so on, so it is necessary to set up error trapping and processing in your program. In ASP, we can use the connection object's errors data collection to get the error or warning information that occurs when the code is run, using the following methods:
1. Use directly on connection objects:
Set errs = cnn.errors
Or
Cnn.errors
2. After the Recordset object or Command object is established, the connection object is used by its ActiveConnection property:
Set errs = rsTest.ActiveConnection.Errors
Or
RsTest.ActiveConnection.Errors
It's too rough to say, give me an example: wuf61.asp
<%@ language= "VBSCRIPT"%>
<% Option Explicit%>
<!--#include file= "Adovbs.inc"-->
<%
Response.Expires = 0
' The following guarantee: Even if the script encounters an error, it continues to execute the next sentence
On Error Resume Next
Dim Cnn, Rstest, errs, I
Set Cnn = Server.CreateObject ("ADODB.") Connection ")
' CommandTimeout-the longest wait time to connect to the database, with a default of 15 seconds
Cnn.commandtimeout = 5
' You can detect errors in the following three cases-take SQL Server as an example
' 1-perfectly correct; 2-The initial database is not set; 3-database name mistaken for 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 property indicates the source of the error
Response.Write "[" & Cnn.errors (I). Source & "]"
' Description attribute indicates the cause or description of the error
Response.Write Cnn.errors (I). Description & "<br>"
Next
If Cnn.Errors.Count > 0 Then
Response.Write "occurs on Connection" & Cnn.Errors.Count & "Error" & "<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
%>
Analysis:
In this case, the error may occur at the time of the connection, or the connection may be correct, but an error occurred while using the Recordset object.
In addition, in a later piece of code, the error collection is placed in a session object to be invoked between pages (when an error is encountered, the error-handling page errorhandle.asp).
In fact, you can also fully assign a Recordset object to the session object to implement the call between the recordset and the page.
Iii. Use of services
The concept of a transaction is very simple and important, and in order to illustrate its usefulness, it is assumed that, for example, in electronic commerce, when currency transfers are made on the Internet, a certain amount must be subtracted from an account and the equivalent amount to be added to another account. Regardless of which update fails, it will result in an imbalance of account balance (either this side is deducted, there is no increase, or there is no button, there is an increase). If you use transactions to make these changes, you can make sure that you can only choose to make all of the changes or not make any changes (either to be fully executed or to be canceled altogether).
The transaction is subordinate to the connection object, and the connection object has three transaction-related methods:
L BeginTrans start 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 referred to as a rollback.
We might as well look at an example of wuf62.asp.
<%@ language= "VBSCRIPT"%>
<% Option Explicit%>
<!--#include file= "Adovbs.inc"-->
<%
Response.Expires = 0
On Error Resume Next
Dim Cnn, 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 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 occurs, the system restores the state at the start of the transaction, neither new nor modified & <br>"
Cnn.rollbacktrans
Else
Response.Write "No errors, save changes to the database, add a new piece of data, modify a 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 case is for testing only, 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 case, the new (Insert) and modify (Update) either occur at the same time, neither occurs, and no new data is added, but the modification is due to a statement error that does not occur. It is a good practice to use transactions in database programming.
Iv. Processing of multiple recordsets
Sometimes we need to get data from two tables at the same time, if we put it back in one SQL statement, we can reduce the network transmission and improve the efficiency of the operation.
For example wuf64.asp, this example also explains how to use the Circular output field value (which we used to do with "Rstest (0) & Rstest (1) & ..."), and if there are only two or three fields, this method is obviously more concise. If you do not understand, please download a simple 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 more than one recordset
strSQL = "SELECT count (*) as ' employee number ' from employee; SELECT * FROM Jobs "
Rstest.open strSQL, Cnn ',,, adCmdText
While not rstest are nothing
Response.Write "<table Border = 2><tr>"
' RsTest.Fields.Count-Number of recordset fields
For I = 0 to Rstest.fields.count-1
' Rstest (I). Name-field name of field I
Response.Write "<td>" & Rstest (I). Name & "</td>"
Next
Response.Write "</tr>"
While not rstest.eof
Response.Write "<tr>"
' Output the value of each field with a loop
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
%>
Description: The SQL Server database supports multiple recordsets, which are not supported by an Access database.
Close the connection early and release the resources
In previous cases, the connection was closed at the end, but the connection object took up resources, and in fact, the connection could be closed earlier by the method provided by the wuf65.asp below.
<% @LANGUAGE = VBScript%>
<!--#include file= "adoaccess.asp"-->
<!--#include file= "Adovbs.inc"-->
<% ' wuf65.asp
Dim strSQL, Rstest
strSQL = "SELECT * from Freight forwarders"
Set rstest = server. CreateObject ("ADODB.") Recordset ")
' Be sure to use a client cursor, otherwise not
Rstest.cursorlocation = adUseClient
Rstest.open strsql,cnn,,, adCmdText
' Delete recordset dependencies on Connection objects
Set rstest.activeconnection = Nothing
' Close the connection as early as possible
Cnn.close:Set Cnn = Nothing
Do as not rstest.eof
Response.Write rstest (0) & "" & Rstest (1) & "& Rstest (2) &" "&" <BR> "
Rstest.movenext
Loop
Set rstest = Nothing
%>
