Special Edition Using Visual Basic.NET

By Brian Siler, Jeff Spotts

ADO was introduced before the .NET initiative and has since become an essential part of many Web sites and Visual Basic applications. As with any external object, before you can use ADO you must add a reference to it, as shown in Figure 21.1.

Note

If you are developing a new application, ADO.NET (discussed in the next chapter) is the preferred method of data access in Visual Studio .NET.

Once you have added a reference to ADO, the objects that make up the ADO object model are available to use in your program. These objects are listed in Table 21.1.

Note

The reference Microsoft ActiveX Data Objects 2.x Library contains all the objects listed in Table 21.1. The reference Microsoft ActiveX Data Objects Recordset 2.x Library contains just the Recordset object, which we will discuss later in this chapter.

When creating the objects in Table 21.1, you can use the ADODB prefix, as in the following example:

Dim cn AS ADODB.Connection

In the following sections, you'll explore some of the properties, methods, and events of the ADO objects. Comprehensive documentation for ADO, as well as updates and examples, is available on the Web at www.microsoft.com/data/ado.

Using the Connection Object

Before you can manipulate data in a database, you have to establish a connection to the database. The Connection object provides a means to connect and disconnect from a database. The first step in opening a connection is to create a new instance of the ADODB.Connection object, as follows:

Dim cn As ADODB.Connection Set cn = New ADODB.Connection

After the object instance is created, all you have to do to establish a connection is provide the connection string and call the Open method. You can set the connection string in two ways, the first being to assign it to the ConnectionString property:

Dim strInfo As String strInfo = "User ID=sa;Password=elvis;Database=pubs;Server=bsw2k\NetSDK;_ Provider=SQLOLEDB" cn.ConnectionString = strInfo cn.Open

Note

Please remember when using the code samples from this book that you might need to adjust the computer name, database, and password contained in the sample connection strings to match your own configuration.

The code sample first assigns the connection string to a string variable and then sets the ConnectionString property to the value of that variable. However, you can also pass the variable or literal string to the Connection object when calling the Open method as follows:

cn.Open "UID=joe;PWD=;DATABASE=pubs;SERVER=bsw2k;DRIVER={SQL Server}"

Note

Some of the other ADO objects (such as the Command object) have a Connection property, which will accept either a connection string or a Connection object.

After you finish using a connection, call the Close method to disconnect from the database.

cn.Close

In the remainder of this section, we'll discuss connection strings in more detail and show how to use the Connection object to retrieve data and execute database commands.

Understanding Connection Strings

As you can see from the preceding example, a connection string can contain many different types of information, including user ID, password, and default database name. Notice that elements of the connection string are listed in name-value pairs separated by semicolons. When creating a connection string, you will generally specify the following types of information:

• The type of database you are connecting to; that is, SQL Server, Access, or Oracle. When making connections with ADO, you can specify the name of an ODBC driver by entering a Driver value, or the name of an OLE DB Provider by entering a value for Provider.

• Your sign-in information for the database (if necessary) specified by the User ID and Password values. (Note that UID and PWD also work as an alternative spelling.)

• The location of the database. This might include a Server and Database value for SQL Server, or just a filename for Microsoft Access.

Note

ADO.NET provides classes specifically designed to connect to a Microsoft SQL Server. When using ADO.NET SQL classes, you do not have to specify a Provider.

The following lines of code show some examples of ADO connection strings:

'Microsoft Access using OLEDB Provider for Jet "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=d:\temp\biblio.mdb" 'Access using ODBC Driver for Access "Driver=Microsoft Access Driver (*.mdb);DBQ=D:\temp\biblio.mdb" 'SQL Server Using OLEDB Provider for SQL "Provider=SQLOLEDB;Password=groovy;User ID=apowers;Server=SQLSRV1; _ Database=employee" 'SQL Server Using SQL ODBC Driver User ID=eisuser;PWD=jan96;DATABASE=devstats;SERVER=eisdb2;DRIVER={SQL Server}

