ADODB.command Object and RecordCount 
Author Message
 ADODB.command Object and RecordCount

Hi Guys,
I need a little help. Im using the ADODB.command Object to send a
SELECT statement to a MySQL-DB via ODBC. Now I wanted to find out how
many Recordsets it returns and I don't know how to do this. I tried to
use the RecordsAffected Option in the command.Execute Function, but
that wouldn't work out, it seems to return always 0 recordsets. Is
there any other Possibility to get the number of recordsets, like
maybe the Recordset.RecordCount Function?

thx in advance
David.



Fri, 09 Sep 2005 18:08:11 GMT  
 ADODB.command Object and RecordCount
[This followup was posted to microsoft.public.vb.database.ado]



Quote:
> Hi Guys,
> I need a little help. Im using the ADODB.command Object to send a
> SELECT statement to a MySQL-DB via ODBC. Now I wanted to find out how
> many Recordsets it returns and I don't know how to do this. I tried to
> use the RecordsAffected Option in the command.Execute Function, but
> that wouldn't work out, it seems to return always 0 recordsets. Is
> there any other Possibility to get the number of recordsets, like
> maybe the Recordset.RecordCount Function?

> thx in advance
> David.

The following code is extracted from one of my VB6 programs running on
Windows NT4. You should test it to make sure it runs on your system, but
it should work OK.

    Dim myConnection As ADODB.Connection
    Dim myRecordSet As ADODB.Recordset
    Dim strSQL As String
    Dim sqlCount As Integer

    strSQL = "SELECT * from name_of_table"

    ' Open the database table and fetch the column values
    Set myConnection = New ADODB.Connection

myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist
Security Info=False;User ID=Admin;Data Source=F:\Program Files\Microsoft
Visual Studio\VB98\newBiblio.MDB;Mode=Share Deny None"

    myConnection.CursorLocation = adUseClient

    myConnection.Open

    ' Create a Recordset by executing a SQL statement
    Set myRecordSet = myConnection.Execute(strSQL)

    If myRecordSet.EOF = True Then
        sqlCount = 0
    Else
        sqlCount = myRecordSet.RecordCount
    End If

    sqlCount = myRecordSet.RecordCount

--
---------

Barry Kimelman
Winnipeg, Manitoba, Canada



Fri, 09 Sep 2005 22:35:10 GMT  
 ADODB.command Object and RecordCount

Quote:
>    sqlCount = myRecordSet.RecordCount

thx for your help, but in this program you are using the Recordset
Object and not the Command Object. I know how to do it with a
Recordset Object, but I was wondering whether it wouldn't work with
the Command.Object, too, because I think I heard st like that.

