Re: how to use GetRows() with VBscrip (ASP)?

From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 10/31/04 

Date: Sun, 31 Oct 2004 09:03:36 -0800

"Mich" <mich@nomail> wrote in message
news:OZJ%23F9xvEHA.1400@TK2MSFTNGP11.phx.gbl...
> Thanks, but suppose i also need the records, how can i then use GetRows()?
>
> Thanks again
> Mich

Ok, this is what the docs say:

    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 most natural looking way to use it is to define constants for each
field's ordinal position. Like say you open a recordset on SQL like this:

    SELECT field1, field2, field3, field4 FROM sometable

Constants for field ordinals would be:

    const field1 = 0
    const field2 = 1
    const field3 = 2
    const field4 = 3

Now assume rs is an already opened recordset:

    DataArray = rs.GetRows()

You can get the record count like this:

    RecCount = UBound(DataArray, 2)

This is how you'd access data in the first row:

    DataArray(field1, 0)
    DataArray(field2, 0)
    DataArray(field3, 0)
    DataArray(field4, 0)

It would work just the same if it was coded as below, but it's a lot harder
to read

    DataArray(0, 0)
    DataArray(1, 0)
    DataArray(2, 0)
    DataArray(3, 0)

If you called rs.MoveFirst, these would be the equivilent recordset object
calls:

    rs("field1").Value
    rs("field2").Value
    rs("field3").Value
    rs("field4").Value

Iterate through the rows like so:

    For i = 0 to UBound(DataArray, 2)
        Debug,Print DataArray(field1, i)
    Next

Make sense?

-Mark

> "Mark J. McGinty" <mmcginty@spamfromyou.com> wrote in message
> news:OAVmglqvEHA.1992@TK2MSFTNGP15.phx.gbl...
>> If all you want is a record count you don't want to use GetRows, as it'll
>> incur i/o cost of reading all the data and stuffing an array with it --
> not
>> that it isn't fairly efficient at that, it's just unnecessary.
>>
>> Instead change your SQL to "SELECT COUNT(*) AS [RowCount] FROM mytable".
>> Since it will return exactly one row with one [derived] field that will
> not
>> be writable, it's pointless to use anything but a 'firehose' (read-only
>> forward-only) cursor, so:
>>
>> set rs = objdc.Execute(sql)
>> rec = rs(0).value
>>
>> will do in place of your last 3 lines.
>>
>>
>> -Mark
>>
>>
>> "Mich" <mich@nomail> wrote in message
>> news:%238KHTkpvEHA.2536@TK2MSFTNGP10.phx.gbl...
>> > Hi,
>> >
>> > I want to use GetRows() instead of RecordCount, but i have some problem
>> > with
>> > array in VBscript (ASP).
>> >
>> > This is what i want to change with GetRows():
>> > <%
>> > set objdc = Server.CreateObject("ADODB.Connection")
>> > objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source =c:\mydb")
>> > sql="select field1 from mytable;"
>> > set rs=Server.CreateObject("ADODB.Recordset")
>> > rs.open sql, OBJDC, 3, 3
>> > rec=rs.RecordCount
>> > %>
>> >
>> > Thanks
>> > Mich
>> >
>> >
>>
>>
>
>