当前位置:首页 开发语言 正文

asp中的getrows和getstring用法

2011年06月05日 | 作者: 米豆网 - 1000seo | 分类: 开发语言 | 

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代码).

view plaincopy to clipboardprint?
01.<TABLE Border=1>  
02.<TR><TD>  
03.<% = Response.Write rs.GetString( , , “</TD><TD>”, “</TD></TR><TR>”, ) %>  
04.</TABLE>  
<TABLE Border=1>
<TR><TD>
<% = Response.Write rs.GetString( , , “</TD><TD>”, “</TD></TR><TR>”, ) %>
</TABLE> 
这样写的HTML结果如下:

view plaincopy to clipboardprint?
01.<TABLE Border=1>  
02.<TR>  
03.<TD>row1, field1 value</TD>  
04.<TD>row1, field2 value</TD>  
05.</TR>  
06.<TR>  
07.<TD>row2, field1 value</TD>  
08.<TD>row2, field2 value</TD>  
09.</TR>  
10.</TABLE> 
<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了,再看看生成下拉选单
view plaincopy to clipboardprint?
01.<%   
02.Set RS = conn.Execute(“SELECT theValue,theText FROM selectOptionsTable ORDER BY theText”)   
03. 
04.optSuffix = “</OPTION>” & vbNewLine   
05.valPrefix = “<OPTION Value='”   
06.valSuffix = “‘>”   
07.opts = RS.GetString( , , valSuffix, optSuffix & valPrefix, “–error–” )   
08.’ Next line is the key to it!   
09.opts = Left( opts, Len(opts)-Len(valPrefix) )   
10. 
11.Response.Write “<SELECT …>” & vbNewLine   
12.Response.Write valPrefix & opts   
13.Response.Write “</SELECT>”   
14.%>  
<%
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,只要这样做就可以了:

view plaincopy to clipboardprint?
01.<%   
02.Set RS = conn.Execute(“SELECT * FROM table”)   
03. 
04.tdSuffix = “</TD>” & vbNewLine & “<TD>  
05.trPrefix = “<TR>” & vbNewLine & “<TD>”   
06.trSuffix = “</TD>” & vbNewLine & “</TR>” & vbNewLine & “<TR>” & vbNewLine  
07.opts = RS.GetString( , , tdSuffix, trSuffix & trPrefix, “–error–” )   
08.’ Next line is the key to it!   
09.opts = Left( opts, Len(opts)-Len(trPrefix) )   
10. 
11.Response.Write “<TABLE Border=1 CellPadding=5>” & vbNewLine   
12.Response.Write trPrefix & opts   
13.Response.Write “</TABLE>” & vbNewLine  
14.%>  
<%
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
%>

————————————————————————
再介绍一个完全不同的办法

view plaincopy to clipboardprint?
01.<%   
02.SQL = “SELECT ‘<OPTION Value=”’,value,”’>’,text,'</OPTION>’ FROM table ORDER BY text”   
03.Set RS = conn.Execute(SQL)   
04.Response.Write “<SELECT>” & vbNewLine & RS.GetString(,,””,vbNewLine) & “</SELECT>” 
05.%>  
<%
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>”
%> 

你用过吗。。。

看到了吗?可以直接从查询中返回结果。
再进一步,您可以这样做

view plaincopy to clipboardprint?
01.<%   
02.SQL = “SELECT ‘<OPTION Value=”’ & value & ”’>’ & text & ‘</OPTION>’ FROM table ORDER BY text”   
03.Set RS = conn.Execute(SQL)   
04.Response.Write “<SELECT>” & vbNewLine & RS.GetString(,,””,vbNewLine) & “</SELECT>” 
05.%>  
<%
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代码如下:
——————————————————————————–