Note

Your driver or provider may have additional connection string options. For example, the SQL Server driver can accept values to determine the type of network connection (TCP versus named pipes). To determine available connection options, see your database system help files.

One key issue when creating your connect string is whether to use an ODBC Driver or an OLE DB Provider. You determine this in the connect string by specifying a Driver value or a Provider value, but not both. ODBC stands for Open Database Connectivity, a standard for database drivers that has existed for several years. OLE DB is a newer, lower-level driver and for this reason the authors' recommendation is to use an OLE DB Provider whenever possible.

Tip

You can store your connection string in the registry and access it using the techniques described in Chapter 24, "Working with Files."

Using a Data Source Name

Although less popular now than it used to be, a Data Source Name (DSN) can be used in a connection string to specify an ODBC Data Source, as follows:

cn.Open "DSN=LocalServer;UID=apowers;PWD=groovy"

The connection string in the preceding line of code is extremely simple; it just lists the DSN, user ID, and password. All the other database information is stored in the ODBC Data Source as defined on the computer. To set up an ODBC Data Source, click the Data Sources icon in the Administrative Tools section of the Windows Control panel. Figure 21.2 shows the Data Source Administrator, which is used to enter connection information.

Although the connection string for an ODBC data source looks a lot simpler, the disadvantage of using it is that an extra step is required to get your program to run on a particular PC. By putting all the required connection information in the connection string, your application is more portable. However, for some database drivers with lots of required options but little documentation, creating a connection string that works may be easier said than done. In such cases, setting up an ODBC Data Source in the Control Panel is an easier way to get connected.

Using the Execute Method

In addition to controlling database connections, the ADO Connection object's Execute method allows you to run a SQL statement against a data source. If the SQL statement returns records, you can access them simply by assigning the return value of the Execute method to an ADO Recordset object.

Note

In ADO, you can accomplish the same goal in many different ways. Retrieving information is a perfect example. You can take your pick from the Recordset, Command, or Connection objects; they all have a method used to pull data from a database.

If you installed the sample SQL database included with Visual Studio .NET, you can test the Execute method with the following simple example:

1. Start a new Windows Application project, and add a reference to ADO as described earlier.

2. Add a list box control to the form, and name it lstAuthors.

3. Add a button to the form, and name it btnLoadList.

4. Place the following lines of code in the button's Click event (modifying the connection string where appropriate):

   Dim strConnect As String Dim sSSN, sName As String Dim cn As ADODB.Connection Dim rs As ADODB._Recordset 'Replace "bsw2k" with your PC name in the connection string strConnect = "UID=sa;PWD=;DATABASE=pubs;SERVER=bsw2k\NetSDK;Provider=SQLOLEDB" cn = New ADODB.Connection() cn.Open(strConnect) rs = cn.Execute("Select * from Authors") While Not rs.EOF sSSN = rs.Fields("au_id").Value.ToString sName = rs.Fields("au_lname").Value.ToString.Trim & ", " & rs.Fields("au_fname").Value.ToString lstAuthors.Items.Add(sSSN & vbTab & sName) rs.MoveNext() End While rs.Close() cn.Close()

5. Run the program and click the button. You should see results similar to those pictured in Figure 21.3

   Figure 21.3. The results of a simple SELECT query were created by using the Execute method of the Connection object.

The Execute method of the Connection object returns a Recordset object, which is stored in the variable rs. Next, a While loop is used to move through the recordset, adding each person's name and social security number to the list box.

You can also use the Execute method to run SQL statements that do not return a recordset, as in the following examples:

cn.Execute "Delete from Person where LastName = 'Doe' and FirstName = 'John'" cn.Execute "exec spTimeLogAdd 'Brian',123456,'Test'"

The first statement deletes a record from the Person table, and the second executes a stored procedure called spTimeLogAdd. You can also use the Execute method to insert records or perform other database commands.

Basic Recordset Operations

