Problems check Array object status when using .GetRows

[Laphan]

Hi All

I know the subject of this post might seem a bit cryptic, but I wanted to
get all of the keywords in.

Basically I'm using a combo of a local array var and GetRows to get my
recordset data and seems to work fine.

The only problem I have is that I know the main benefit of this GetRows is
to close the Recordset object as quick as poss, but using error checking on
an array doesn't seem to work as well. To explain my ramblings, I currently
use the following syntax:

.... connection stuff ...
.... command stuff ...
.... recordset stuff ...

' == I quickly get the recordset data into my array and close the object

IF NOT oRSv2.EOF THEN
arrSQLData = oRSv2.GetRows
END IF
oRSv2.close

' == I then check that the array is valid

IF IsArray(arrSQLData) THEN
..... do the recordset work
ELSE
.... no data available routine
END IF

This is where the problem seems to start, if there is no data then
arrSQLData generates an error because it was never defined as array in the
first place.

If I dim it as an array then it has the wrong initial parameters for the
variable data that GetRows puts into it and even if it did work then the
routine thinks it is an array which defeats the test.

At the moment, I've had to resort to using the following test instead:

oRSv.open oCmd
IF NOT oRSv.EOF THEN
arrSQLData = oRSv.GetRows
call BuildSearchResults()
ELSE
call NoSearchResults()
END IF
oRSv.close

But this then defeats the object (excuse the pun!!) of closing the recordset
object quickly because it has to go through the whole sub proc before it can
be closed.

Basically what I'm wanting to ask is what is the ipso facto code routine to
check whether any recordset data has come into the local array so that I can
do an IF.. THEN... ELSE on it.

PLEASE NOTE: another major problem of the above is that I generate the same
type of routine throughout a page to display different recordset data, which
can sometimes be 5 or 6 recordset calls to the same local var/array
(arrSQLData ) in one page. If for example, the 4th recordset doesn't
contain any data then the local var/array still passes the test because it
has the recordset data from the 3rd recordset. What's the best and most
efficient way to deal with this problem?

I know the above is a big ask, but I thank you in advance for any assistance
you can give.

Rgds

Laphan

[Aaron [SQL Server MVP]]

If the rs is EOF, then the array is going to be empty. So your code could
look like this:

if rs.eof then
response.write "no data"
response.end
else
myArray = rs.GetRows()
... do stuff with array
end if

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)





"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:41038ecf$1_3@127.0.0.1...

> Hi All
>
> I know the subject of this post might seem a bit cryptic, but I wanted to
> get all of the keywords in.
>
> Basically I'm using a combo of a local array var and GetRows to get my
> recordset data and seems to work fine.
>
> The only problem I have is that I know the main benefit of this GetRows is
> to close the Recordset object as quick as poss, but using error checking
> on
> an array doesn't seem to work as well. To explain my ramblings, I
> currently
> use the following syntax:
>
> ... connection stuff ...
> ... command stuff ...
> ... recordset stuff ...
>
> ' == I quickly get the recordset data into my array and close the object
>
> IF NOT oRSv2.EOF THEN
> arrSQLData = oRSv2.GetRows
> END IF
> oRSv2.close
>
> ' == I then check that the array is valid
>
> IF IsArray(arrSQLData) THEN
> .... do the recordset work
> ELSE
> .... no data available routine
> END IF
>
> This is where the problem seems to start, if there is no data then
> arrSQLData generates an error because it was never defined as array in the
> first place.
>
> If I dim it as an array then it has the wrong initial parameters for the
> variable data that GetRows puts into it and even if it did work then the
> routine thinks it is an array which defeats the test.
>
> At the moment, I've had to resort to using the following test instead:
>
> oRSv.open oCmd
> IF NOT oRSv.EOF THEN
> arrSQLData = oRSv.GetRows
> call BuildSearchResults()
> ELSE
> call NoSearchResults()
> END IF
> oRSv.close
>
> But this then defeats the object (excuse the pun!!) of closing the
> recordset
> object quickly because it has to go through the whole sub proc before it
> can
> be closed.
>
> Basically what I'm wanting to ask is what is the ipso facto code routine
> to
> check whether any recordset data has come into the local array so that I
> can
> do an IF.. THEN... ELSE on it.
>
> PLEASE NOTE: another major problem of the above is that I generate the
> same
> type of routine throughout a page to display different recordset data,
> which
> can sometimes be 5 or 6 recordset calls to the same local var/array
> (arrSQLData ) in one page. If for example, the 4th recordset doesn't
> contain any data then the local var/array still passes the test because it
> has the recordset data from the 3rd recordset. What's the best and most
> efficient way to deal with this problem?
>
> I know the above is a big ask, but I thank you in advance for any
> assistance
> you can give.
>
> Rgds
>
> Laphan
>
>
>
>
>

