When we use ASP's built-in ADO component for database programming, we usually open a connection at the beginning of the script and close it at the end of the script. However, for larger scripts, in most cases the connection opening time is longer than that. It takes much longer to open. Therefore, in order to save server resources, the connection should be closed as much as possible to release the resources occupied by the connection. This technique of closing the connection of the record set without closing the record set is called a disconnected record set, and the record set itself is called a disconnected record. set.
Below we will use an example to illustrate the use of this technology (NorthWind.mdb is a database that comes with Microsoft Access97, and the file adovbs.inc can be found under C:Program FilesCommon FilesSystemADO):
＜ % @LANGUAGE= VBScript %＞
＜!--#includefile="adovbs.inc"--＞
＜%
Response.Expires = 0
Dim Cnn,objRS, strOut, strQ, strC
StrC= "Driver={Microsoft Access Driver (* .mdb)}; DBQ=" & Server.MapPath("asp24") & "NorthWind.mdb;"
'Establish a connection
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.Open StrC
'Create a Recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation =adUseClient
objRS.CursorType = adOpenStatic
objRS.LockType = adLockOptimistic
strQ = "SELECT carrier ID, company name, phone FROM carrier"
objRS.Open strQ , Cnn, , , adCmdText
Set objRS.ActiveConnection = Nothing 'Disconnect the record set
Cnn.Close 'Close the connection
Set Cnn = Nothing
Response.Write "＜HTML＞＜BODY＞"
'Use the disconnected record set
Do While (NotobjRS .EOF)
strOut = objRS("Shipper ID") & ", " & objRS("Company Name") & ", " & objRS("Telephone")
Response.Write Server.HTMLEncode(strOut) & "＜BR ＞"
objRS.MoveNext
Loop
Response.Write "＜BR＞Prepare to add or insert records: "
'If you need to update the database, re-establish the connection
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.Open strC
Set objRS.ActiveConnection = Cnn
objRS.Filter = "Company Name = 'Wu Feng'"
If objRS.EOF Then
objRS.AddNew
objRS("Company Name") = "Wu Feng"
objRS("Telephone") = "571-7227298"
objRS.Update
Response.Write "If the record that meets the condition does not exist, then add it. <BR>"
Else
objRS("Telephone") = "571-7227071"
Response.Write "If the record that meets the condition exists, then Update. ＜BR＞"
objRS.Update
End If
Set objRS.ActiveConnection = Nothing
Cnn.close
Set Cnn = Nothing
objRS.Close
Set objRS = Nothing
Response.Write "＜/BODY＞＜/HTML＞"
%＞