Contents Index Topic Contents |
Open and Close Methods Example
This example uses the Open and Close methods on both Recordset and Connection objects that have been opened.
Public Sub OpenX() Dim cnn1 As ADODB.Connection Dim rstEmployees As ADODB.Recordset Dim strCnn As String Dim varDate As Variant ' Open connection. strCnn = "driver={SQL Server};server=srv;" & _ "uid=sa;pwd=;database=pubs" Set cnn1 = New ADODB.Connection cnn1.Open strCnn ' Open employee table. Set rstEmployees = New ADODB.Recordset rstEmployees.CursorType = adOpenKeyset rstEmployees.LockType = adLockOptimistic rstEmployees.Open "employee", cnn1, , , adCmdTable ' Assign the first employee record's hire date ' to a variable, then change the hire date. 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 ' Requery Recordset and reset the hire date. 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.Close End SubVBScript Version
The following is the same example written in VBScript to be used in an Active Server Page (ASP). To view this example, you need to create a system Data Source Name (DSN) called AdvWorks for the Microsoft Access database file AdvWorks.mdb, which is installed with Microsoft Internet Information Server and located at C:\InetPub\ASPSamp\AdvWorks. Locate the file adovbs.inc and place it in the directory you plan to use. Cut and paste the following code to Notepad or another text editor and save it as ADOOpen.asp. You can view the result in any client browser.
<!-- #Include file="ADOVBS.INC" --> <HTML><HEAD> <TITLE>ADO 1.5 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> <!--- ADO Connection used to create 2 recordsets--> <% Set OBJdbConnection = Server.CreateObject("ADODB.Connection") OBJdbConnection.Open "AdvWorks" SQLQuery = "SELECT * FROM Customers" 'First Recordset RSCustomerList Set RSCustomerList = OBJdbConnection.Execute(SQLQuery) 'Second Recordset RsProductist Set RsProductList = Server.CreateObject("ADODB.Recordset") RsProductList.CursorType = adOpenDynamic RsProductList.LockType = adLockOptimistic RsProductList.Open "Products", OBJdbConnection %> <TABLE COLSPAN=8 CELLPADDING=5 BORDER=0> <!-- BEGIN column header row for Customer Table--> <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> <!--Display ADO Data from Customer Table--> <% 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 and add to html table--> <% RScustomerList.MoveNext Loop RScustomerList.Close OBJdbConnection.Close %> </TABLE> <HR> <TABLE COLSPAN=8 CELLPADDING=5 BORDER=0> <!-- BEGIN column header row for Product List Table--> <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> <!-- Display 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 'Remove Objects from Memory Freeing Set RsProductList = Nothing Set OBJdbConnection = Nothing %> </TABLE></FONT></Center></BODY></HTML>
Top of Page
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.