Analysis of getrows and getstring usage of record set objects in asp. Friends who need it can refer to the GetRows method.
Copies multiple records of a Recordset object into an array.
grammar
Copy the code code as follows:
array = recordset.GetRows( Rows, Start, Fields )
return value
Returns a two-dimensional array.
parameter
Rows Optional, long expression specifying the number of records to retrieve. The default value is adGetRowsRest (-1).
Start Optional, string or long, computes the bookmark of the record at the start of the GetRows operation. The following BookmarkEnum values can also be used.
Constant description
AdBookmarkCurrent starts with the current record.
AdBookmarkFirst starts with the first record.
AdBookmarkLast starts from the last record.
Fields is optional, variant type, representing a single field name, sequential position, array of field names, or sequential position number. ADO only returns data from these fields.
illustrate
Use the GetRows method to copy records from a Recordset into a two-dimensional array. The first subscript identifies the field, and the second identifies the record number. The array variable will automatically adjust to the correct size when the GetRows method returns data.
If you do not specify a value for the Rows parameter, the GetRows method will automatically retrieve all records in the Recordset object. If more records are requested than available, GetRows returns only the number of available records.
If the Recordset object supports bookmarks, you can specify the record at which the GetRows method will begin retrieving data by passing the value of the record's Bookmark property.
To limit the fields returned by the GetRows call, you can pass a single field name/number or an array of field names/numbers in the Fields parameter.
After GetRows is called, the next unread record becomes the current record, or if there are no more records, the EOF property is set to True.
GetString method
When querying the database to display tables, we often use Do While()...Loop or For...Next loop to display tables, so when we want to query a large amount of data, it will inevitably be slower. At this time, we can use the GetString() method provided by the recordset object (ADO must be upgraded to 2.0).
grammar
Copy the code code as follows:
Str=objRecordset.GetString(format,n,coldel,rowdel,nullexpr)
Parameter description:
objRecordset: the opened recordset object;
format: optional, generally takes the default value (default value is 2)
n: Optional, the number of records to display, the default value is to display all
coldel: optional, column delimiter
rowdel: optional, row delimiter
nullexpr: optional, this parameter is used to fill empty fields!
With the GetString method, we can use only one Response.Write to display all output. It is like a DO... LOOP loop that can determine whether the Recordset is EOF.
Using this method, you can automatically output strings in a loop, without having to go through while or for loops. As long as the RS object is created and the corresponding operations are performed, whether it is returning one or more records, or even empty records, getstring Work as usual.
To generate an HTML table from the results of a Recordset, we only need to care about 3 of the 5 parameters of GetString: coldel (the HTML code that separates the columns of the recordset), rowdel (the HTML code that separates the rows of the recordset), and nullexpr (HTML code that should be generated when the current record is empty).
Copy the code code as follows:
<TABLE Border=1>
<TR><TD>
<% = Response.Write rs.GetString( , , </TD><TD>, </TD></TR><TR>, ) %>
</TABLE>
The HTML result written like this is as follows:
Copy the code code as follows:
<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>
There is a BUG here, let’s look at generating the drop-down menu:
Copy the code code as follows:
<%
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>
%>
If you want to create a correct table and solve the bug, just do this:
Copy the code code as follows:
<%
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
%>
Let’s introduce a completely different approach:
Copy the code code as follows:
<%
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>
%>
Have you ever used it? . .
Did you see that? Results can be returned directly from the query.
Going one step further, you can do this:
Copy the code code as follows:
<%
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>
%>
Here is a complete example:
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
The complete ASP code is as follows:
Copy the code code as follows:
<%
' 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!
%>