Open 和 Close 方法范例

该范例使用已经打开的 Recordset 和 Connection 对象的 Open 和 Close 方法。

Public Sub OpenX()   Dim cnn1 As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String
   Dim varDate As Variant   ' 打开连接。
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn
   
   ' 打开雇员表。
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.Open "employee", cnn1, , , adCmdTable   ' 将第一个雇员记录的受雇日期赋值给变量，然后更改受雇日期。
   varDate = rstEmployees!hire_date
   Debug.Print "Original data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fName & " " & _
      rstEmployees!lName & " - " & rstEmployees!hire_date
   rstEmployees!hire_date = #1/1/1900#
   rstEmployees.Update
   Debug.Print "Changed data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fName & " " & _
      rstEmployees!lName & " - " & rstEmployees!hire_date   ' 再查询 Recordset 并重置受雇日期。
   rstEmployees.Requery
   rstEmployees!hire_date = varDate
   rstEmployees.Update
   Debug.Print "Data after reset"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fName & " " & _
      rstEmployees!lName & " - " & rstEmployees!hire_date   rstEmployees.Close
   cnn1.CloseEnd Sub

VBScript 版本

下面是使用 VBScript 编写、并用于 Active Server Page (ASP) 的相同范例。如需查看该完整功能范例，请使用与 IIS 一同安装并位于 C:\InetPub\ASPSamp\AdvWorks 的数据源 AdvWorks.mdb，来创建名为 AdvWorks 的系统“数据源名称”(DSN)。这是 Microsoft Access 数据库文件。请使用查找命令定位文件 Adovbs.inc，并将其放入计划使用的目录中。请将以下代码剪切并粘贴到记事本或其他文本编辑器中，另存为“ADOOpen.asp”。这样，便可在任何客户端浏览器中查看结果。

<!-- #Include file="ADOVBS.INC" -->
<HTML><HEAD>
<TITLE>ADO Open Method</TITLE>
</HEAD><BODY> 
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Open Method</H3>
<TABLE WIDTH=600 BORDER=0>
<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>
<!--- 用于创建 2 个记录集的 ADO 连接 -->
<% 
Set OBJdbConnection = Server.CreateObject("ADODB.Connection") 
OBJdbConnection.Open "AdvWorks" 
SQLQuery = "SELECT * FROM Customers" 
' 第一个记录集 RSCustomerList
Set RSCustomerList = OBJdbConnection.Execute(SQLQuery) 
' 第二个记录集 RsProductist
Set RsProductList = Server.CreateObject("ADODB.Recordset")
RsProductList.CursorType = adOpenDynamic
RsProductList.LockType = adLockOptimistic
RsProductList.Open "Products", OBJdbConnection 
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0><!-- Customer 表的 BEGIN 列标头行 --><TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail address</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT></TD></TR><!-- 显示 Customer 表的 ADO 数据 -->
<% Do While Not RScustomerList.EOF %>
  <TR><TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RSCustomerList("CompanyName")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RScustomerList("ContactLastName") & ", " %> 
  <%= RScustomerList("ContactFirstName") %> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RScustomerList("ContactLastName")%> 
 </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RScustomerList("City")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RScustomerList("StateOrProvince")%> 
  </FONT></TD></TR> 
<!-Next Row = Record Loop 并添加到 html 表 -->
<% 
RScustomerList.MoveNext 
Loop 
RScustomerList.Close
OBJdbConnection.Close
%>
</TABLE>
<HR>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0><!-- Product List 表的 BEGIN 列标头行 --><TR><TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Type</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Description</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit Price</FONT></TD></TR>
<!-- 显示 ADO Data 的 Product List -->
<% Do While Not RsProductList.EOF %>
  <TR>  <TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RsProductList("ProductType")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RsProductList("ProductName")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
   <%= RsProductList("ProductDescription")%> 
 </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RsProductList("UnitPrice")%> 
  </FONT></TD>
  
<!--  Next Row = Record -->
<% 
RsProductList.MoveNext 
Loop 
' 从 Memory Freeing 删除对象。 
Set RsProductList = Nothing
Set OBJdbConnection = Nothing
%>
</TABLE></FONT></Center></BODY></HTML>
www.holmesian.org