4GuysFromRolla.com - Using GetString to quicken your ASP Scripts (and simplify your life!)

Windows Systems Administrator
Jupitermedia
US-CT-Darien

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


	%>


	<TABLE>

	<% Do While not rs.EOF %>

   
		<TR>

   
   
			<TD><%=rs("Field1")%></TD>

   
   
			<TD><%=rs("Field2")%></TD>

   
   
			...

   
		</TR>

	<%	rs.MoveNext

	Loop %>

	</TABLE>

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.

<%@ LANGUAGE="VBSCRIPT" %>
<% 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>"," ") %>

<TABLE>
<TR><TD>
<% Response.Write(strTable) %>
</TD></TR>
</TABLE>

</BODY>
</HTML>

'Cleanup!
rs.Close
Set rs = Nothing
conn.Close
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