Help! How do I get Record Count?

Help! How do I get Record Count?

Post by Newz » Sun, 11 Feb 2001 12:28:30



I'm using a Stored Procedure:

--*********************

As

--*********************

I retrive the recordset in ADO Like this:
'**************
Set Cmd.ActiveConnection = Db
Cmd.CommandText = "MyProc"
Cmd.CommandType = 4
Cmd.Parameters.Refresh
Cmd.Parameters(1) = Request.Form("Password")
 rsSec.Open Cmd
'**************

rsSec.Recordcount returns a -1. I figure SQL Server isn't returning that
value correct?
If I try to use an Output parameter, I can't retrieve the recordset.

How can I know how many records I have and still retrieve the recordset?

Thanks
Marl K. Atkins
Microsoft Certified Professional
SoftLink Systems, Inc.

 
 
 

Help! How do I get Record Count?

Post by Rey Nu?e » Sun, 11 Feb 2001 20:16:57


Hi,

The cursor type of the Recordset object affects whether the number of
records can be determined.

The RecordCount property will return -1 for a forward-only cursor, the
actual count for a static or keyset cursor, and either -1 or the actual
count, depending on the data source, for a dynamic cursor.

--
Rey Nu?ez
http://www.aspwatch.com
http://db101.terrashare.com

 
 
 

Help! How do I get Record Count?

Post by Hugo Sil » Sun, 11 Feb 2001 21:46:22


Hi Marl,

If you are using a cursortype other than adOpenForwarOnly then you can
retrieve the number of records in your recordset reading the
RecordCount property. Remember that RecordCount only gives you the
right number of records after it moves to the last record so you must
first execute the MoveLast method.

Hugo Silva


>I'm using a Stored Procedure:

>--*********************
>Create Proc MyProc

>As

>--*********************

>I retrive the recordset in ADO Like this:
>'**************
>Set Cmd.ActiveConnection = Db
>Cmd.CommandText = "MyProc"
>Cmd.CommandType = 4
>Cmd.Parameters.Refresh
>Cmd.Parameters(1) = Request.Form("Password")
> rsSec.Open Cmd
>'**************

>rsSec.Recordcount returns a -1. I figure SQL Server isn't returning that
>value correct?
>If I try to use an Output parameter, I can't retrieve the recordset.

>How can I know how many records I have and still retrieve the recordset?

>Thanks
>Marl K. Atkins
>Microsoft Certified Professional
>SoftLink Systems, Inc.


Hugo Silva
 
 
 

Help! How do I get Record Count?

Post by Newz » Sun, 11 Feb 2001 23:40:30


Thanks for your reply.
How do I set the CursorType?

I tried rsSec.CursorType = 3
It still gives me a forwardonly cursor.


> Hi Marl,

> If you are using a cursortype other than adOpenForwarOnly then you can
> retrieve the number of records in your recordset reading the
> RecordCount property. Remember that RecordCount only gives you the
> right number of records after it moves to the last record so you must
> first execute the MoveLast method.

> Hugo Silva


> >I'm using a Stored Procedure:

> >--*********************
> >Create Proc MyProc

> >As

> >--*********************

> >I retrive the recordset in ADO Like this:
> >'**************
> >Set Cmd.ActiveConnection = Db
> >Cmd.CommandText = "MyProc"
> >Cmd.CommandType = 4
> >Cmd.Parameters.Refresh
> >Cmd.Parameters(1) = Request.Form("Password")
> > rsSec.Open Cmd
> >'**************

> >rsSec.Recordcount returns a -1. I figure SQL Server isn't returning that
> >value correct?
> >If I try to use an Output parameter, I can't retrieve the recordset.

> >How can I know how many records I have and still retrieve the recordset?

> >Thanks
> >Marl K. Atkins
> >Microsoft Certified Professional
> >SoftLink Systems, Inc.

> Hugo Silva

 
 
 

Help! How do I get Record Count?

Post by Rey Nu?e » Mon, 12 Feb 2001 02:00:37