[Laphan]

Hi Aaron

Can't do the response.end, because this call might be halfway through me
generating the page.

My condensed problems are:

1) How do I zap the local var/array before doing my routine? I can't use
Erase local var/array cos it might not be an array and therefore a tyoe
mismatch will come up.

2) If I did:

IF NOT oRSv.EOF THEN
oRSv.close
arrSQLData = oRSv.GetRows
call BuildSearchResults()
ELSE
oRSv.close
call NoSearchResults()
END IF

Would the new positioning of the oRSv.close free up the resources better
whilst not relying on my array check?

Rgds

Laphan


Aaron [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote in message
news:uvPryfjcEHA.3792@TK2MSFTNGP09.phx.gbl...
If the rs is EOF, then the array is going to be empty. So your code could
look like this:

if rs.eof then
response.write "no data"
response.end
else
myArray = rs.GetRows()
... do stuff with array
end if

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)





"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:41038ecf$1_3@127.0.0.1...

> Hi All
>
> I know the subject of this post might seem a bit cryptic, but I wanted to
> get all of the keywords in.
>
> Basically I'm using a combo of a local array var and GetRows to get my
> recordset data and seems to work fine.
>
> The only problem I have is that I know the main benefit of this GetRows is
> to close the Recordset object as quick as poss, but using error checking
> on
> an array doesn't seem to work as well. To explain my ramblings, I
> currently
> use the following syntax:
>
> ... connection stuff ...
> ... command stuff ...
> ... recordset stuff ...
>
> ' == I quickly get the recordset data into my array and close the object
>
> IF NOT oRSv2.EOF THEN
> arrSQLData = oRSv2.GetRows
> END IF
> oRSv2.close
>
> ' == I then check that the array is valid
>
> IF IsArray(arrSQLData) THEN
> .... do the recordset work
> ELSE
> .... no data available routine
> END IF
>
> This is where the problem seems to start, if there is no data then
> arrSQLData generates an error because it was never defined as array in the
> first place.
>
> If I dim it as an array then it has the wrong initial parameters for the
> variable data that GetRows puts into it and even if it did work then the
> routine thinks it is an array which defeats the test.
>
> At the moment, I've had to resort to using the following test instead:
>
> oRSv.open oCmd
> IF NOT oRSv.EOF THEN
> arrSQLData = oRSv.GetRows
> call BuildSearchResults()
> ELSE
> call NoSearchResults()
> END IF
> oRSv.close
>
> But this then defeats the object (excuse the pun!!) of closing the
> recordset
> object quickly because it has to go through the whole sub proc before it
> can
> be closed.
>
> Basically what I'm wanting to ask is what is the ipso facto code routine
> to
> check whether any recordset data has come into the local array so that I
> can
> do an IF.. THEN... ELSE on it.
>
> PLEASE NOTE: another major problem of the above is that I generate the
> same
> type of routine throughout a page to display different recordset data,
> which
> can sometimes be 5 or 6 recordset calls to the same local var/array
> (arrSQLData ) in one page. If for example, the 4th recordset doesn't
> contain any data then the local var/array still passes the test because it
> has the recordset data from the 3rd recordset. What's the best and most
> efficient way to deal with this problem?
>
> I know the above is a big ask, but I thank you in advance for any
> assistance
> you can give.
>
> Rgds
>
> Laphan
>
>
>
>
>

[Aaron [SQL Server MVP]]

> Can't do the response.end, because this call might be halfway through me

> generating the page.

It was just an example. If it's rs.eof, then nothing in the else block gets
processed (could be the rest of the page).

A

[Ken Schaefer]

Use isArray()

<%
If not objRS.EOF then
arrResults = objRS.GetRows
End If
objRS.Close
Set objRS = Nothing
'
'
'
'
'
If isArray(arrResults) then
' there were results before
Else
' there were no results before
End If
%>

Cheers
Ken



"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:41038ecf$1_3@127.0.0.1...

> Hi All
>
> I know the subject of this post might seem a bit cryptic, but I wanted to
> get all of the keywords in.
>
> Basically I'm using a combo of a local array var and GetRows to get my
> recordset data and seems to work fine.
>
> The only problem I have is that I know the main benefit of this GetRows is
> to close the Recordset object as quick as poss, but using error checking