view plaincopy to clipboardprint?
01.<%  
02.’ Selected constants from adovbs.inc:  
03.Const adClipString = 2  
04. 
05.’ Declare our variables… always good practice!  
06.Dim cnnGetString   ‘ ADO connection  
07.Dim rstGetString   ‘ ADO recordset  
08.Dim strDBPath      ‘ Path to our Access DB (*.mdb) file  
09.Dim strDBData      ‘ String that we dump all the data into  
10.Dim strDBDataTable ‘ String that we dump all the data into                     
11.                   ‘ only this time we build a table  
12.’ MapPath to our mdb file’s physical path.  
13.strDBPath = Server.MapPath(“db_scratch.mdb”)  
14. 
15.’ Create a Connection using OLE DB  
16.Set cnnGetString = Server.CreateObject(“ADODB.Connection”)  
17. 
18.’ This line is for the Access sample database:  
19.’cnnGetString.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strDBPath & “;”  
20.’ We’re actually using SQL Server so we use this line instead.  
21.’ Comment this line out and uncomment the Access one above to  
22.’ play with the script on your own server.  
23.cnnGetString.Open “Provider=SQLOLEDB;Data Source=10.2.1.214;” _  
24.& “Initial Catalog=samples;User Id=samples;Password=password;” _  
25.& “Connect Timeout=15;Network Library=dbmssocn;” 
26. 
27.’ Execute a simple query using the connection object.  
28.’ Store the resulting recordset in our variable.  
29.Set rstGetString = cnnGetString.Execute(“SELECT * FROM scratch”)  
30. 
31.’ Now this is where it gets interesting… Normally we’d do  
32.’ a loop of some sort until we ran into the last record in  
33.’ in the recordset.  This time we’re going to get all the data  
34.’ in one fell swoop and dump it into a string so we can  
35.’ disconnect from the DB as quickly as possible.  
36.strDBData = rstGetString.GetString()  
37. 
38.’ Since I’m doing this twice for illustration… I reposition  
39.’ at the beginning of the RS before the second call.  
40.rstGetString.MoveFirst  
41. 
42.’ This time I ask for everything back in HTML table format:  
43.strDBDataTable = rstGetString.GetString(adClipString, -1, _  
44.&”</td><td>”, “</td></tr>” & vbCrLf & “<tr><td>”, “&nbsp;”)  
45. 
46.’ Because of my insatiable desire for neat HTML, I actually  
47.’ truncate the string next.  You see, GetString only has  
48.’ a parameter for what goes between rows and not a seperate  
49.’ one for what to place after the last row.  Because of the  
50.’ way HTML tables are built, this leaves us with an extra  
51.’ <tr><td> after the last record.  GetString places the  
52.’ whole delimiter at the end since it doesn’t have anything  
53.’ else to place there and in many situations this works fine.  
54.’ With HTML it’s a little bit weird.  Most developers simply  
55.’ close the row and move on, but I couldn’t bring myself to’  
56. leave the extra row… especially since it would have a  
57.’ different number of cells then all the others.  
58.’ What can I say… these things tend to bother me.  ;)  
59.strDBDataTable = Left(strDBDataTable, Len(strDBDataTable) – Len(“<tr><td>”))  
60. 
61.’ Some notes about .GetString:  
62.’ The Method actually takes up to 5 optional arguments:  
63.’ 1. StringFormat    – The format in which to return the  
64.’                      recordset text. adClipString is the only  
65.’                      valid value.  
66.’ 2. NumRows         – The number of rows to return.  Defaults  
67.’                      to  -1 indicating all rows.  
68.’ 3. ColumnDelimiter – The text to place in between the columns.  
69.’                      Defaults to a tab character  
70.’ 4. RowDelimiter    – The text to place in between the rows  
71.’                      Defaults to a carriage return  
72.’ 5. NullExpr        – Expression to use if a NULL value is  
73.’                      returned.  Defaults to an empty string.  
74.’ Close our recordset and connection and dispose of the objects.  
75.’ Notice that I’m able to do this before we even worry about  
76.’ displaying any of the data!  
77.rstGetString.Close  
78.Set rstGetString = Nothing 
79.cnnGetString.Close  
80.Set cnnGetString = Nothing 
81. 
82.’ Display the table of the data.  I really don’t need to do  
83.’ any formatting since the GetString call did most everything  
84.’ for us in terms of building the table text.  
85.Response.Write “<table border=””1″”>” & vbCrLf  
86.Response.Write “<tr><td>” 
87.Response.Write strDBDataTable  
88.Response.Write “</table>” & vbCrLf  
89.’ FYI: Here’s the output format you get if you cann GetString  
90.’ without any parameters:  
91.Response.Write vbCrLf & “<p>Here’s the unformatted version:</p>” & vbCrLf  
92.Response.Write “<pre>” & vbCrLf  
93.Response.Write strDBDataResponse.Write “</pre>” & vbCrLf  
94. 
95.’ That’s all folks!  
96.%>

本文固定链接:http://www.1000seo.com/soft/56
本文章由 米豆网 - 1000seo 于2011年06月05日发布在开发语言分类下,您可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
+复制链接 转载请注明:asp中的getrows和getstring用法-米豆网
关键字:, ,

好文章就要一起分享!

更多

发表评论

你的大名(必填)

你的邮箱(必填)

你的网站(选填)

评论内容(必填)

icon_wink.gif icon_neutral.gif icon_mad.gif icon_twisted.gif icon_smile.gif icon_eek.gif icon_sad.gif icon_rolleyes.gif icon_razz.gif icon_redface.gif icon_surprised.gif icon_mrgreen.gif icon_lol.gif icon_idea.gif icon_biggrin.gif icon_evil.gif icon_cry.gif icon_cool.gif icon_arrow.gif icon_confused.gif icon_question.gif icon_exclaim.gif