In the last section, you saw how to pull data into an ADO Recordset object. A recordset represents rows of data from one or more tables in a database, and is usually created by executing a query. Even though the query may involve multiple tables, the resulting recordset always looks like a single table to your Visual Basic program; it has fields (such as Name and Phone Number) and values for those fields (such as Robert Allen and 555-1212). Each set of field values that go together makes up a single record, and all these records together make up a recordset.

For more on tables and records, p. 535

Note

In the next chapter, we'll discuss the Datatable object, which is the .NET version of a set of records, as well as the more advanced Dataset object, which can store multiple table results and relationships.

In this section we will describe how to create a recordset and browse the records it contains using Visual Basic code.

Creating a Recordset from a Database Query

You have already learned how to create and populate a Recordset object by using the Execute method of the Connection object. However, a Recordset object has its own methods and properties that can be used to retrieve data. As with all objects, to use these properties, you need to create a new instance of the Recordset object first:

Dim rsPerson As ADODB.Recordset rsPerson = New ADODB.Recordset

Next, you can use properties of the object to specify the connection, record source, and recordset type. To specify a data source for a Recordset object, set the ActiveConnection property equal to an ADO Connection object or connection string:

rsPerson.ActiveConnection = cn rsPerson.ActiveConnection = "DSN=BIBLIO"

The first line of code assumes that cn represents an open connection that points to a data source, as discussed earlier. The second line demonstrates the use of a connection string, in which case a connection object will be implicitly created.

A recordset's Open method causes the recordset to be populated with data. The code in Listing 21.1 creates a new Recordset object and then prints the data it contains in the Output window.

Listing 21.1 ADOTEST.ZIP Creating an ADO Recordset

Dim rsPerson As ADODB.Recordset Dim cn As ADODB.Connection Dim strConnect As String Dim sSQL As String strConnect = "UID=sa;PWD=;DATABASE=BrianTest;SERVER=localhost;Provider=SQLOLEDB" SSQL = "Select LastName,FirstName from Person Where LastName like 'S%'" cn = New ADODB.Connection() cn.Open(strConnect) rsPerson = New ADODB.Recordset() rsPerson.ActiveConnection = cn rsPerson.Open(sSQL) While Not rsPerson.EOF Debug.Write(rsPerson.Fields(0).Value.ToString) Debug.WriteLine(rsPerson.Fields(1).Value.ToString) rsPerson.MoveNext() End While rsPerson.Close() cn.Close()

The code inListing 21.1 runs a SQL query that selects the last and first names of all persons having last names that begin with the letter S.

To learn how to set up the sample database, p. 535

Displaying Field Values

Listing 21.1 uses a numeric index to retrieve field values from the Recordset object's Fields collection. The SQL SELECT query retrieves two fields (LastName and FirstName), which correspond to indexes 0 and 1 in the fields collection, respectively. However, as we saw in the earlier discussion of the ADO Connection object, you can also reference fields by name. The following statements show an example of both types of field access:

rsPerson.Fields("LastName") rsPerson.Fields(0)

Note

The method of referring to field names using the exclamation mark, as in recordset!field name, is no longer supported.

Another interesting thing to note about the earlier lines of code is that they return Field objects. In previous versions of Visual Basic, this was enough to retrieve the field values, because Value was the default property. However, Visual Studio .NET's strong typing requires that you specify the Value property explicitly. The following lines of code use For loops to print both the field names and values of the fields in any given recordset:

'PRINT THE FIELD NAMES For i = 0 To rsPerson.Fields.Count - 1 Debug.Write(rsPerson.Fields(i).Name & vbTab) Next i Debug.WriteLine("") 'PRINT VALUES OF ALL FIELDS FOR EACH RECORD rsPerson.MoveFirst() While Not rsPerson.EOF For i = 0 To rsPerson.Fields.Count - 1 Debug.Write(rsPerson.Fields(i).Value.ToString.Trim & vbTab) Next i Debug.WriteLine("") rsPerson.MoveNext() End While