You can do that either BEFORE opening the Recordset object, for example

recordset.CursorType = 3

or can be passed as arguments in the recordSet.Open method call.

recordset.Open strQuery, strProvider, adOpenStatic, , adCmdText

--
Rey Nu?ez
http://www.aspwatch.com
http://db101.terrashare.com

 
 
 

Help! How do I get Record Count?

Post by Newz » Wed, 14 Feb 2001 00:03:06


Thanks:

I was doing that and it wasn't working.
I determined that my Stored Procedure was forcing a ForwardOnly somehow.
I think it's because I use 2 selects to get my results.

Can you tell me how I should do this to get just the recordset I want in a
Static cursor??


As


     begin
         Select 'Login and Password are required.' 'SQLError'
     end
else
    --********* HERE IS THE FIRST STATEMENT *********


            -- ********* HERE IS THE ONE I WANT ************
          Select 'None' 'SQLError', SecGroups.* From SecGroups Where ID

    Else
      begin
           Select 'Employee does not exist' 'SQLError'
      end

Thanks
Marl K. Atkins
Microsoft Certified Professional
Softlink Systems, Inc.


Quote:> You can do that either BEFORE opening the Recordset object, for example

> recordset.CursorType = 3

> or can be passed as arguments in the recordSet.Open method call.

> recordset.Open strQuery, strProvider, adOpenStatic, , adCmdText

> --
> Rey Nu?ez
> http://www.aspwatch.com
> http://db101.terrashare.com

 
 
 

Help! How do I get Record Count?

Post by Luca Faiazz » Wed, 14 Feb 2001 19:28:58


I think you should use client cursor to find out the record count. Check the
cursor location setting instead of cursor type.

Luca


> Thanks:

> I was doing that and it wasn't working.
> I determined that my Stored Procedure was forcing a ForwardOnly somehow.
> I think it's because I use 2 selects to get my results.

> Can you tell me how I should do this to get just the recordset I want in a
> Static cursor??

> Alter Proc usp_AdminLogin


> As


>      begin
>          Select 'Login and Password are required.' 'SQLError'
>      end
> else
>     --********* HERE IS THE FIRST STATEMENT *********



>             -- ********* HERE IS THE ONE I WANT ************
>           Select 'None' 'SQLError', SecGroups.* From SecGroups Where ID

>     Else
>       begin
>            Select 'Employee does not exist' 'SQLError'
>       end

> Thanks
> Marl K. Atkins
> Microsoft Certified Professional
> Softlink Systems, Inc.



> > You can do that either BEFORE opening the Recordset object, for example

> > recordset.CursorType = 3

> > or can be passed as arguments in the recordSet.Open method call.

> > recordset.Open strQuery, strProvider, adOpenStatic, , adCmdText

> > --
> > Rey Nu?ez
> > http://www.aspwatch.com
> > http://db101.terrashare.com

 
 
 

Help! How do I get Record Count?

Post by RW » Thu, 15 Feb 2001 05:00:40


You could try another approach :

Create Proc MyProc

  As


and then:

Dim iRecCount ' as integer --<< New !!

Set Cmd.ActiveConnection = Db
Cmd.CommandText = "MyProc"
Cmd.CommandType = 4
Cmd.Parameters.Refresh
Cmd.Parameters(1) = Request.Form("Password")
'--rsSec.Open Cmd  '-- Old
Set rsSec = Cmd.Execute  ' --<< New !!
iRecCount = Cmd.Parameters(0) ' --<< New !!

RW



> I'm using a Stored Procedure:

> --*********************
> Create Proc MyProc

> As

> --*********************

> I retrive the recordset in ADO Like this:
> '**************
> Set Cmd.ActiveConnection = Db
> Cmd.CommandText = "MyProc"
> Cmd.CommandType = 4
> Cmd.Parameters.Refresh
> Cmd.Parameters(1) = Request.Form("Password")
>  rsSec.Open Cmd
> '**************

