Did you find this useful?
Socialize it today.


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.

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

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)
ParameterDescription
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>
<html>
<head>
    <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;}
</style>
</head>
<body>

<% 
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;")
%>
 
<table>
  <tr><td>
  <% Response.Write(strTable) %>
  </td></tr>
</table>

<%
oRS.close
oConn.close
Set oRS=nothing
Set oConn=nothing
%>

</body>
</html>

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.

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

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