VB6 beginners tutorial - Learn VB6

Advanced VB6 tutorial - Learn Advanced VB6

Systems Analysis - System analysis and Design tutorial for Software Engineering

You are here: Visual Basic > Advanced VB6 tutorial > Chapter 8

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.

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):


    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.

  • 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:


    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.

  • You can also refer to the field as a temporary property of the Recordset, using the bang (!) syntax:


    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.

NOTE - Square Brackets Around Field Names: Some DBMSs support spaces in their field names. MS Access supports spaces in field names, for example, but SQL Server does not. In Access and SQL Server 7.0, a field named "Last Name" would be acceptable, but in SQL Server 6.5 and before, it would not be acceptable. You must place the square bracket characters "[ ]" in your code around field names that have spaces, as in these examples:

Rs.fields("[Last Name]")
Rs![Last Name]

For consistency you can place square brackets around field names that don't contain spaces as well, but there is no need to do so.

See Also


<< Previous | Contents | Next >>

Home | About Us | Privacy Policy | Contact Us

Copyright © | All Rights Reserved