ADO - ADO Recordset GetString Method

The GetString method in ADO is used to convert the contents of a Recordset into a single string. A Recordset normally contains rows and columns retrieved from a database. Instead of processing each row individually using a loop, GetString allows developers to retrieve the entire set of records, or a selected portion of it, as one formatted string. This can be useful when database results need to be displayed as text, placed inside an HTML page, written to a file, or prepared for another text-based operation.

Syntax

The general syntax of the ADO GetString method is:

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

Each argument controls how the returned string is constructed.

1. StringFormat

StringFormat specifies the format in which the Recordset should be converted into a string.

The commonly used value is:

adClipString

adClipString tells ADO to return the Recordset as a text string with columns and rows separated according to the delimiters supplied to the method.

For example:

rs.GetString(adClipString)

This converts the Recordset into a string representation.

2. NumRows

NumRows specifies the number of rows that should be included in the returned string.

For example:

rs.GetString(adClipString, 10)

This requests up to 10 rows from the Recordset.

If you want to process all available rows, you can use:

rs.GetString(adClipString, -1)

The exact behavior can depend on the provider and ADO environment, so using the appropriate ADO constant or documented value for the target environment is recommended.

3. ColumnDelimiter

ColumnDelimiter determines what separates individual columns in the generated string.

For example:

","

will produce comma-separated values.

If the Recordset contains:

101    John    25
102    David   30
103    Mary    28

using a comma as the column delimiter could produce:

101,John,25
102,David,30
103,Mary,28

A tab can also be used:

vbTab

This is useful when creating tab-separated data.

4. RowDelimiter

RowDelimiter determines what separates one row from the next.

For example:

vbCrLf

places each Recordset row on a new line.

Therefore, combining:

",", vbCrLf

produces a structure similar to CSV data.

5. NullExpr

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

For example:

"NULL"

If a particular field contains a database NULL, the resulting string can contain:

NULL

instead of leaving the value empty.

You can also specify an empty string:

""

if you want database NULL values to appear as blank values.

Basic Example

Consider a database table named Employees containing:

ID Name Department
1 John Sales
2 Mary HR
3 David IT

An ADO Recordset can be created using:

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT ID, Name, Department FROM Employees", conn

The GetString method can then be used:

Dim result

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

The resulting string could look like:

1,John,Sales
2,Mary,HR
3,David,IT

The entire Recordset has therefore been converted into one string.

Using GetString for HTML Output

One practical application of GetString is generating HTML output from database records.

For example:

Dim result

result = rs.GetString(adClipString, -1, "</td><td>", "</td></tr><tr><td>", "")

Response.Write "<table>"
Response.Write "<tr><td>" & result & "</td></tr>"
Response.Write "</table>"

The delimiters can be selected so that database values are inserted between HTML table cells.

However, directly inserting database values into HTML without HTML encoding can create security problems. In applications that display untrusted database content, appropriate output encoding should be applied.

GetString Compared with a Loop

Without GetString, developers commonly process records one at a time:

Do Until rs.EOF
    Response.Write rs("Name")
    Response.Write "<br>"

    rs.MoveNext
Loop

Here, the program explicitly accesses each row and then moves to the next row.

With GetString, the operation can be considerably shorter:

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

The main advantage is that ADO performs the conversion of the Recordset into the requested string representation.

Creating CSV-Like Output

GetString can also be useful when a simple text representation of database results is required.

For example:

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

This produces:

1,John,Sales
2,Mary,HR
3,David,IT

This looks like CSV data, but there is an important limitation: GetString does not automatically provide full CSV escaping and quoting rules. If values contain commas, quotation marks, or line breaks, additional processing may be required to create a standards-compliant CSV file.

Handling NULL Values

Suppose the database contains:

ID Name Department
1 John Sales
2 Mary NULL

You can specify a replacement expression:

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

The output could then contain:

1,John,Sales
2,Mary,N/A

This is useful when an application needs a visible representation for missing database values.

Important Characteristics

The GetString method works with an existing ADO Recordset. It does not execute a SQL query itself. The normal sequence is:

Database
   |
   v
SQL Query
   |
   v
Recordset
   |
   v
GetString()
   |
   v
Formatted String

Therefore, GetString should be viewed as a Recordset-to-string conversion method, rather than a database retrieval method.

Advantages

The major advantages of GetString include:

  1. It can convert many Recordset rows into a single string.

  2. It reduces the amount of code required for simple Recordset formatting.

  3. Custom column and row delimiters can be specified.

  4. NULL values can be represented using a custom expression.

  5. It can be useful for text-based output and simple data exports.

  6. It can eliminate the need for an explicit loop when only formatted output is required.

Limitations

GetString is not appropriate for every situation.

If individual records require complex processing, a normal Recordset loop is usually more appropriate. For example, if every employee record needs different calculations, conditional processing, validation, or database updates, processing each row individually provides much greater control.

It is also important to remember that generating one large string from a very large Recordset can consume considerable memory. For large datasets, retrieving and processing data in smaller portions may be more efficient.

Another limitation is that GetString provides delimiters but does not automatically transform the output into every possible structured data format. If you need strict JSON, XML, or standards-compliant CSV, a dedicated serialization or formatting approach may be preferable.

Example with Complete Processing

A simple Classic ASP example can look like this:

Dim conn
Dim rs
Dim output

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=CompanyDB;Integrated Security=SSPI"

Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT ID, Name, Department FROM Employees", conn

If Not rs.EOF Then
    output = rs.GetString(adClipString, -1, ",", vbCrLf, "")
    Response.Write output
End If

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

The query first creates a Recordset. The GetString method then converts the records into a comma-separated, line-by-line string.

Difference Between GetString and GetRows

GetString and GetRows both provide ways of extracting Recordset data, but their purposes are different.

GetString converts the data into a string, making it useful for textual output.

GetRows converts the data into an array, making it more appropriate when the application needs to manipulate individual values programmatically.

For example:

output = rs.GetString(adClipString)

produces text, whereas:

data = rs.GetRows()

produces an array containing the Recordset data.

Therefore, use GetString when the final result is primarily intended to be text, and use GetRows when the application needs structured programmatic access to individual values.

Conclusion

The ADO Recordset GetString method provides a convenient way to transform Recordset data into a single formatted string. Its parameters allow developers to control the number of rows returned, column separators, row separators, and representation of NULL values. It is particularly useful for straightforward textual output, simple data exports, and situations where manually looping through every Recordset row would add unnecessary code. However, for complex processing, very large datasets, or strict data formats such as properly escaped CSV or JSON, other approaches may be more appropriate.