GetRows method
Copies multiple records of a Recordset object into an array.
Grammar
Copy Code code as follows:
Array = Recordset. GetRows (Rows, Start, Fields)
return value
Returns a two-dimensional array.
Parameters
Rows optional, long-integer expression that specifies the number of records to retrieve. The default value is Adgetrowsrest (-1).
Start optional, string, or long integer to calculate the bookmark for the record at the beginning of the GetRows operation. You can also use the following BookmarkEnum values.
Constants Description
Adbookmarkcurrent starts at the current record.
Adbookmarkfirst begins with the first record.
Adbookmarklast begins with the tail record.
Fields Optional, Variant type that represents a single field name, ordinal position, field array group, or ordinal position number. ADO returns only the data in these fields.
Description
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. When the GetRows method returns data, the array variable is automatically resized to the correct size.
If you do not specify the value of the Rows parameter, the GetRows method automatically retrieves all the records in the Recordset object. If the requested record is more than the available record, GetRows only returns the number of available records.
If the Recordset object supports bookmarks, you can specify which record the GetRows method will start retrieving data by passing the value of the Bookmark property of the record.
To restrict the fields returned by the GetRows call, you can transfer a single field name/number or an array of field names/numbers in the Fields parameter.
After calling GetRows, 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 a database to display a table, we often use the doing while () ... Loop or for ... Next loop to display the table so that when we query a lot of data, it's going to be slow. At this point, we can use the GetString () method provided by the Recordset object (ADO must be upgraded to 2.0).
Grammar
Copy Code code as follows:
Str=objrecordset.getstring (format,n,coldel,rowdel,nullexpr)
Parameter description:
objRecordSet: Open Recordset object;
Format: Optional, general default value (default is 2)
N: Optional, display the number of records, the default value is all displayed
Coldel: Optional, column-defined character
Rowdel: Optional, row-defined character
NULLEXPR: Optional, this parameter is used to populate empty fields!
With the GetString method, we can display all the output with just one Response.Write, which is like a do ... that can tell if the recordset is EOF. Loop loop.
With this method, you can automatically loop the output string, you do not have to go to the while or for loop, as long as the RS object is established, and the corresponding operation, whether it is to return one or more records, or even empty records, GetString still work.
To generate an HTML table from the recordset's results, we only need to care about 3 of the 5 parameters of GetString: Coldel (the HTML code that separates the recordset's columns), Rowdel (the HTML code that separates the rows of the Recordset), and nullexpr ( The HTML code that should be generated when the current record is empty.
Copy Code code as follows:
<table border=1>
<TR><TD>
<% = Response.Write Rs. GetString (,, "</TD><TD>", "</TD></TR><TR>")%>
</TABLE>
The HTML results of this write are as follows:
Copy 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>
Here's a bug, then look at the Build dropdown menu:
Copy 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 ' 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 form, fix the bug, just do it:
Copy 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 ' 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
%>
To introduce a completely different approach:
Copy 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 it? You can return the results directly from the query.
Further, you can do this:
Copy 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>"
%>
The following is a complete example:
Script Output:
711855 Wednesday 3/23/2005 1:33:37 AM
711856 Wednesday 3/23/2005 1:23:00 AM
711857 Wednesday 3/23/2005 1:26:34 AM
711858 Wednesday 3/23/2005 1:33:53 AM
711859 Wednesday 3/23/2005 1:30:36 AM
The complete code for ASP is as follows:
Copy 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 of the data into
Dim strdbdatatable ' String that we dump all of 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 ")
The "This" 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 ' 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 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 asked 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 are GetString only has
' A parameter for what goes between rows and not a seperate
' One for what to place on the last row. Because of the
' Way HTML tables are built, this leaves us with a 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 the 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 others.
' What can I say 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
' 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 "If a NULL value is
' returned. Defaults to a empty string.
' Close our recordset and connection and dispose of the objects.
' Notice I ' m able to doing 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 todo
' Any formatting since of 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 have if you cann GetString
' Without any parameters:
Response.Write vbCrLf & "<p>here ' Unformatted version:</p>" & VbCrLf
Response.Write "<pre>" & VbCrLf
Response.Write Strdbdataresponse.write "</pre>" & VbCrLf
' That ' s all folks!
%>
