asp中記錄集對象的getrows和getstring用法分析

GetRows 方法
將 Recordset 對象的多個記錄複製到數組中。
文法 複製代碼 代碼如下:array = recordset.GetRows( Rows, Start, Fields )

傳回值
返回二維數組。
參數
Rows 可選，長整型運算式，指定要檢索記錄數。預設值為 adGetRowsRest (-1)。
Start 可選，字串或長整型，計算得到在 GetRows 操作開始處的記錄的書籤。也可使用下列 BookmarkEnum 值。
常量　　　　　　　　　　　說明
AdBookmarkCurrent 　　從目前記錄開始。
AdBookmarkFirst 　　　　從首記錄開始。
AdBookmarkLast　　　　 從尾記錄開始。

Fields 可選，變體型，代表單個欄位名、順序位置、欄位名數組或順序位置號。ADO 僅返回這些欄位中的資料。
說明
使用 GetRows 方法可將記錄從 Recordset 複製到二維數組中。第一個下標識別欄位，第二個則標識記錄號。當 GetRows 方法返回資料時陣列變數將自動調整到正確大小。
如果不指定 Rows 參數的值，GetRows 方法將自動檢索 Recordset 對象中的所有記錄。如果請求的記錄比可用記錄多，則 GetRows 僅返回可用記錄數。
如果 Recordset 對象支援書籤，則可以通過傳送該記錄的 Bookmark 屬性值，來指定 GetRows 方法將從哪個記錄開始檢索資料。
如要限制 GetRows 調用返回的欄位，則可以在 Fields 參數中傳送單個欄位名/編號或者欄位名/編號數組。
在調用 GetRows 後，下一個未讀取的記錄成為目前記錄，或者如果沒有更多的記錄，則 EOF 屬性設定為 True。
GetString方法
查詢資料庫顯示表格時,我們常用Do While()...Loop 或者是For...Next迴圈來顯示表格,這樣當我們要查詢大量資料時,勢必會比較慢。這時，我們就可以用記錄集對象提供的GetString()方法(ADO必須升級到2.0)。
文法 複製代碼 代碼如下:Str=objRecordset.GetString(format,n,coldel,rowdel,nullexpr)

參數說明:
objRecordset:已開啟的記錄集對象;
format:可選,一般取預設值(預設值為2)
n:可選,顯示記錄的數量，預設值為全部顯示
coldel:可選,列界定符
rowdel:可選,行界定符
nullexpr:可選,該參數用於填充空欄位!
有了GetString方法,我們就可以僅用一個Response.Write來顯示所有的輸出了，它就象是能判斷Recordset是否為EOF的DO ... LOOP迴圈。
用這個方法，可以自動的迴圈輸出字串,就不用再去while或for迴圈了,只要建立了RS對象，並且執行了相應操作，不管那是返回一條或者多條記錄，甚至是空記錄，getstring照樣工作。
要從Recordset的結果裡產生HTML表格，我們只需關心GetString的5個參數中的3個：coldel(分隔記錄集的列的HTML代碼)，rowdel(分隔記錄集的行的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>
</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 strDBDataResponse.Write "</pre>" & vbCrLf

' That's all folks!
%>