ADO GetString Method

Thursday, September 27, 2012

tags ADO

When displaying large amounts of data that you retrieve from your database, the process can slow down ASP script processing time. The GetString method returns a specified Recordset object as a string. This method is very useful for populating tables using HTML in an ASP file. Most web developers may simply create a Connection and then populate the information into a Recordset. Finally, loop through the Recordset and display the data in an HTML table using the Response.Write method. Here is an example of this type of approach.

<% Do While not oRs.EOF %>
        <td><% Response.Write(oRs.Fields("Field1")) %></td>
        <td><% Response.Write(oRs.Fields("Field2")) %></td>
<% oRs.MoveNext
Loop %>

For queries that contain a lot of data, this can slow down your ASP script processing time. Response.Write statements can slow down the process. A quicker approach would be to have the entire data stored in one string. Then output that one string using the Response.Write method one time. We can do so using the GetString method. The GetString method eliminates the DO ... LOOP and testing for EOF. Here is the syntax:

String = Recordset.GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)
StringFormatAn optional value that specifies the format when retrieving a Recordset as a string
NumRowsAn optional value to specify the number of rows to be converted in the Recordset
ColumnDelimiterAn optional value to set the delimiter. Tab is default.
RowDelimiterAn optional value to set the delimiter. Carriage return character is default.
NullExprAn optional value to set instead of a null value. Otherwise it is an empty string.

To populate a table with the results from a recordset, you only need to pass three parameters: ColumnDelimiter, RowDelimiter, and NullExpr. Here is an example of the code you may use.

<!DOCTYPE html>
    <title>My Page</title>
<style type="text/css">
table {border:1px solid #ABABAB;border-collapse:collapse;color:#4F4F4F;width:50%}
tr {border:1px solid #ABABAB;}
td {border:1px solid #ABABAB;padding:5px;}

Dim oConn, oRS, datasource, sql
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.recordset")
datasource = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
sql = "SELECT empName, empTitle FROM employees"
oConn.Open datasource
oRS.Open sql, oConn

Dim strTable
strTable = oRS.GetString(,,"</td><td>","</td></tr><tr><td>","&nbsp;")
  <% Response.Write(strTable) %>

Set oRS=nothing
Set oConn=nothing


The strTable variable above contains a string of all the columns and rows returned by the SQL SELECT statement. Between each column the HTML </td><td> will be inserted, and between each row, the HTML </td></tr><tr><td> will be inserted. This will produce the HTML code that we need with one Response.Write statement.

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

ADO Programmers Reference ADO: ActiveX Data Objects ADO Programming for Dummies