As you can see from the sample code, field indexes start at zero and end at one less than the number of fields in the recordset. The number of fields in a recordset is available through the Count property of the Fields collection.

Note

When accessing fields using a numeric index, keep in mind that any changes to the SQL statement (or tables themselves if you are using SELECT * will make the indexes change). For this reason, we suggest using stored procedures and field lists whenever possible, as described in the previous chapter.

Recordset Navigation

After data has been retrieved into a Recordset object, you can access and update the values of fields in the current record. Think of the recordset as a long sequential file. At any given time, the current record is a pointer to a location within that file. To work with different records, you can use the following navigation methods to change the current record:

• MoveFirst Moves to the first record, just after the beginning-of-file marker (BOF)

• MoveLast Moves to the last record, just before the end-of-file marker (EOF)

• MoveNext Moves to the next record after the current record (toward EOF)

• MovePrevious Moves back to the record before the current record (toward BOF)

• Move Moves forward or backward a specified number of records

BOF and EOF are properties that indicate the beginning and ending points of a recordset, respectively.

Navigation and the CursorType Property

In the examples up to this point, you have seen only two methods used to navigate within a recordset: the MoveFirst and MoveNext methods. In Listing 21.1, you used a While loop to move forward through the records in an open recordset until the EOF property was True.

Note

Looping through recordsets in a forward-only manner is typical for certain types of operations, such as creating a Web page. In Chapter 22 we will discuss ADO.NET's DataReader class, which can be used to create quick, forward-only result sets.

As a matter of fact, attempting to use MovePrevious or MoveLast with Listing 21.1 would cause an error, because it is invalid for the selected cursor type. A cursor in a recordset is like a cursor on a computer screen; it is a pointer to a current position.

Because you did not specify a value for the Recordset object's CursorType property, it was set to the default value of adOpenForwardOnly. The ramifications of the CursorType property are discussed shortly; but for now just remember that a "forward only" cursor does not support MovePrevious and MoveLast navigation methods.

Using Navigation Methods

As an example of recordset navigation, you will create a project that uses the navigation methods. To get started, create a new Standard EXE project with a list box named lstData, three command buttons (btnPrevious, btnNext, and btnJump), and a text box named txtJump.

The sample program runs a query against the membership database from Chapter 20 and then allows the user to execute the navigation methods to browse the recordset. The code for the sample project is shown in Listing 21.2.

Listing 21.2 ADONAV.ZIP Using Recordset Navigation Methods

Private rs As ADODB.Recordset Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the InitializeComponent() call rs = New ADODB.Recordset() rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic rs.Open("Select * from Person", _ "Server=mypc\NetSDK;UID=sa;pwd=;Database=pubs;Provider=SQLOLEDB") DisplayCurrentRecord() End Sub Private Sub DisplayCurrentRecord() Dim i As Integer Dim s As String If rs.BOF Then rs.MoveFirst() If rs.EOF Then rs.MoveLast() lstData.Items.Clear() For i = 0 To rs.Fields.Count - 1 s = rs.Fields(i).Name & ": " & rs.Fields(i).Value.ToString lstData.Items.Add(s) Next i Me.Text = "Current Position:" & rs.AbsolutePosition End Sub Private Sub btnNext_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnNext.Click rs.MoveNext() DisplayCurrentRecord() End Sub Private Sub btnPrevious_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnPrevious.Click rs.MovePrevious() DisplayCurrentRecord() End Sub Private Sub btnJump_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnJump.Click rs.Move(convert.ToInt32(txtJump().Text)) DisplayCurrentRecord() End Sub

The sample code creates a recordset using a static cursor, which, unlike the forward-only cursor, allows navigation in both directions. The use of the navigation methods in the sample project is straightforward. The only part that merits further explanation is the DisplayCurrentRecord function. Notice that it checks for BOF or EOF conditions before displaying the field names and values for the current record. This step is necessary because attempting to access the Field object without a current record would cause an error. The working project is pictured in Figure 21.4.

Using the RecordCount Property

