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)
| Parameter | Description |
|---|
| StringFormat | An optional value that specifies the format when retrieving a Recordset as a string |
| NumRows | An optional value to specify the number of rows to be converted in the Recordset |
| ColumnDelimiter | An optional value to set the delimiter. Tab is default. |
| RowDelimiter | An optional value to set the delimiter. Carriage return character is default. |
| NullExpr | An 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>"," ")
%>
<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.
Did you find the page informational and useful? Share it using one of your favorite social sites.
Recommended Books & Training Resources
