What is ADO? - Examples 1-3

(Page 9 of 12 )

Because this is primarily a reference book, I’ll give you a few documented samples of code so that you can see what’s possible and how some of the components fit together. You can use this as a sort of “table of contents” to the rest of the book, where the various aspects are covered in more detail. A later section of this chapter provide more detail about language specifics, so I use Visual Basic here because it’s easy to read and understand.

Example 1

The following section of Visual Basic code creates a connection to SQL Server, opens a recordset, and then adds some details from the recordset to a listbox:

' Define two object variables
' The object model is discussed in Chapter 2
Dim objConn As ADODB.Connection
Dim objRs As ADODB.Recordset
Set objConn = New ADODB.Connection
Set objRs As New ADODB.Recordset

' Open a connection to the pubs database using the
' SQL Server OLE DB Provider
' Connection strings are discussed in Chapter 2
' The Connection object is discussed in Chapter 3
objConn.Open "Provider=SQLOLE DB; Data Source=Tigger; " &_
             "Initial Catalog=pubs; User Id=sa; Password="

' Open a recordset on the 'authors' table
' The Recordset is discussed in Chapter 5
objRs.Open "authors", objConn, adOpenForwardOnly, _ 
            adLockReadOnly, adCmdTable

' Loop while we haven't reached the end of the recordset
' The EOF property is set to True when we reach the end
While Not objRs.EOF
   ' Add the names to the listbox, using the default Fields collection
   ' The Fields collection is discussed in Chapter 6
   List1.AddItem objRs.Fields("au_fname") & " " & _
                 objRs.Fields("au_lname")
   ' Move to the next record
   objRs.MoveNext
Wend

' Close the objects
objRs.Close
objConn.Close

' Release the memory associated with the objects
Set objRs = Nothing
Set objConn = Nothing  

Example 2

This example runs a SQL statement and doesn’t expect a set of records to be returned. It also uses an ODBC Data Source Name called pubs, which has been set up previously.

' Define the object variable
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection

' Open a connection to the pubs database using the
' OLE DB Provider for ODBC
objConn.Open "DSN=pubs; UID=sa; PWD="

' Run a SQL UPDATE statement to update book prices by 10%
objConn.Execute "UPDATE titles SET price = price * 1.10" _
        , , adExecuteNoRecords + adCmdText

' Close the connection
objConn.Close

' Release the memory associated with the objects
Set objConn = Nothing

Example 3

This example runs a stored query in Access, passing in some parameters:

' Declare the object variables
' The Command object is discussed in Chapter 4
' The Parameter object is discussed in Chapter 4
Dim objConn     As ADODB.Connection
Dim objCmd      As ADODB.Command
Dim objRs        As ADODB.Recordset
Dim objParam    As ADODB.Parameter
Set objConn = New ADODB.Connection
Set objCmd = New ADODB.Command

' Open a connection to an Access pubs database,
' using the Access OLE DB provider
objConn.Open "Provider=Microsoft.Jet.OLE DB.4.0; " & _
        "Data Source=C:\temp\pubs.mdb" 

' Create a new Parameter, called RequiredState
Set objParam = objCmd.CreateParameter("RequiredState", _
               adChar, _ adParamInput, 2, "CA")

' Add the Parameter to the Parameters collection of the Command object
objCmd.Parameters.Append objParam

' Set the active connection of the command to the open Connection object
Set objCmd.ActiveConnection = objConn

' Set the name of the stored query that is to be run
objCmd.CommandText = "qryAuthorsBooksByState"

' Set the type of command
objCmd.CommandType = adCmdStoredProc

' Run the stored query and set the recordset which it returns
Set objRs = objCmd.Execute

' Loop through the recordset, adding the items to a listbox
While Not objRs.EOF
     List1.AddItem objRs.Fields("au_fname") & " " & _ 
                      objRs.Fields("au_lname") & _ 
                      ": " & objRs.Fields("title")
     objRs.MoveNext
Wend

' Close the recordset and connection
objRs.Close
objConn.Close

' Reclaim the memory from the objects
Set objRs = Nothing
Set objCmd = Nothing
Set objParam = Nothing
Set objConn = Nothing

 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

Next: Examples 4-6 >>
 

More ASP.NET Articles
More By Apress Publishing

Comments On: What is ADO?

>>> Be the FIRST to comment on this article!