Colorful Life²⁰¹⁰

如果查询结果很多,服务器解释你的ASP script将花费大量的时间,因为有许多的Response.Write语句要处理. 如果你将输出的全部结果放在一个很长的字符串里(从< TABLE >到< /TABLE >),那么服务器只需解释一遍Response.Write语句,速度就会快得多. 微软公司里的一些能干的家伙已经将想法变成了现实. (注意,这是一个ADO 2.0才有的特性. 如果你还在使用ADO 1.5话,可以在http://www.microsoft.com/data/download.htm免费下载ADO 2.0)

有了GetString方法,我们就可以仅用一个Response.Write来显示所有的输出了,它就象是能判断Recordset是否为EOF的DO ... LOOP循环
用这个方法,可以自动的循环输出字符串,就不用再去while或for循环了,只要建立了RS对象，并且执行了相应操作，不管那是返回一条或者多条记录，甚至是空记录，getstring照样工作。
这个方法的参数如下

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

要从Recordset的结果里生成HTML表格,我们只需关心GetString的5个参数中的3个: ColumnDelimiter(分隔记录集的列的HTML代码),RowDelimiter(分隔记录集的行的HTML代码),和NullExpr(当前记录为空时应生成的HTML代码).

<TABLE Border=1>
<TR><TD>
<% = Response.Write rs.GetString( , , "</TD><TD>", "</TD></TR><TR>", ) %>
</TABLE>
这样写的HTML结果如下：
<TABLE Border=1>
<TR>
<TD>row1, field1 value</TD>
<TD>row1, field2 value</TD>
</TR>
<TR>
<TD>row2, field1 value</TD>
<TD>row2, field2 value</TD>
</TR>
<TR>
</TABLE>

这里有个BUG了，再看看生成下拉选单

<%
Set RS = conn.Execute("SELECT theValue,theText FROM selectOptionsTable ORDER BY theText")

optSuffix = "</OPTION>" & vbNewLine
valPrefix = "<OPTION Value='"
valSuffix = "'>"
opts = RS.GetString( , , valSuffix, optSuffix & valPrefix, "--error--" )
' Next line is the key to it!
opts = Left( opts, Len(opts)-Len(valPrefix) )

Response.Write "<SELECT ...>" & vbNewLine
Response.Write valPrefix & opts
Response.Write "</SELECT>"
%>

如果想建立一个正确的表格的话，解决那个BUG，只要这样做就可以了：
<%
Set RS = conn.Execute("SELECT * FROM table")

tdSuffix = "</TD>" & vbNewLine & "<TD>
trPrefix = "<TR>" & vbNewLine & "<TD>"
trSuffix = "</TD>" & vbNewLine & "</TR>" & vbNewLine & "<TR>" & vbNewLine
opts = RS.GetString( , , tdSuffix, trSuffix & trPrefix, "--error--" )
' Next line is the key to it!
opts = Left( opts, Len(opts)-Len(trPrefix) )

Response.Write "<TABLE Border=1 CellPadding=5>" & vbNewLine
Response.Write trPrefix & opts
Response.Write "</TABLE>" & vbNewLine
%>
------------------------------------------------------------------------
再介绍一个完全不同的办法

<%
SQL = "SELECT '<OPTION Value=''',value,'''>',text,'</OPTION>' FROM table ORDER BY text"
Set RS = conn.Execute(SQL)
Response.Write "<SELECT>" & vbNewLine & RS.GetString(,,"",vbNewLine) & "</SELECT>"
%>

你用过吗。。。

看到了吗？可以直接从查询中返回结果。
再进一步，您可以这样做
<%
SQL = "SELECT '<OPTION Value=''' & value & '''>' & text & '</OPTION>' FROM table ORDER BY text"
Set RS = conn.Execute(SQL)
Response.Write "<SELECT>" & vbNewLine & RS.GetString(,,"",vbNewLine) & "</SELECT>"
%>
下面是一份完整的示例：
Script Output:

ASP代码如下：

<%
' Selected constants from adovbs.inc:
Const adClipString = 2

' Declare our variables... always good practice!
Dim cnnGetString   ' ADO connection
Dim rstGetString   ' ADO recordset
Dim strDBPath      ' Path to our Access DB (*.mdb) file
Dim strDBData      ' String that we dump all the data into
Dim strDBDataTable ' String that we dump all the data into
                   ' only this time we build a table

' MapPath to our mdb file's physical path.
strDBPath = Server.MapPath("db_scratch.mdb")

' Create a Connection using OLE DB
Set cnnGetString = Server.CreateObject("ADODB.Connection")

' This line is for the Access sample database:
'cnnGetString.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

' We're actually using SQL Server so we use this line instead.
' Comment this line out and uncomment the Access one above to
' play with the script on your own server.
cnnGetString.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
& "Initial Catalog=samples;User Id=samples;Password=password;" _
& "Connect Timeout=15;Network Library=dbmssocn;"

' Execute a simple query using the connection object.
' Store the resulting recordset in our variable.
Set rstGetString = cnnGetString.Execute("SELECT * FROM scratch")

' Now this is where it gets interesting... Normally we'd do
' a loop of some sort until we ran into the last record in
' in the recordset.  This time we're going to get all the data
' in one fell swoop and dump it into a string so we can
' disconnect from the DB as quickly as possible.
strDBData = rstGetString.GetString()

' Since I'm doing this twice for illustration... I reposition
' at the beginning of the RS before the second call.
rstGetString.MoveFirst

' This time I ask for everything back in HTML table format:
strDBDataTable = rstGetString.GetString(adClipString, -1, _
"</td><td>", "</td></tr>" & vbCrLf & "<tr><td>", "&nbsp;")

' Because of my insatiable desire for neat HTML, I actually
' truncate the string next.  You see, GetString only has
' a parameter for what goes between rows and not a seperate
' one for what to place after the last row.  Because of the
' way HTML tables are built, this leaves us with an extra
' <tr><td> after the last record.  GetString places the
' whole delimiter at the end since it doesn't have anything
' else to place there and in many situations this works fine.
' With HTML it's a little bit weird.  Most developers simply
' close the row and move on, but I couldn't bring myself to
' leave the extra row... especially since it would have a
' different number of cells then all the others.
' What can I say... these things tend to bother me.  ;)
strDBDataTable = Left(strDBDataTable, Len(strDBDataTable) - Len("<tr><td>"))

' Some notes about .GetString:
' The Method actually takes up to 5 optional arguments:
' 1. StringFormat    - The format in which to return the
'                      recordset text. adClipString is the only
'                      valid value.
' 2. NumRows         - The number of rows to return.  Defaults
'                      to  -1 indicating all rows.
' 3. ColumnDelimiter - The text to place in between the columns.
'                      Defaults to a tab character
' 4. RowDelimiter    - The text to place in between the rows
'                      Defaults to a carriage return
' 5. NullExpr        - Expression to use if a NULL value is
'                      returned.  Defaults to an empty string.

' Close our recordset and connection and dispose of the objects.
' Notice that I'm able to do this before we even worry about
' displaying any of the data!
rstGetString.Close
Set rstGetString = Nothing
cnnGetString.Close
Set cnnGetString = Nothing

' Display the table of the data.  I really don't need to do
' any formatting since the GetString call did most everything
' for us in terms of building the table text.
Response.Write "<table border=""1"">" & vbCrLf
Response.Write "<tr><td>"
Response.Write strDBDataTable
Response.Write "</table>" & vbCrLf

' FYI: Here's the output format you get if you cann GetString
' without any parameters:
Response.Write vbCrLf & "<p>Here's the unformatted version:</p>" & vbCrLf
Response.Write "<pre>" & vbCrLf
Response.Write strDBData
Response.Write "</pre>" & vbCrLf

' That's all folks!
%>