Anyway thxalot, but thats unfortunately not the answer Ive been
looking for :(

bye
David.



Fri, 09 Sep 2005 22:48:05 GMT  
 ADODB.command Object and RecordCount
Well.. I'm not sure.. but i think you can use something like this

Dim myCommand as New ADODB.Command
' * Set all properties and execute all methods you need to and then
msgbox myCommand.recordset.recordcount

If I'm not mistaken, you have a Recordset Object inside the Command
object...
I think you can set a Recordset as your Command.Open....

As I'm out of VB for some hours I can guarantee none of these information
for you, but I think I'm pretty close of the path you gotta take....

Daniel



Quote:

> >    sqlCount = myRecordSet.RecordCount

> thx for your help, but in this program you are using the Recordset
> Object and not the Command Object. I know how to do it with a
> Recordset Object, but I was wondering whether it wouldn't work with
> the Command.Object, too, because I think I heard st like that.

> Anyway thxalot, but thats unfortunately not the answer Ive been
> looking for :(

> bye
> David.



Fri, 09 Sep 2005 23:56:03 GMT  
 ADODB.command Object and RecordCount

Quote:
>msgbox myCommand.recordset.recordcount

Unfortunately this doesn't work out. There doesn't seem to be a
recordset Object inside the Command Object.

Quote:
>If I'm not mistaken, you have a Recordset Object inside the Command
>object...
>I think you can set a Recordset as your Command.Open....

Sorry, but I guess I can't follow you quite right. Do you mean st.
like "Set MyCommandObject = New ADODB.Recordset"?

Quote:
>As I'm out of VB for some hours I can guarantee none of these information
>for you, but I think I'm pretty close of the path you gotta take....

Anyway, thanks for your help

Quote:
>Daniel

bye
David.


Sat, 10 Sep 2005 00:10:32 GMT  
 ADODB.command Object and RecordCount
Quote:
> Sorry, but I guess I can't follow you quite right. Do you mean st.
> like "Set MyCommandObject = New ADODB.Recordset"?

No, I meant:
Dim rs as ADODB.Recordset
Dim cmd as ADODB.Command
Set cmd = New ADODB.Command

' * Set all Properties of your Command

Set rs = cmd.Open

For not to keep giving you only suggestions, I'm moving to a machine w/ VB
and performing some tests... BRB with a more consistent solution..
Don't run away =)

Daniel



Quote:

> >msgbox myCommand.recordset.recordcount

> Unfortunately this doesn't work out. There doesn't seem to be a
> recordset Object inside the Command Object.

> >If I'm not mistaken, you have a Recordset Object inside the Command
> >object...
> >I think you can set a Recordset as your Command.Open....

> Sorry, but I guess I can't follow you quite right. Do you mean st.
> like "Set MyCommandObject = New ADODB.Recordset"?

> >As I'm out of VB for some hours I can guarantee none of these information
> >for you, but I think I'm pretty close of the path you gotta take....

> Anyway, thanks for your help

> >Daniel

> bye
> David.



Sat, 10 Sep 2005 00:32:22 GMT  
 ADODB.command Object and RecordCount
David,

If you open recordset based on SELECT statement, it does not matter if it is
opened by Command or using Connection or Open method of the recordset.
Anyway you may be able to get number of records from RecordCount property of
recordset. But it depends on settings of your cursor. In case, if using
specific cursor settings, provider cannot return actual record count, then
it will return -1 as a value. To get actual record count you would need to
open recordset on client side as a static one. Then RecordCount will return
you that value. See more details about that in next KB

http://support.microsoft.com/default.aspx?scid=kb;en-us;194973

--
Val Mazur
Microsoft MVP


Quote:
> Hi Guys,
> I need a little help. Im using the ADODB.command Object to send a
> SELECT statement to a MySQL-DB via ODBC. Now I wanted to find out how
> many Recordsets it returns and I don't know how to do this. I tried to
> use the RecordsAffected Option in the command.Execute Function, but
> that wouldn't work out, it seems to return always 0 recordsets. Is
> there any other Possibility to get the number of recordsets, like
> maybe the Recordset.RecordCount Function?

> thx in advance
> David.



Sat, 10 Sep 2005 00:51:42 GMT  
 ADODB.command Object and RecordCount
I've tried to play with the rs Cursors, but when i do a "SET myRS =
MyCommand.EXECUTE" it sets my cursor to adUseServer automatically...
If I were needing this solutions I'd not be confortable with using:
Set myRs = myCommand.Execute
myRs.MoveLast
myRecordCount = myRs.AbsolutePosition
But it solves the problem...
I found this Exemple into MSDN Library.. might be helpful....

(Yeap.. it's a little big, but might help you)
ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and
Direction Properties Example
This example uses the ActiveConnection, CommandText, CommandTimeout,
CommandType, Size, and Direction properties to execute a stored procedure.

Public Sub ActiveConnectionX()

   Dim cnn1 As ADODB.Connection
   Dim cmdByRoyalty As ADODB.Command
   Dim prmByRoyalty As ADODB.Parameter
   Dim rstByRoyalty As ADODB.Recordset
   Dim rstAuthors As ADODB.Recordset
   Dim intRoyalty As Integer
   Dim strAuthorID As String
   Dim strCnn As String

   ' Define a command object for a stored procedure.
   Set cnn1 = New ADODB.Connection
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   cnn1.Open strCnn
   Set cmdByRoyalty = New ADODB.Command
   Set cmdByRoyalty.ActiveConnection = cnn1
   cmdByRoyalty.CommandText = "byroyalty"
   cmdByRoyalty.CommandType = adCmdStoredProc
   cmdByRoyalty.CommandTimeout = 15

   ' Define the stored procedure's input parameter.
   intRoyalty = Trim(InputBox( _
      "Enter royalty:"))
   Set prmByRoyalty = New ADODB.Parameter
   prmByRoyalty.Type = adInteger
   prmByRoyalty.Size = 3
   prmByRoyalty.Direction = adParamInput
   prmByRoyalty.Value = intRoyalty
   cmdByRoyalty.Parameters.Append prmByRoyalty

   ' Create a recordset by executing the command.
   Set rstByRoyalty = cmdByRoyalty.Execute()

   ' Open the Authors table to get author names for display.
   Set rstAuthors = New ADODB.Recordset
   rstAuthors.Open "authors", strCnn, , , adCmdTable

   ' Print current data in the recordset, adding
   ' author names from Authors table.
   Debug.Print "Authors with " & intRoyalty & _
      " percent royalty"
   Do While Not rstByRoyalty.EOF
      strAuthorID = rstByRoyalty!au_id
      Debug.Print , rstByRoyalty!au_id & ", ";
      rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
      Debug.Print rstAuthors!au_fname & " " & _
         rstAuthors!au_lname
      rstByRoyalty.MoveNext
   Loop

   rstByRoyalty.Close
   rstAuthors.Close
   cnn1.Close

End Sub



Quote:
> David,

> If you open recordset based on SELECT statement, it does not matter if it
is
> opened by Command or using Connection or Open method of the recordset.
> Anyway you may be able to get number of records from RecordCount property
of
> recordset. But it depends on settings of your cursor. In case, if using
> specific cursor settings, provider cannot return actual record count, then
> it will return -1 as a value. To get actual record count you would need to
> open recordset on client side as a static one. Then RecordCount will
return
> you that value. See more details about that in next KB

> http://support.microsoft.com/default.aspx?scid=kb;en-us;194973

> --
> Val Mazur
> Microsoft MVP



> > Hi Guys,
> > I need a little help. Im using the ADODB.command Object to send a
> > SELECT statement to a MySQL-DB via ODBC. Now I wanted to find out how
> > many Recordsets it returns and I don't know how to do this. I tried to
> > use the RecordsAffected Option in the command.Execute Function, but
> > that wouldn't work out, it seems to return always 0 recordsets. Is
> > there any other Possibility to get the number of recordsets, like
> > maybe the Recordset.RecordCount Function?

> > thx in advance
> > David.



Sat, 10 Sep 2005 00:54:28 GMT  
 ADODB.command Object and RecordCount
Daniel,

This happens because by-default Command object inherits cursor location
options from the connection, not from the recordset. Try to set
CursorLocation of your connection to adUseClient before opening of
connection and it should fix the problem

--
Val Mazur
Microsoft MVP


Quote:
> I've tried to play with the rs Cursors, but when i do a "SET myRS =
> MyCommand.EXECUTE" it sets my cursor to adUseServer automatically...
> If I were needing this solutions I'd not be confortable with using:
> Set myRs = myCommand.Execute
> myRs.MoveLast
> myRecordCount = myRs.AbsolutePosition
> But it solves the problem...
> I found this Exemple into MSDN Library.. might be helpful....

> (Yeap.. it's a little big, but might help you)
> ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and
> Direction Properties Example
> This example uses the ActiveConnection, CommandText, CommandTimeout,
> CommandType, Size, and Direction properties to execute a stored procedure.

> Public Sub ActiveConnectionX()

>    Dim cnn1 As ADODB.Connection
>    Dim cmdByRoyalty As ADODB.Command
>    Dim prmByRoyalty As ADODB.Parameter
>    Dim rstByRoyalty As ADODB.Recordset
>    Dim rstAuthors As ADODB.Recordset
>    Dim intRoyalty As Integer
>    Dim strAuthorID As String
>    Dim strCnn As String

>    ' Define a command object for a stored procedure.
>    Set cnn1 = New ADODB.Connection
>    strCnn = "Provider=sqloledb;" & _
>       "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
>    cnn1.Open strCnn
>    Set cmdByRoyalty = New ADODB.Command
>    Set cmdByRoyalty.ActiveConnection = cnn1
>    cmdByRoyalty.CommandText = "byroyalty"
>    cmdByRoyalty.CommandType = adCmdStoredProc
>    cmdByRoyalty.CommandTimeout = 15

>    ' Define the stored procedure's input parameter.
>    intRoyalty = Trim(InputBox( _
>       "Enter royalty:"))
>    Set prmByRoyalty = New ADODB.Parameter
>    prmByRoyalty.Type = adInteger
>    prmByRoyalty.Size = 3
>    prmByRoyalty.Direction = adParamInput
>    prmByRoyalty.Value = intRoyalty
>    cmdByRoyalty.Parameters.Append prmByRoyalty

>    ' Create a recordset by executing the command.
>    Set rstByRoyalty = cmdByRoyalty.Execute()

>    ' Open the Authors table to get author names for display.
>    Set rstAuthors = New ADODB.Recordset
>    rstAuthors.Open "authors", strCnn, , , adCmdTable

>    ' Print current data in the recordset, adding
>    ' author names from Authors table.
>    Debug.Print "Authors with " & intRoyalty & _
>       " percent royalty"
>    Do While Not rstByRoyalty.EOF
>       strAuthorID = rstByRoyalty!au_id
>       Debug.Print , rstByRoyalty!au_id & ", ";
>       rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
>       Debug.Print rstAuthors!au_fname & " " & _
>          rstAuthors!au_lname
>       rstByRoyalty.MoveNext
>    Loop

>    rstByRoyalty.Close
>    rstAuthors.Close
>    cnn1.Close

> End Sub



> > David,

> > If you open recordset based on SELECT statement, it does not matter if
it
> is
> > opened by Command or using Connection or Open method of the recordset.
> > Anyway you may be able to get number of records from RecordCount
property
> of
> > recordset. But it depends on settings of your cursor. In case, if using
> > specific cursor settings, provider cannot return actual record count,
then
> > it will return -1 as a value. To get actual record count you would need
to
> > open recordset on client side as a static one. Then RecordCount will
> return
> > you that value. See more details about that in next KB

> > http://support.microsoft.com/default.aspx?scid=kb;en-us;194973

> > --
> > Val Mazur
> > Microsoft MVP



> > > Hi Guys,
> > > I need a little help. Im using the ADODB.command Object to send a
> > > SELECT statement to a MySQL-DB via ODBC. Now I wanted to find out how
> > > many Recordsets it returns and I don't know how to do this. I tried to
> > > use the RecordsAffected Option in the command.Execute Function, but
> > > that wouldn't work out, it seems to return always 0 recordsets. Is
> > > there any other Possibility to get the number of recordsets, like
> > > maybe the Recordset.RecordCount Function?

> > > thx in advance
> > > David.



Sat, 10 Sep 2005 01:33:51 GMT  
 ADODB.command Object and RecordCount

Try the following (marked in Green):

Quote:

> > Sorry, but I guess I can't follow you quite right. Do you mean st.
> > like "Set MyCommandObject = New ADODB.Recordset"?
> No, I meant:
> Dim rs as ADODB.Recordset
> Dim cmd as ADODB.Command
> Set cmd = New ADODB.Command

> ' * Set all Properties of your Command
> Here,

Set rs=new Recordset
rs.CursorType=adOpenStatic
rs.CursorLocation=adUseClient
and then
Quote:
> Set rs = cmd.Open

> For not to keep giving you only suggestions, I'm moving to a machine w/ VB
> and performing some tests... BRB with a more consistent solution..
> Don't run away =)

> Daniel




> > >msgbox myCommand.recordset.recordcount

> > Unfortunately this doesn't work out. There doesn't seem to be a
> > recordset Object inside the Command Object.

> > >If I'm not mistaken, you have a Recordset Object inside the Command
> > >object...
> > >I think you can set a Recordset as your Command.Open....

> > Sorry, but I guess I can't follow you quite right. Do you mean st.
> > like "Set MyCommandObject = New ADODB.Recordset"?

> > >As I'm out of VB for some hours I can guarantee none of these information
> > >for you, but I think I'm pretty close of the path you gotta take....

> > Anyway, thanks for your help

> > >Daniel

> > bye
> > David.



Sat, 10 Sep 2005 05:03:29 GMT  
 ADODB.command Object and RecordCount

Will not work.. when you set your exististing RS to another one (regardless if it's an rs retrieved from a Function, from a Conn.Execute, from a Command.Execute, or anywhere else), the original settings will be overriding with the retrieved RS information...
As you can't set these informations after the rs is Filled up, the only choice is to set all the similar properties on the Connection Object (if available or applicable) or use the solution I suggested (move to last record and retrieve the Absolute Position)...

I would use the following as an alternative to my own Code previously posted:

Dim cn as Connection
set cn = new Connection
cn.CursorLocation = adUseClient
cn.Open "ConnectionString"

Dim cmd as Command
Set cmd = New Command
Set cmd.ActiveConnection = cn

' *****************************************
' * Set Command Properties Here
' *****************************************

Dim rs as Recordset
Set rs = cmd.Execute

It has no reason not to work...
I've relearned it reading an Answer Post of this Thread Itself (from Val Mazur ** Thx Mazur **)


  Try the following (marked in Green):


  > > Sorry, but I guess I can't follow you quite right. Do you mean st.
  > > like "Set MyCommandObject = New ADODB.Recordset"?
  > No, I meant:
  > Dim rs as ADODB.Recordset
  > Dim cmd as ADODB.Command
  > Set cmd = New ADODB.Command
  >
  > ' * Set all Properties of your Command
  > Here,
  Set rs=new Recordset
  rs.CursorType=adOpenStatic
  rs.CursorLocation=adUseClient
  and then

  > Set rs = cmd.Open
  >
  > For not to keep giving you only suggestions, I'm moving to a machine w/ VB
  > and performing some tests... BRB with a more consistent solution..
  > Don't run away =)
  >
  > Daniel
  >



  > >
  > > >msgbox myCommand.recordset.recordcount
  > >
  > > Unfortunately this doesn't work out. There doesn't seem to be a
  > > recordset Object inside the Command Object.
  > >
  > > >If I'm not mistaken, you have a Recordset Object inside the Command
  > > >object...
  > > >I think you can set a Recordset as your Command.Open....
  > >
  > > Sorry, but I guess I can't follow you quite right. Do you mean st.
  > > like "Set MyCommandObject = New ADODB.Recordset"?
  > >
  > > >As I'm out of VB for some hours I can guarantee none of these information
  > > >for you, but I think I'm pretty close of the path you gotta take....
  > >
  > > Anyway, thanks for your help
  > >
  > > >Daniel
  > >
  > > bye
  > > David.
  >
  >



Sat, 10 Sep 2005 05:16:45 GMT  
 ADODB.command Object and RecordCount

Will not work.. when you set your exististing RS to another one (regardless if it's an rs retrieved from a Function, from a Conn.Execute, from a Command.Execute, or anywhere else), the original settings will be overriding with the retrieved RS information...
As you can't set these informations after the rs is Filled up, the only choice is to set all the similar properties on the Connection Object (if available or applicable) or use the solution I suggested (move to last record and retrieve the Absolute Position)...

I would use the following as an alternative to my own Code previously posted:

Dim cn as Connection
set cn = new Connection
cn.CursorLocation = adUseClient
cn.Open "ConnectionString"

Dim cmd as Command
Set cmd = New Command
Set cmd.ActiveConnection = cn

' *****************************************
' * Set Command Properties Here
' *****************************************

Dim rs as Recordset
Set rs = cmd.Execute

It has no reason not to work...
I've relearned it reading an Answer Post of this Thread Itself (from Val Mazur ** Thx Mazur **)


  Try the following (marked in Green):


  > > Sorry, but I guess I can't follow you quite right. Do you mean st.
  > > like "Set MyCommandObject = New ADODB.Recordset"?
  > No, I meant:
  > Dim rs as ADODB.Recordset
  > Dim cmd as ADODB.Command
  > Set cmd = New ADODB.Command
  >
  > ' * Set all Properties of your Command
  > Here,
  Set rs=new Recordset
  rs.CursorType=adOpenStatic
  rs.CursorLocation=adUseClient
  and then

  > Set rs = cmd.Open
  >
  > For not to keep giving you only suggestions, I'm moving to a machine w/ VB
  > and performing some tests... BRB with a more consistent solution..
  > Don't run away =)
  >
  > Daniel
  >



  > >
  > > >msgbox myCommand.recordset.recordcount
  > >
  > > Unfortunately this doesn't work out. There doesn't seem to be a
  > > recordset Object inside the Command Object.
  > >
  > > >If I'm not mistaken, you have a Recordset Object inside the Command
  > > >object...
  > > >I think you can set a Recordset as your Command.Open....
  > >
  > > Sorry, but I guess I can't follow you quite right. Do you mean st.
  > > like "Set MyCommandObject = New ADODB.Recordset"?
  > >
  > > >As I'm out of VB for some hours I can guarantee none of these information
  > > >for you, but I think I'm pretty close of the path you gotta take....
  > >
  > > Anyway, thanks for your help
  > >
  > > >Daniel
  > >
  > > bye
  > > David.
  >
  >



Sat, 10 Sep 2005 05:25:14 GMT  
 ADODB.command Object and RecordCount
thank you very much you guys,
anyways, ive decided now to though use a recordset object as i need to
go on with my project. maybe ill change this rs later out with my
still existing command.
and now ive gotten to the next problem: i don't know whats up with me
these days but it seems i don't even get that rs's recordcount
property to work. here's my code: im sure i just did a little mistake,
but i always get a recordcount of 0.

Dim cn As ADODB.Connection, cm As ADODB.Command

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Provider = "MSDASQL"
cn.CursorLocation = adUseClient
cn.Open "DSN=pmed"
rs.Open "SELECT * FROM scanned WHERE zeit='" & EFC(1, txtCode.Text) &
"' AND nummer='" & EFC(5, txtCode.Text) & "'", cn, adOpenStatic,
adLockReadOnly
MsgBox rs.RecordCount
rs.Close

what am I doin wrong?

bye
David.



Sat, 10 Sep 2005 16:40:51 GMT  
 ADODB.command Object and RecordCount
damn, im not really woken up yes it seems, my rs worked, i only wrote
a too big number into a too small int, so when i compared my big int
with the int in the db it would return 0 because it were 2 different
numbers.

anyway, thanks you guys...



Sat, 10 Sep 2005 16:48:01 GMT  
 
 [ 14 post ] 

 Relevant Pages 

1. Sending command to a FoxPro database using ADODB.Command Object

2. Using adodb command object with transferspreadsheet

3. Using browser-side ADODB.Command object?

4. Problem creating ADODB.command object in asp.

5. Using browser-side ADODB.Command object?

6. ADODB command object

7. When to use ADODB.Command Object in ADO Programming

8. Using where IN with a ADODB.Command object...

9. ADODB.Command Connection Error in MTS object

10. When to use ADODB.Command Object in ADO Programming

11. Err in using ADODB Command Object in VB.Net

12. Help SQL server, ADOdb.procedures has no command object

 

 
Powered by phpBB® Forum Software