-
- RecordSet的GetString方法(你以前听说过米?)
- Weather:天气还算不错的...
- 2005-03-23
如果查询结果很多,服务器解释你的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:711855 Wednesday 23 3/23/2005 1:33:37 AM 711856 Wednesday 23 3/23/2005 1:23:00 AM 711857 Wednesday 23 3/23/2005 1:26:34 AM 711858 Wednesday 23 3/23/2005 1:33:53 AM 711859 Wednesday 23 3/23/2005 1:30:36 AM 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>", " ")
' 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!
%>-
Views(3813) | Comments(0) |
In:
web develop
|