on

> an array doesn't seem to work as well. To explain my ramblings, I

currently

> use the following syntax:
>
> ... connection stuff ...
> ... command stuff ...
> ... recordset stuff ...
>
> ' == I quickly get the recordset data into my array and close the object
>
> IF NOT oRSv2.EOF THEN
> arrSQLData = oRSv2.GetRows
> END IF
> oRSv2.close
>
> ' == I then check that the array is valid
>
> IF IsArray(arrSQLData) THEN
> .... do the recordset work
> ELSE
> .... no data available routine
> END IF
>
> This is where the problem seems to start, if there is no data then
> arrSQLData generates an error because it was never defined as array in the
> first place.
>
> If I dim it as an array then it has the wrong initial parameters for the
> variable data that GetRows puts into it and even if it did work then the
> routine thinks it is an array which defeats the test.
>
> At the moment, I've had to resort to using the following test instead:
>
> oRSv.open oCmd
> IF NOT oRSv.EOF THEN
> arrSQLData = oRSv.GetRows
> call BuildSearchResults()
> ELSE
> call NoSearchResults()
> END IF
> oRSv.close
>
> But this then defeats the object (excuse the pun!!) of closing the

recordset

> object quickly because it has to go through the whole sub proc before it

can

> be closed.
>
> Basically what I'm wanting to ask is what is the ipso facto code routine

to

> check whether any recordset data has come into the local array so that I

can

> do an IF.. THEN... ELSE on it.
>
> PLEASE NOTE: another major problem of the above is that I generate the

same

> type of routine throughout a page to display different recordset data,

which

> can sometimes be 5 or 6 recordset calls to the same local var/array
> (arrSQLData ) in one page. If for example, the 4th recordset doesn't
> contain any data then the local var/array still passes the test because it
> has the recordset data from the 3rd recordset. What's the best and most
> efficient way to deal with this problem?
>
> I know the above is a big ask, but I thank you in advance for any

assistance

> you can give.
>
> Rgds
>
> Laphan
>
>
>
>
>

[Chris Hohmann]

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:41038ecf$1_3@127.0.0.1...

> Hi All
>
> I know the subject of this post might seem a bit cryptic, but I wanted to
> get all of the keywords in.
>
> Basically I'm using a combo of a local array var and GetRows to get my
> recordset data and seems to work fine.
>
> The only problem I have is that I know the main benefit of this GetRows is
> to close the Recordset object as quick as poss, but using error checking

on

> an array doesn't seem to work as well. To explain my ramblings, I

currently

> use the following syntax:
>
> ... connection stuff ...
> ... command stuff ...
> ... recordset stuff ...
>
> ' == I quickly get the recordset data into my array and close the object
>
> IF NOT oRSv2.EOF THEN
> arrSQLData = oRSv2.GetRows
> END IF
> oRSv2.close
>
> ' == I then check that the array is valid
>
> IF IsArray(arrSQLData) THEN
> .... do the recordset work
> ELSE
> .... no data available routine
> END IF
>
> This is where the problem seems to start, if there is no data then
> arrSQLData generates an error because it was never defined as array in the
> first place.
>
> If I dim it as an array then it has the wrong initial parameters for the
> variable data that GetRows puts into it and even if it did work then the
> routine thinks it is an array which defeats the test.
>
> At the moment, I've had to resort to using the following test instead:
>
> oRSv.open oCmd
> IF NOT oRSv.EOF THEN
> arrSQLData = oRSv.GetRows
> call BuildSearchResults()
> ELSE
> call NoSearchResults()
> END IF
> oRSv.close
>
> But this then defeats the object (excuse the pun!!) of closing the

recordset

> object quickly because it has to go through the whole sub proc before it

can

> be closed.
>
> Basically what I'm wanting to ask is what is the ipso facto code routine

to

> check whether any recordset data has come into the local array so that I

can

> do an IF.. THEN... ELSE on it.
>
> PLEASE NOTE: another major problem of the above is that I generate the

same

> type of routine throughout a page to display different recordset data,

which

> can sometimes be 5 or 6 recordset calls to the same local var/array
> (arrSQLData ) in one page. If for example, the 4th recordset doesn't
> contain any data then the local var/array still passes the test because it
> has the recordset data from the 3rd recordset. What's the best and most
> efficient way to deal with this problem?
>
> I know the above is a big ask, but I thank you in advance for any

assistance

> you can give.

