Re: Dynamic access to ADO recordset?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 05/29/04 

Date: Sat, 29 May 2004 09:51:38 -0400

Charles E Finkenbiner wrote:
> Hi All,
>
> My question is, do I always have to Move to a record? Instead of
> actually moving can I address the required record? Something like:
>
> objRecordset.Record(1).Fields(1).Value
>
> The reason I ask? I want to write a recursive function. This
> function will access a header recordset along with X-number detail
> recordset. If the function calls itself and moves to a different
> record how will this affect the record position when the function
> returns to itself?

I see that others have helped you out on the use of the recordsets to do
this. I though you might want to consider the use of more efficient arrays
for this task, which will allow you to address a specific row's field value
as you desire above without the overhead of creating bookmarks and using the
cursor library. If you do this:

Dim objRecordset, arData
'code to open a connection and create the query string
objRecordset.Open ...
if not objRecordset.EOF Then arData = objRecordset.GetRows
objRecordset.Close: Set objRecordset = Nothing
'close and destroy the connection if you are done with it
if isArray(arData) then
    'you can now refer to the second field in the second row:
    value = arData(1,1)
    'or the third field in the 4th row:
    value = arData(2,3)
else
    'no data was returned
end if

GetRows returns a 2-dimensional array: the first dimension corresponding to
the field position, and the second corresponding to the row (record)
position. Array indexes are zero-based, so the first field in the first row
is referred to as: arData(0,0). If you don't know how many rows were
returned by your query, you can easily find out using the ubound fundtion:

rows = ubound(arData,2) + 1

Using arrays is more efficient, because there is no cursor library needed to
maintain current position, etc.

HTH,
Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • Issue with table parameter types and varchar column in C++ ODBC
    ... Connection may have been terminated by the server." ... It occurs on the 'second' SP call to the interface using the same connection ... The first loop SP execution does succeed and character arrays ...
    (microsoft.public.data.odbc)
  • Re: verilog multidimentional arrays
    ... Verilog-2001 supports multi-dimensional arrays and arrays of nets, ... connect scalars or vectors to ports. ... SystemVerilog allows connection of entire arrays to ports (which is ...
    (comp.lang.verilog)
  • Re: Comparing TCP sockets
    ... If I do "io_1.peeraddr" I can see the remote address. ... If latter the connection is closed by the peer and I do ... You can compare the Arrays without going field by field. ...
    (comp.lang.ruby)
  • Re: change URL variables
    ... // this will replace parameters to the query string ... // may seem like a lot of code, but I use these 2 funcs all the time. ... // does not handle arrays ... I tried to parse the url but can only get the part ...
    (comp.lang.php)