Another useful property used with recordset navigation is the RecordCount property, which determines the number of records returned in a recordset. While it is easy to check the EOF property during navigation, using the RecordCount property may allow you to avoid even more extra processing:

rsInvoices.Open("spGetInvoicestoProcess") If rsInvoices.RecordCount = 0 Then MessageBox.Show("No invoices to process") Else PreparePrinter() ProcessInvoices(rsInvoices) End If

In this sample code, the PreparePrinter function is called only if rsInvoices contains records. Another use of the RecordCount property might be to display a progress bar indicating the number of records remaining until all are processed.

Note

Depending on the CursorType and CursorLocation property settings, an accurate record count may not be available. For example, a forward-only server-side cursor will contain 1 if records are available. If you do not get the accurate record count you need, try changing the cursor type or executing the MoveLast method before accessing the RecordCount property.

Sorting and Filtering a Recordset

As you may recall from our discussion of SQL statements in the previous chapter, a WHERE clause can be used to filter records returned from a SQL query, and the ORDER BY clause can be used to control their order. The Recordset object contains Filter and Sort properties that can alter these same attributes without executing an additional SQL query. To sort a recordset, simply set the Sort property to a comma-separated field list, as in the following examples:

rs.Sort = "State, City, LastName, FirstName" rs.Sort = "Age DESC, LastName, FirstName"

As with the ORDER BY clause, you can specify whether the records are sorted in ascending order (the default) or descending order. The Filter property works just like a WHERE clause:

rs.Filter = "Age > 15 AND LastName Like 'S%'" rs.Filter = "State = 'AL' OR State = 'NY'"

Keep in mind that while these properties act similarly to their SQL counterparts, they are not as versatile. For example, the second line of code uses an OR to specify multiple states because the Filter property does not support an IN clause.

Note

When you set the Filter or Sort properties, the contents of the recordset are updated immediately to reflect the changes.

Changing Data in a Recordset

Now that you know how to get information into a Recordset object and display it, you can take the next step and change (or update) the information in the database. If your recordset has been set up appropriately, you can change the underlying database information easily. Simply navigate to the appropriate record, assign a new value to each field you want to change, and finally call the Update method, as follows:

rs.Fields("FirstName").Value = "Dweezil" rs.Update()

Adding new records to a Recordset object is similar to changing the content of existing records, but with one extra step:

1. Call the AddNew method.

2. Assign values to the fields.

3. Call the Update method.

The code in Listing 21.3 adds a new record to the Person table.

Listing 21.3 ADOTEST.ZIP Adding a New Record to an ADO Recordset

Dim rs As ADODB.Recordset 'OPEN A RECORDSET rs = New ADODB.Recordset() rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic rs.LockType = ADODB.LockTypeEnum.adLockOptimistic rs.Open("Select * from Person",_ "Server=localhost;UID=sa;pwd=;Database=BrianTest;Provider=SQLOLEDB") 'ADD A NEW RECORD rs.AddNew() rs.Fields("SSN").Value = "000000007" rs.Fields("LastName").Value = "Bond" rs.Fields("FirstName").Value = "Jimmy" rs.Fields("Address").Value = "MI-6 Headquarters" rs.Fields("City").Value = "London" rs.Fields("Age").Value = 39 rs.Update() rs.Close

As with a SQL INSERT statement, you must provide values for fields that do not accept nulls; otherwise, an exception will be thrown.

Note

If you do not call the Update statement when adding records, changes will not be written to the database and the new record will be lost when you perform a navigation method.

To delete the current record from a recordset, use the Delete method:

rs.Delete()

Note that after calling the Delete method, the current record will be invalid, so you will have to use a navigation method before attempting to access field values.

Understanding Record Locking

By default, ADO recordsets are read only. Attempting to change a field value in a read-only recordset causes an error. To add, change, or delete records in an ADO recordset, you must set the LockType property to a different value than the read-only default, as shown in the following line of code from Listing 21.3:

rs.LockType = ADODB.LockTypeEnum.adLockOptimistic