> rsSec.Recordcount returns a -1. I figure SQL Server isn't returning that
> value correct?
> If I try to use an Output parameter, I can't retrieve the recordset.

> How can I know how many records I have and still retrieve the recordset?

> Thanks
> Marl K. Atkins
> Microsoft Certified Professional
> SoftLink Systems, Inc.


 
 
 

Help! How do I get Record Count?

Post by Newz » Thu, 15 Feb 2001 12:12:03


OK, thanks

> I think you should use client cursor to find out the record count. Check
the
> cursor location setting instead of cursor type.

> Luca



> > Thanks:

> > I was doing that and it wasn't working.
> > I determined that my Stored Procedure was forcing a ForwardOnly somehow.
> > I think it's because I use 2 selects to get my results.

> > Can you tell me how I should do this to get just the recordset I want in
a
> > Static cursor??

> > Alter Proc usp_AdminLogin


> > As


> >      begin
> >          Select 'Login and Password are required.' 'SQLError'
> >      end
> > else
> >     --********* HERE IS THE FIRST STATEMENT *********

=


> >             -- ********* HERE IS THE ONE I WANT ************
> >           Select 'None' 'SQLError', SecGroups.* From SecGroups Where ID

> >     Else
> >       begin
> >            Select 'Employee does not exist' 'SQLError'
> >       end

> > Thanks
> > Marl K. Atkins
> > Microsoft Certified Professional
> > Softlink Systems, Inc.



> > > You can do that either BEFORE opening the Recordset object, for
example

> > > recordset.CursorType = 3

> > > or can be passed as arguments in the recordSet.Open method call.

> > > recordset.Open strQuery, strProvider, adOpenStatic, , adCmdText

> > > --
> > > Rey Nu?ez
> > > http://www.aspwatch.com
> > > http://db101.terrashare.com

 
 
 

Help! How do I get Record Count?

Post by Newz » Thu, 15 Feb 2001 12:27:33


Thanks very much for the reply. I'm having a hard time getting answers.

I think my problem is that I've actually got 2 select statements.I found out
that if I just request a single statement w/ no "code" it will let me have a
Static cursor type and a record count.

