The following code illustrates the use og Getstring function in ASP,
which will speed up the display of records from the table
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 elminates 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>"," ") %>
<HTML>
<BODY>
<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. Lets 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.)
Send Message
Advertise