You might wonder why the LockType property is important. The answer is that if you are editing records in a multiuser database, you have to be concerned with record locking. Record locking means preventing other users from trying to edit the same database record at the same time. Record locking is controlled with the LockType property, which has the following values:

• adLockReadOnly Sets data in the recordset as read only

• adLockPessimistic Provides pessimistic record locking, which means the record is locked while you are editing it

• adLockOptimisitc Provides optimistic record locking, which means records are locked only when you call the Update method

• adLockBatchOptimisitc Updates multiple records at a time with the UpdateBatch method

Why are the parameters named after attitudes (optimistic and pessimistic)? Because attempting to update a locked record causes an error in your program. You can be optimistic that a record will be available when you need to update it, or pessimistic and lock it for as long as you need it.

Viewing Others' Changes

Another concern with multiuser databases is making sure the data in your recordset is as accurate as it needs to be. If you recall an earlier section, you know that the CursorType property can restrict recordset navigation. However, the main point of the different cursor types, listed in Table 21.2, is to control how your recordset is linked to the underlying data.

As you will find out from using ADO, not all cursor types are supported by all databases. For Access databases, the default adOpenForwardOnly cursor is intended for a quick read-only pass through the database, whereas the adOpenKeySet cursor is better suited for updates and more complex operations. As we will see in a moment, another important property when dealing with recordsets is the CursorLocation property. This property determines whether the recordset is linked to the database or disconnected from the database.

The Command Object

When you are working with a set of data, most of the time you are working with properties and methods of the Recordset object. However, to retrieve that data, you will find that the ADO Command object is indispensable. It allows you to encapsulate a query or SQL stored procedure into a reusable object, which is especially ideal if you need to perform the operation multiple times. You can even store the parameters from your query or stored procedure in the object's Parameters collection, which means you don't have to worry about building an appropriate SQL string with associated quotation marks. After you set up a Command object, you can change the parameters of the object and call it repeatedly.

For example, recall the SQL statement from Listing 21.1, which was used to select persons whose last names begin with the letter S. Suppose we created a stored procedure, spSearch that contained a parameter:

CREATE PROCEDURE spSearch @strSearchLetter char(1) AS SELECT * FROM Person WHERE LastName Like @strSearchLetter + '%'

For more on stored procedures, p. 535

To create a Command object for this stored procedure, you need to tell it the name of the stored procedure and parameter information, as shown in Listing 21.4.

Listing 21.4 ADOTEST.ZIP Using a Command Object

Dim rs AS ADODB.RecordSet Dim cmd As New ADODB.Command() Dim prm As ADODB._Parameter Dim cn As New ADODB.Connection() 'Open a connection to the database_ cn.Open("Server=localhost;uid=sa;pwd=;Database=BrianTest;Provider=SQLOLEDB") 'Set up the command object cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc cmd.CommandText = "spSearch" cmd.ActiveConnection = cn 'Set up the parameter object prm = cmd.CreateParameter("@strSearchLetter", _ ADODB.DataTypeEnum.adChar, _ ADODB.ParameterDirectionEnum.adParamInput, _ 1, _ "S") cmd.Parameters.Append(prm) 'Set up the recordset object rs = New ADODB.Recordset() rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic rs.LockType = ADODB.LockTypeEnum.adLockOptimistic 'Execute the command rs.Open(cmd)

The code in Listing 21.4 makes use of every ADO object we have discussed in this chapter. The Command object contains a collection of Parameter objects, each of which represents a parameter in the underlying stored procedure. After a Command object has been created, you can manipulate its parameters and call it again with just a few lines of code:

cmd.Parameters("@strSearchLetter").Value = "T" rs.Close() rs.Open(cmd)

The preceding lines of code would re-open a recordset after changing the command parameters. If these lines of code were executed subsequently to those in Listing 21.4, the recordset would contain those people whose last names began with T.

Note

The Command object also has an Execute method that works similarly to the Connection.Execute method.