Basically, I need to find out if I've got a valid employee and then if so,
what the Security Clearance for that employee is.
I really only want to return either  ** "SQLError" OR  the **SECOND
SELECT**. SQLError could have 4 values (and I don't even need the 4th):
"Login and Password are required";
"Employee doesn't exist";
"Employee exists but has no clearance" <<-- not currently in the Proc
because it crashes ADO
"None"

If I try to send the "Employee exists but no clearance", ADO crashes, I
presume because I'm accidentally sending it too many SQL Statements.
How should I do this? I sure would appreciate some help. This one's kickin
my butt.

I guess I better post the whole proc:
********************************************************

As


 begin
     Select 'Login and Password are required.' 'SQLError'
 end
else
    --****** FIRST SELECT  this one just finds out if I've got a valid
employee ************


            -- ****** SECOND SELECT this one is the one I want to return to
ADO ********
              Select 'None' 'SQLError', SecGroups.* From SecGroups Where ID

            -- **** If above Select returns 0 rows:      Select "Employee
exists but no clearance"  "SQLError"     *****
    Else
          begin
               Select 'Employee does not exist' 'SQLError'
          end
***********************************************************


> You could try another approach :

> Create Proc MyProc

>   As


> and then:

> Dim iRecCount ' as integer --<< New !!

> Set Cmd.ActiveConnection = Db
> Cmd.CommandText = "MyProc"
> Cmd.CommandType = 4
> Cmd.Parameters.Refresh
> Cmd.Parameters(1) = Request.Form("Password")
> '--rsSec.Open Cmd  '-- Old
> Set rsSec = Cmd.Execute  ' --<< New !!
> iRecCount = Cmd.Parameters(0) ' --<< New !!

> RW



> > I'm using a Stored Procedure:

> > --*********************
> > Create Proc MyProc

> > As

> > --*********************

> > I retrive the recordset in ADO Like this:
> > '**************
> > Set Cmd.ActiveConnection = Db
> > Cmd.CommandText = "MyProc"
> > Cmd.CommandType = 4
> > Cmd.Parameters.Refresh
> > Cmd.Parameters(1) = Request.Form("Password")
> >  rsSec.Open Cmd
> > '**************

> > rsSec.Recordcount returns a -1. I figure SQL Server isn't returning that
> > value correct?
> > If I try to use an Output parameter, I can't retrieve the recordset.

> > How can I know how many records I have and still retrieve the recordset?

> > Thanks
> > Marl K. Atkins
> > Microsoft Certified Professional
> > SoftLink Systems, Inc.


 
 
 

Help! How do I get Record Count?

Post by RW » Fri, 16 Feb 2001 00:36:24


How about this ?

 Alter Proc usp_AdminLogin


 As


  begin
      Select 'Login and Password are required.' 'SQLError'
  end

 else
    BEGIN
     --****** FIRST SELECT  this one just finds out if I've got a valid
employee ************


        BEGIN
             -- ****** SECOND SELECT this one is the one I want to return to
ADO ********

          IF EXISTS (  Select * From SecGroups Where ID
                             In ( Select SecGroupID From Security Where

              Select 'None' 'SQLError', SecGroups.* From SecGroups Where ID

            ELSE
             -- **** If above Select returns 0 rows:
                    Select "Employee exists but no clearance"  "SQLError"

        END

     Else
           begin
                Select 'Employee does not exist' 'SQLError'
           end

END

This would return only one result set.

I would try also:

rsSec.CursorLocation = 3  ' adUseClient
rsSec.CursorType = 3       ' adOpenStatic
rsSec.Open Cmd

That always correctly sets RecordCount

Hope this helps

RW



> Thanks very much for the reply. I'm having a hard time getting answers.

> I think my problem is that I've actually got 2 select statements.I found
out
> that if I just request a single statement w/ no "code" it will let me have
a
> Static cursor type and a record count.

> Basically, I need to find out if I've got a valid employee and then if so,
> what the Security Clearance for that employee is.
> I really only want to return either  ** "SQLError" OR  the **SECOND
> SELECT**. SQLError could have 4 values (and I don't even need the 4th):
> "Login and Password are required";
> "Employee doesn't exist";
> "Employee exists but has no clearance" <<-- not currently in the Proc
> because it crashes ADO
> "None"

> If I try to send the "Employee exists but no clearance", ADO crashes, I
> presume because I'm accidentally sending it too many SQL Statements.
> How should I do this? I sure would appreciate some help. This one's kickin
> my butt.

> I guess I better post the whole proc:
> ********************************************************
> Alter Proc usp_AdminLogin


> As


>  begin
>      Select 'Login and Password are required.' 'SQLError'
>  end
> else
>     --****** FIRST SELECT  this one just finds out if I've got a valid
> employee ************



>             -- ****** SECOND SELECT this one is the one I want to return
to
> ADO ********
>               Select 'None' 'SQLError', SecGroups.* From SecGroups Where
ID

>             -- **** If above Select returns 0 rows:      Select "Employee
> exists but no clearance"  "SQLError"     *****
>     Else
>           begin
>                Select 'Employee does not exist' 'SQLError'
>           end
> ***********************************************************



> > You could try another approach :

> > Create Proc MyProc

> >   As


> > and then:

> > Dim iRecCount ' as integer --<< New !!

> > Set Cmd.ActiveConnection = Db
> > Cmd.CommandText = "MyProc"
> > Cmd.CommandType = 4
> > Cmd.Parameters.Refresh
> > Cmd.Parameters(1) = Request.Form("Password")
> > '--rsSec.Open Cmd  '-- Old
> > Set rsSec = Cmd.Execute  ' --<< New !!
> > iRecCount = Cmd.Parameters(0) ' --<< New !!

> > RW



> > > I'm using a Stored Procedure:

> > > --*********************
> > > Create Proc MyProc

> > > As

> > > --*********************

> > > I retrive the recordset in ADO Like this:
> > > '**************
> > > Set Cmd.ActiveConnection = Db
> > > Cmd.CommandText = "MyProc"
> > > Cmd.CommandType = 4
> > > Cmd.Parameters.Refresh
> > > Cmd.Parameters(1) = Request.Form("Password")
> > >  rsSec.Open Cmd
> > > '**************

> > > rsSec.Recordcount returns a -1. I figure SQL Server isn't returning
that
> > > value correct?
> > > If I try to use an Output parameter, I can't retrieve the recordset.

> > > How can I know how many records I have and still retrieve the
recordset?

> > > Thanks
> > > Marl K. Atkins
> > > Microsoft Certified Professional
> > > SoftLink Systems, Inc.


 
 
 

Help! How do I get Record Count?

Post by Joshua Emel » Fri, 16 Feb 2001 06:45:11


try a MoveFirst, then MoveLast ... the record count property should be good.


> OK, thanks


> > I think you should use client cursor to find out the record count. Check
> the
> > cursor location setting instead of cursor type.

> > Luca



> > > Thanks:

> > > I was doing that and it wasn't working.
> > > I determined that my Stored Procedure was forcing a ForwardOnly
somehow.
> > > I think it's because I use 2 selects to get my results.

> > > Can you tell me how I should do this to get just the recordset I want
in
> a
> > > Static cursor??

> > > Alter Proc usp_AdminLogin


> > > As


> > >      begin
> > >          Select 'Login and Password are required.' 'SQLError'
> > >      end
> > > else
> > >     --********* HERE IS THE FIRST STATEMENT *********

Password
> =


> > >             -- ********* HERE IS THE ONE I WANT ************
> > >           Select 'None' 'SQLError', SecGroups.* From SecGroups Where
ID

> > >     Else
> > >       begin
> > >            Select 'Employee does not exist' 'SQLError'
> > >       end

> > > Thanks
> > > Marl K. Atkins
> > > Microsoft Certified Professional
> > > Softlink Systems, Inc.



> > > > You can do that either BEFORE opening the Recordset object, for
> example

> > > > recordset.CursorType = 3

> > > > or can be passed as arguments in the recordSet.Open method call.

> > > > recordset.Open strQuery, strProvider, adOpenStatic, , adCmdText

> > > > --
> > > > Rey Nu?ez
> > > > http://www.aspwatch.com
> > > > http://db101.terrashare.com

 
 
 

Help! How do I get Record Count?

Post by Tom Gilme » Fri, 16 Feb 2001 22:15:44


That's not required for ADO. An accurate record count is provided by static
and keyset cursors. A dynamic cursor may provide a record count. A forward
only cursor never returns a record count.

 - Tom


Quote:> try a MoveFirst, then MoveLast ... the record count property should be

good.
 
 
 

Help! How do I get Record Count?

Post by Alan » Sun, 18 Feb 2001 01:05:40


Read "Counting your rows before they hatch" in the MSDN. Also, you can use a
simple stored procedure to return the number of records, then use Getrows to
retrieve them

> I'm using a Stored Procedure:

> --*********************
> Create Proc MyProc

> As

> --*********************

> I retrive the recordset in ADO Like this:
> '**************
> Set Cmd.ActiveConnection = Db
> Cmd.CommandText = "MyProc"
> Cmd.CommandType = 4
> Cmd.Parameters.Refresh
> Cmd.Parameters(1) = Request.Form("Password")
> ?rsSec.Open Cmd
> '**************

> rsSec.Recordcount returns a -1. I figure SQL Server isn't returning that
> value correct?
> If I try to use an Output parameter, I can't retrieve the recordset.

> How can I know how many records I have and still retrieve the recordset?

> Thanks
> Marl K. Atkins
> Microsoft Certified Professional
> SoftLink Systems, Inc.