Getrows and getstring Usage Analysis of record set objects in asp

GetRows Method
Copy multiple records of the Recordset object to the array.
Syntax Copy codeThe Code is as follows: array = recordset. GetRows (Rows, Start, Fields)

Return Value
Returns a two-dimensional array.
Parameters
Rows (optional) Long Integer expression, which specifies the number of records to be retrieved. The default value is adGetRowsRest (-1 ).
Start is optional. It can be a string or a long integer to calculate the bookmarks of records starting from the GetRows operation. You can also use the following BookmarkEnum values.
Constant description
AdBookmarkCurrent starts from the current record.
AdBookmarkFirst starts from the first record.
AdBookmarkLast starts from the End record.

Optional. It is a variant that represents a single field name, sequence position, array of field names, or sequence position number. ADO returns only the data in these fields.
Description
You can use the GetRows method to copy records from Recordset to a two-dimensional array. The first subscript identifies the field, and the second identifies the record number. When the GetRows method returns data, the Group variable is automatically adjusted to the correct size.
If the value of the Rows parameter is not specified, the GetRows method automatically retrieves all records in the Recordset object. If the requested record is more than the available record, GetRows returns only the number of available records.
If the Recordset object supports bookmarks, You can transmit the Bookmark attribute value of the record to specify the record from which the GetRows method will retrieve data.
To limit the Fields returned by GetRows, You can transmit a single field name/number or an array of field name/number 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 attribute is set to True.
GetString Method
Do While ()... loop or... the Next loop is used to display tables, so that when we want to query a large amount of data, it will be slow. In this case, we can use the GetString () method provided by the record set object (ADO must be upgraded to 2.0 ).
SyntaxCopy codeCode: Str = objRecordset. GetString (format, n, coldel, rowdel, nullexpr)

Parameter description:
ObjRecordset: An opened record set object;
Format: Optional. Generally, the default value is 2)
N: (optional) number of records displayed. The default value is "show all ".
Coldel: (optional) column delimiter.
Rowdel: (optional) row delimiter.
Nullexpr: Optional. this parameter is used to fill an empty field!
With the GetString method, we can use only one Response. Write to display all the output. It is like a DO... LOOP that can judge whether Recordset is EOF.
With this method, you can automatically loop the output string without having to go through the while or for loop. As long as the RS object is created and the corresponding operations are performed, whether it is to return one or more records or even empty records, getstring still works.
To generate an HTML table from the Recordset results, we only need to care about three of the five parameters of GetString: coldel (HTML code that separates the columns of the record set ), rowdel (the HTML code that separates the rows of the record set), and nullexpr (the HTML code generated when the current record is empty ).

Copy codeThe Code is as follows: <TABLE Border = 1>
<TR> <TD>
<% = Response. Write rs. GetString (, "</TD> <TD>", "</TD> </TR> <TR>",) %>
</TABLE>

The HTML result is as follows:Copy codeThe Code is 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>

Here is a BUG. Let's look at the generated drop-down menu:Copy codeThe Code is 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, you only need to do so:Copy codeThe Code is 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
%>

Next we will introduce a completely different method:Copy codeThe Code is 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 used it...

Have you seen it? You can directly return results from the query.
Further, you can do this:Copy codeThe Code is 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>"
%>

The following is a complete example:
Script Output:
711855 Wednesday 23 1:33:37 AM
711856 Wednesday 23 1:23:00 AM
711857 Wednesday 23 1:26:34 AM
711858 Wednesday 23 1:33:53 AM
711859 Wednesday 23 1:30:36 AM

The complete ASP code is as follows:Copy codeThe Code is as follows: <%
'Selected constants from adovbs. inc:
Const adClipString = 2

'Descare 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
Dim strDBDataTable 'string that we dump all the data
'Only this time we build a table
'Mapath 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
'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 registration... 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
'Way HTML tables are built, this leaves us with an extra
'<Tr> <td> after the last record. GetString places
'Whole delimiter at the end since it doesn' t have anything
'Else to place there and in scenarios 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'
Leave the extra row... especially since it wowould have
'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
'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. ults 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!
%>