ADO - ADO GetString Method for Formatting Recordset Data

The GetString method in ActiveX Data Objects (ADO) is used to convert the contents of an ADO Recordset into a single string. Instead of processing every record individually with a loop, an application can use GetString to retrieve multiple rows and columns at once in a formatted text representation. This can be particularly useful when database results need to be displayed as plain text, placed into an HTML response, written to a text file, or passed to another part of an application.

Syntax of GetString

The general syntax of the ADO GetString method is:

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

Each parameter controls how the returned string is constructed.

StringFormat specifies the format of the generated string. In classic ADO, the commonly used value is adClipString, which formats the Recordset as a delimited string.

NumRows specifies the number of rows that should be included in the resulting string. If this value is omitted or configured appropriately, the method can process the available rows according to the ADO implementation.

ColumnDelimiter specifies the characters placed between column values. For example, a comma can be used to create comma-separated output.

RowDelimiter specifies the characters placed between records. A newline character is commonly used when the result needs to appear as separate lines.

NullExpr specifies what should be returned when a database field contains a NULL value. This allows an application to replace database NULL values with a specified text representation.

How GetString Works

Suppose a database contains an Employees table with the following information:

EmployeeID    Name       Department
101           Arun       Sales
102           Priya      Finance
103           Ravi       Support

After executing a query and storing its results in a Recordset, the application can use GetString to convert these records into a single string.

For example:

101,Arun,Sales
102,Priya,Finance
103,Ravi,Support

Here, the comma is the column delimiter and the newline is the row delimiter. The important point is that the method does not return a collection of individual values. Instead, it produces one formatted string containing the selected Recordset data.

Example in VBScript

A simple classic ASP/VBScript example is:

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database\Company.accdb"

Set rs = conn.Execute("SELECT EmployeeID, Name, Department FROM Employees")

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

Response.Write output

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>

In this example, the SQL query produces a Recordset containing employee information. The GetString method converts the complete result into a text string. The comma separates columns, while vbCrLf separates rows. The empty string specified for NullExpr means that a NULL database value will be represented by an empty value.

Why GetString Is Useful

One of the major advantages of GetString is that it reduces the amount of code required to convert a Recordset into formatted text. Without GetString, an application would normally have to move through each record and retrieve each field separately.

For example, without GetString, an application might use:

Do Until rs.EOF
    Response.Write rs("EmployeeID") & "," & rs("Name") & "," & rs("Department")
    Response.Write "<br>"
    rs.MoveNext
Loop

With GetString, the same basic conversion can be performed using a single method call:

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

This makes the code shorter and can make straightforward data-export operations easier to implement.

Column and Row Delimiters

The delimiter parameters are an important feature of GetString. A column delimiter determines how values belonging to the same record are separated.

For example:

101 | Arun | Sales
102 | Priya | Finance

Here, the pipe character (|) could be used as the column delimiter.

The row delimiter determines how separate records are separated:

101 | Arun | Sales
102 | Priya | Finance
103 | Ravi | Support

A carriage-return and line-feed combination can be used to place each database record on a separate line.

This flexibility allows GetString to produce output suitable for different applications.

Handling NULL Values

Database fields can contain NULL, which represents the absence of a value. When converting Recordset data into a string, applications may want to replace NULL with a meaningful representation.

For example, suppose the database contains:

101    Arun     Sales
102    Priya    NULL
103    Ravi     Support

The application could specify "N/A" as the NullExpr value. The resulting output could then appear as:

101,Arun,Sales
102,Priya,N/A
103,Ravi,Support

This is useful when the generated string will be displayed to users or exported to another system that does not handle database NULL values directly.

Limiting the Number of Rows

The NumRows argument allows an application to control how many rows are converted into the resulting string. This can be useful when a Recordset contains many records but the application needs only a portion of the available data.

For example:

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

This requests formatting of a specified number of rows rather than automatically processing every available row.

The exact behavior should be considered alongside the current Recordset position and the ADO provider being used.

Common Applications

The GetString method can be useful in several situations. It can simplify the creation of text-based reports because database rows can be converted directly into a formatted string. It can also be used when generating HTML output from database results, although applications should properly encode values before inserting database content into HTML.

Another common use is exporting data into simple delimited formats. For example, a Recordset can be converted into comma-separated or tab-separated text and then written to a file. It can also be useful when an application needs to pass a collection of database results as one text value to another component.

Advantages

The primary advantage of GetString is simplicity. It provides a convenient way to transform Recordset data into text without manually iterating through every row and field.

It also provides control over formatting through delimiters and NULL replacement values. This makes the method suitable for simple data-export and presentation tasks.

Another advantage is reduced repetitive code. Instead of writing separate statements to retrieve and concatenate every field, the application can allow ADO to perform the conversion.

Limitations and Precautions

Although GetString is convenient, it should not be considered a replacement for structured data serialization in every situation. A delimited string does not inherently preserve database data types, relationships, or metadata. If the resulting data needs to be consumed by another application, a structured format may be more appropriate.

Care should also be taken when values themselves contain the delimiter character. For example, if a comma is being used as the column delimiter and a database field contains "Bangalore, Karnataka", simply generating comma-separated output can make it difficult to distinguish the field's comma from the delimiter.

For large Recordsets, converting everything into one large string may also require significant memory. In such situations, processing or exporting data in smaller portions can be more appropriate.

Difference Between GetString and Manual Recordset Processing

Manual processing gives the programmer complete control over every individual record:

Do Until rs.EOF
    employeeID = rs("EmployeeID")
    employeeName = rs("Name")

    Response.Write employeeID & " - " & employeeName
    rs.MoveNext
Loop

GetString, on the other hand, is designed for situations where the primary objective is to convert Recordset contents into a formatted string:

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

Therefore, manual processing is generally preferable when each record requires individual calculations or special business logic, while GetString is convenient when the objective is straightforward text generation.

Conclusion

The ADO GetString method provides a convenient mechanism for converting Recordset data into a single formatted string. By controlling the string format, number of rows, column delimiter, row delimiter, and representation of NULL values, developers can quickly transform database results into text suitable for reports, exports, or simple presentation. Its main strength is reducing repetitive Recordset-processing code, although developers should consider data size, delimiter conflicts, and the need for structured formats before using it for large or complex datasets.