You need to do is declare arrSQLData as a variant because it could end up
being an array or it could end up being "empty". I usually use something
like this:

<%
Dim cn, rs, arr, j, jMax
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open gstrConn 'Your DSNLess OLEDB Connection string here
cn.MyStoredProcedure Parameter1,Paramter2, ..., ParameterN, rs
If Not rs.EOF Then arr = rs.GetRows()
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing

If IsArray(arr) Then
jMax = UBound(arr,2)
For j = 0 To jMax
'Do row processing here
Next
Else
'Report no data
End If
%>

[Chris Hohmann]

"Astra" <info@NoEmail.com> wrote in message news:410f59fa$1_4@127.0.0.1...

> "Chris Hohmann" <nospam@thankyou.com> wrote in message
> news:%232YsztzcEHA.3476@tk2msftngp13.phx.gbl...
> "Laphan" <news@DoNotEmailMe.co.uk> wrote in message
> news:41038ecf$1_3@127.0.0.1...

> > Hi All
> >
> > I know the subject of this post might seem a bit cryptic, but I wanted

to

> > get all of the keywords in.
> >
> > Basically I'm using a combo of a local array var and GetRows to get my
> > recordset data and seems to work fine.
> >
> > The only problem I have is that I know the main benefit of this GetRows

is

> > to close the Recordset object as quick as poss, but using error checking

> on

> > an array doesn't seem to work as well. To explain my ramblings, I

> currently

> > use the following syntax:
> >
> > ... connection stuff ...
> > ... command stuff ...
> > ... recordset stuff ...
> >
> > ' == I quickly get the recordset data into my array and close the object
> >
> > IF NOT oRSv2.EOF THEN
> > arrSQLData = oRSv2.GetRows
> > END IF
> > oRSv2.close
> >
> > ' == I then check that the array is valid
> >
> > IF IsArray(arrSQLData) THEN
> > .... do the recordset work
> > ELSE
> > .... no data available routine
> > END IF
> >
> > This is where the problem seems to start, if there is no data then
> > arrSQLData generates an error because it was never defined as array in

the

> > first place.
> >
> > If I dim it as an array then it has the wrong initial parameters for the
> > variable data that GetRows puts into it and even if it did work then the
> > routine thinks it is an array which defeats the test.
> >
> > At the moment, I've had to resort to using the following test instead:
> >
> > oRSv.open oCmd
> > IF NOT oRSv.EOF THEN
> > arrSQLData = oRSv.GetRows
> > call BuildSearchResults()
> > ELSE
> > call NoSearchResults()
> > END IF
> > oRSv.close
> >
> > But this then defeats the object (excuse the pun!!) of closing the

> recordset

> > object quickly because it has to go through the whole sub proc before it

> can

> > be closed.
> >
> > Basically what I'm wanting to ask is what is the ipso facto code routine

> to

> > check whether any recordset data has come into the local array so that I

> can

> > do an IF.. THEN... ELSE on it.
> >
> > PLEASE NOTE: another major problem of the above is that I generate the

> same

> > type of routine throughout a page to display different recordset data,

> which

> > can sometimes be 5 or 6 recordset calls to the same local var/array
> > (arrSQLData ) in one page. If for example, the 4th recordset doesn't
> > contain any data then the local var/array still passes the test because

it

> > has the recordset data from the 3rd recordset. What's the best and most
> > efficient way to deal with this problem?
> >
> > I know the above is a big ask, but I thank you in advance for any

> assistance

> > you can give.

>
> You need to do is declare arrSQLData as a variant because it could end up
> being an array or it could end up being "empty". I usually use something
> like this:
>
> <%
> Dim cn, rs, arr, j, jMax
> Set cn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> cn.Open gstrConn 'Your DSNLess OLEDB Connection string here
> cn.MyStoredProcedure Parameter1,Paramter2, ..., ParameterN, rs
> If Not rs.EOF Then arr = rs.GetRows()
> rs.Close : Set rs = Nothing
> cn.Close : Set cn = Nothing
>
> If IsArray(arr) Then
> jMax = UBound(arr,2)
> For j = 0 To jMax
> 'Do row processing here
> Next
> Else
> 'Report no data
> End If
> %>
>
> Hi Chris
>
> I have declared it as a variant, so what am I doing wrong?
>
> What about the erase bit as well?
>
> Rgds
>
> Robbie

I have no idea what you're doing wrong since I can't see the code you are
using. Please post the code. Also, please post your responses below the
quoted material or interlaced. Placing your response at the top makes it
very difficult to follow the thread of the discussion.