When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
Published: Tuesday, December 15, 1998

WebDaily: Your daily source for Web Technology Tips and Tricks!

Speeding up ASP by Using GetString

* This article describes how to speed up your ASP scripts by using GetString as opposed to multiple Response.Write's

- continued -

Most every ASP developer has had the experience of needing to display a certain database query in an HTML table. Chances are we've implemented it this way:


'Create connection / recordset
'Populate data into recordset object

<% Do While not rs.EOF %>
<% rs.MoveNext
Loop %>

For large queries, this can slow down your ASP script processing time, since many Response.Write commands must be processed by the server. It would be much quicker if you could have the entire string (from <TABLE> to </TABLE> created then outputted using Response.Write just once. Well, the fine people at Microsoft have made this possible. (Note, this is an ADO 2.0 feature. This will not work if you are still using ADO 1.5. You can obtain ADO 2.0 freely at http://www.microsoft.com/data/download.htm.)

The GetString method allows us to display our string with only one Response.Write; it eliminates the DO ... LOOP and our conditional which tests if the recordset is at EOF.

Here is the syntax (all parameters are optional):

String = recordset.GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)

To populate a table with the results from a recordset, we only need to worry about three of the four parameters above: ColumnDelimiter (what HTML we separate each column in the recordset with), RowDelimiter (what HTML we separate each row in the recordset with), and NullExpr (what HTML we use if the column we're currently on is NULL). As you can see from the table-populating ASP example above, each column is delimited by a <TD>...</TD>, and each row is delimited by a <TR>...</TR>. That being said, let's look at some code.

<% Option Explicit 'Good coding technique

'Establish connection to DB
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=Northwind;"

'Create a recordset
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM table1", conn

'Store our one big string
Dim strTable
strTable = rs.GetString(,,"</td><td>","</td></tr><tr><td>","&nbsp;") %>


<% Response.Write(strTable) %>


Set rs = Nothing
Set conn = Nothing

The string strTable will contain a lengthy string of all our columns and rows returned by our SQL statement "SELECT * FROM table1". Between each row the HTML </td><td> will appear, and between each row, the HTML </td></tr><tr><td> will appear. This will produce the exact HTML we need with only one Response.Write. Let's quickly look at an example. Imagine that our query returned the following rows and columns:

  Col1 Col2 Col3
Row1 Bob Smith 40
Row1 Ed Frank 43
Row1 Sue Void 42

The resulting string from our GetString statement would be:

Bob</td><td>Smith</td><td>40</td><td></td></tr><tr><td>Ed ...

and so on. Granted, this string is not pretty, but it gets the job done. (Note that we put the <TABLE><TR><TD> at the beginning and the </TD></TR></TABLE> at the end of the raw HTML. This is because our formatted string lacks these beginning and ending strings.)

To see an example of using GetString to populate a SELECT box, check out Robert Monteiro's article: Displaying Listboxes and Hyperlinks with GetString.

If you have any questions or comments, please response to this email. I'd also like to invite you to use the ASP Message board available at http://www.ASPMessageboard.com. Have a great day!

Happy Programming!

Related Articles
  • Optimizing your ASP Code
  • Speeding up your ASP Scripts
  • Using ADO 2.0's GetString to Speed up your ASP Scripts
  • How to Use Connection Pooling to Speed up your Queries

    ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article