ADO - ADO GetString Method for Recordset Formatting

The ADO GetString method is a useful method of the Recordset object that converts the records contained in a Recordset into a single formatted string. Instead of processing each record individually with a loop, an application can use GetString to retrieve multiple rows at once and represent them as text. This is particularly useful when Recordset data needs to be displayed, generated as HTML, included in a report, or transferred into a text-based format.

1. What is the GetString Method?

The GetString method belongs to the ADO Recordset object. Its primary purpose is to return the contents of a Recordset as a string.

The general syntax is:

Recordset.GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)

The parameters allow the developer to control how the retrieved data should be formatted.

For example:

Dim rs As ADODB.Recordset
Dim result As String

Set rs = New ADODB.Recordset

rs.Open "SELECT ID, Name, Department FROM Employees", _
        connectionObject, adOpenStatic, adLockReadOnly

result = rs.GetString()

In this example, the entire Recordset is converted into a string and stored in the result variable.

2. Parameters of GetString

The GetString method provides several parameters for controlling the resulting string.

StringFormat

The StringFormat parameter specifies the format in which the Recordset should be returned.

ADO provides the adClipString format for returning the Recordset as a string suitable for text-based processing.

Example:

result = rs.GetString(adClipString)

The adClipString option tells ADO to return the Recordset contents as a delimited string.

NumRows

NumRows determines how many rows should be converted.

For example:

result = rs.GetString(adClipString, 5)

This requests up to five rows from the current position of the Recordset.

Using a specific number of rows can be useful when an application does not need to convert the entire Recordset.

ColumnDelimiter

ColumnDelimiter specifies the characters placed between individual column values.

For example:

result = rs.GetString(adClipString, -1, ",")

If a Recordset contains:

101   John   Sales
102   David  HR

the resulting string can resemble:

101,John,Sales
102,David,HR

A tab character can also be used:

result = rs.GetString(adClipString, -1, vbTab)

RowDelimiter

RowDelimiter specifies what separates one row from the next.

For example:

result = rs.GetString(adClipString, -1, ",", vbCrLf)

Here, vbCrLf places a new line between records.

The resulting output could be:

101,John,Sales
102,David,HR
103,Robert,Finance

NullExpr

NullExpr specifies the value that should be used when a database field contains NULL.

For example:

result = rs.GetString(adClipString, -1, ",", vbCrLf, "N/A")

If the Department field contains NULL, the output could contain:

101,John,N/A

This is useful because it prevents missing database values from producing unclear output.

3. Example Using GetString

Consider a table named Employees:

ID Name Department
101 Arun Sales
102 Meena HR
103 Ravi Finance

The Recordset can be opened using:

Dim rs As ADODB.Recordset
Dim output As String

Set rs = New ADODB.Recordset

rs.Open "SELECT ID, Name, Department FROM Employees", _
        connectionObject, adOpenStatic, adLockReadOnly

output = rs.GetString(adClipString, -1, ",", vbCrLf, "N/A")

MsgBox output

The resulting string can be similar to:

101,Arun,Sales
102,Meena,HR
103,Ravi,Finance

The method therefore provides a convenient way to transform database rows into a simple text representation.

4. Why GetString is Useful

One major advantage of GetString is that it can reduce the amount of code required to process Recordset data.

Without GetString, a developer might use a loop:

Do Until rs.EOF
    output = output & rs("ID") & "," _
                    & rs("Name") & "," _
                    & rs("Department") & vbCrLf

    rs.MoveNext
Loop

With GetString, the same basic operation can be performed more directly:

output = rs.GetString(adClipString, -1, ",", vbCrLf)

This makes the code shorter and can be particularly convenient when the objective is simply to convert the contents of a Recordset into text.

5. GetString and HTML Generation

One practical use of GetString is generating HTML-style output from database information.

For example, an application may retrieve employee information and use delimiters to construct portions of an HTML table.

However, GetString itself does not automatically create a complete HTML document. It simply converts the Recordset into a string according to the specified formatting parameters. The application is responsible for adding the necessary HTML structure.

For example, the application could generate:

<table>
<tr><td>101</td><td>Arun</td><td>Sales</td></tr>
<tr><td>102</td><td>Meena</td><td>HR</td></tr>
</table>

This makes GetString useful as part of a larger data-presentation process.

6. GetString and Empty Recordsets

An important situation to consider is an empty Recordset.

Suppose the SQL query does not return any records:

SELECT ID, Name FROM Employees WHERE ID = 9999

The resulting Recordset contains no rows. The application should therefore handle the possibility that there is no data before relying on the returned string.

A common approach is to check:

If rs.EOF And rs.BOF Then
    output = "No records found."
Else
    output = rs.GetString(adClipString, -1, ",", vbCrLf)
End If

This allows the application to provide meaningful output when no matching records exist.

7. GetString and NULL Values

Database fields can contain NULL, and applications need to decide how those values should appear in the generated string.

The NullExpr parameter provides a convenient way to replace NULL values.

For example:

output = rs.GetString(adClipString, -1, ",", vbCrLf, "Unknown")

If a database field contains NULL, ADO can represent it using the specified replacement value.

This is especially helpful when the resulting string will be displayed to users or processed by another application.

8. Controlling the Number of Records

GetString does not always have to process the entire Recordset.

For example:

output = rs.GetString(adClipString, 10, ",", vbCrLf)

This limits the operation to the specified number of rows.

This can be useful when an application needs only a portion of the available data.

Developers should also understand that the operation works from the current position of the Recordset. Therefore, Recordset navigation can affect which records are returned.

9. GetString Compared with Manual Recordset Processing

There are two common approaches to converting Recordset data into text.

The first is manual processing:

Do Until rs.EOF
    output = output & rs("Name") & vbCrLf
    rs.MoveNext
Loop

The second is using GetString:

output = rs.GetString(adClipString, -1, ",", vbCrLf)

Manual processing provides greater control because the developer can perform calculations, conditional processing, formatting, or transformations for every individual record.

GetString is more convenient when the objective is straightforward conversion of Recordset contents into a delimited string.

Therefore, GetString should not be considered a replacement for all Recordset-processing techniques. It is primarily a convenience method for text-oriented output.

10. Important Points to Remember

The ADO GetString method has several important characteristics:

  • It is a method of the ADO Recordset object.

  • It converts Recordset contents into a string.

  • adClipString is used for string-based Recordset formatting.

  • NumRows controls how many records are returned.

  • ColumnDelimiter separates values within a row.

  • RowDelimiter separates individual rows.

  • NullExpr provides replacement text for NULL values.

  • The method can simplify text-based Recordset processing.

  • The resulting string can be used for reports, text output, or further application processing.

  • It does not automatically create a complete HTML document or other presentation format.

  • The current position of the Recordset can affect which rows are included.

Conclusion

The ADO GetString method provides a convenient way to transform Recordset data into a formatted string without manually iterating through every row. Its delimiter and formatting parameters give developers control over how columns, rows, and NULL values are represented. It is particularly useful when database results need to be converted into text-oriented output quickly.

For applications requiring detailed processing of every record, a traditional Recordset loop may provide greater flexibility. However, when the requirement is simply to convert a collection of database records into a structured string, GetString offers a concise and practical solution.