Programming With ADO

So far, this chapter has mostly discussed automatic ways to set up ADO Connection, Command, and Recordset objects using Data Environment Designers or the ADO Data Control.

To program ADO objects directly, you must set your VB project to refer to the latest version of the Microsoft ActiveX Data Objects Library from the Project, References menu dialog box, as shown below.

 

You basically have three main concerns when you program with ADO in VB:

• Setting up and maintaining a connection to data with the Connection object
  
• Retrieving rows or otherwise manipulating the data with the Recordset object, the Command and Parameter objects, or the Connection object
  
• Determining the exact behavior and nature of the rows of data returned by a Connection or Command and manipulating the data's individual fields and rows with the Recordset and Field objects
  

Recordsets - Command Object Events in VB6

The general steps you need to take to initialize a data-connected Recordset in your code are as follows:

1. Make sure you have a valid Connection or Command object.
2. Declare an object variable of the type ADODB.Recordset.
3. Set the Source property (typically, a SQL statement or the name of a stored procedure or table) and the ActiveConnection property (use the Set = syntax to cause this property to point to a valid ADO Connection object). You can also omit this step and pass information about the Source and ActiveConnection as arguments in the next step.
4. Call the Recordset's Open method. If you omitted step 3, indicate the Recordset's Source and ActiveConnection as the Open method's first and second arguments, respectively.

---
Set xConn = New ADODB.Connection
Set xRecord = New ADODB.Recordset

Dim xString As String
xString = "---connection string---"
xConn.Open xString
xRecord.Open "---query statement---", xConn
---

Manipulating a Recordset's Data With Its Methods (VB6) 

The most direct manipulation of data with ADO takes place through the Recordset. The methods for ADO's Recordset object are basically the same as the methods for the Data Environment's or ADO Data Control's Recordset objects. You can therefore read the following sections on the specific Recordset methods and properties as applying to both the Recordset of ADO, and to the Recordset that belongs to the Data Environment and ADO Data Control.
One activity varies significantly between the two classes of Recordset, however: Notice that the technique for adding a record in ADO code significantly differs from the technique for adding a record for the Data Environment or ADO Data Control.
There is no design-time binding of controls to the data as there is with the Data Environment or the ADO Data Control. Because straight ADO objects lack this automatic binding of controls to data, the programmer must write code to refresh variables or userinterface controls whenever any action happens that would move the record pointer or otherwise change the contents of the fields.
Similarly, the programmer must explicitly move data from controls or variables to the record buffer whenever data should be saved.
Because a data access program must perform these two tasks so often, it is most efficient for the programmer to provide one general routine to read data from the record buffer into controls and another general routine to write data from controls into the record buffer.
The program can then call these routines whenever it needs to perform these tasks.
The following sections,
Referring to Recordset Field Contents
Programmatically Reading a Record into VB Controls
Programmatically Writing VB Controls to a Record
describe how you can write routines to manually refresh data in both directions (reading and writing) when you directly program ADO.


Referring to Recordset Field Contents 

Every open Recordset must be associated with a data cursor. Behind every Recordset cursor, there is, among other things, a buffer representing the values of fields in the current record that the cursor points to. The Fields collection of the Recordset exposes this record buffer.
You can always programmatically read the values of individual fields in the record buffer. Depending on whether the current Recordset's cursor type permits writes to the data, you can also assign values to the record buffer's fields.

1. There are several syntactic styles for referring to an individual field in the current Record, as follows:
You can use the numeric index of the field in the Fields collection (Fields is zero-based, so the first field is element 0 in the collection):
--> rs.Fields(2).Value
This technique is flexible (you could use a numeric variable as the index for the Fields collection), but it's not quite as useful as the following technique, because unless you know the position of fields in the data, you will have a hard time getting the right index.

2. Because the Fields collection also supports index key strings, you can use the field's name in a string literal or variable to refer to the field in the Fields collection:
---> rs.Fields("LastName").Value rs("LastName").Value
Notice the second alternative form of this example, which bypasses an explicit reference to the Fields collection.
This technique is perhaps the most useful from a programming standpoint, but it also requires the most runtime overhead and so is the slowest of the three techniques.

3. You can also refer to the field as a temporary property of the Recordset, using the bang (!) syntax:
---> rs!LastName
This technique is the most efficient, but it's the least flexible of the three (because you hard-code the field name in the program, whereas you could substitute variables for the index and key values in the first and second techniques).

As already mentioned, you can both read and write these fields programmatically. However, no changes are transferred to the underlying data until you call the Update method as discussed in the following section.
Unless you need flexibility at runtime, the last method listed is probably the best to use; it's fast and explicitly identifies the field you want to access. If you want to read the contents of the current record's Last Name field into the Text property of txtLastName, for example the line would look like this:

---> txtLastName.Text = rsEmployees![Last Name] & "" 

The Text property of a TextBox control does not accept null data. The use of the final characters & "" at the end of the line ensures that, even if the underlying field contains null data, an error will not occur. The & "" makes sure that at least a blank string is contained in the data being written to the TextBox.

Read More