Problem using the ADO Command object 
Author Message
 Problem using the ADO Command object

Delving now into ADO I am looking at using the Command object to execute SQL
queries.  I have trouble getting them to work.

The following code, using the Open method of a recordset, works fine,
returning the
correct number of records (in objRs.RecordCount) from the database table in
the recordset:

       Dim objConn As ADODB.Connection
       Dim objRs As ADODB.Recordset
       Dim sqlStr As String

       Set objConn = New ADODB.Connection
       Set objRs = New ADODB.Recordset

       objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
       objConn.Open "C:\Inetpub\Databases\Timecard\Tdata2.mdb"

       sqlStr = "SELECT * FROM tdata2 WHERE BADGENUM = 'R21010'"

' this open works fine, returns RecordCount with correct number ...

       objRs.Open sqlStr, objConn, adOpenStatic, adLockOptimistic

       objRs.Close
       objConn.Close
       Set objRs = Nothing
       Set objConn = Nothing

However, the following code, doing the same except using the Command Object,
returns
a record count of -1 (obviously an error indication):

       Dim objConn As ADODB.Connection
       Dim objCmd As ADODB.Command
       Dim sqlStr As String

       Set objConn = New ADODB.Connection
       Set objCmd = New ADODB.Command

       objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
       objConn.Open "C:\Inetpub\Databases\Timecard\Tdata2.mdb"

       sqlStr = "SELECT * FROM tdata2 WHERE BADGENUM = 'R21010'"

       objCmd.CommandType = adCmdText
       objCmd.CommandText = sqlStr
       objCmd.ActiveConnection = objConn

' this call returns objRs with RecordCount set to -1 ...

       Set objRs = objCmd.Execute

       objRs.Close
       objConn.Close
       Set objRs = Nothing
       Set objCmd = Nothing
       Set objConn = Nothing

Can anyone tell me why this seemling simple use of the ADO Command object
does
not work as expected?  Are there some other attributes of the Command object
I need
to set up first, or what?

TIA - Regards,
Carl



Sat, 26 Jun 2004 07:23:57 GMT  
 Problem using the ADO Command object
On Mon, 7 Jan 2002 16:23:57 -0700, "Carl Husic"

Quote:

>' this call returns objRs with RecordCount set to -1 ...

>       Set objRs = objCmd.Execute

Take a look at the "Execute Method" topic in online help. You'll see
that it returns a forward-only, read-only cursor (the default). This
type of cursor does not support the record-count property.

You should ALWAYS use the recordset.open method to open a recordset.
This gives you control over the cursor type that is returned. Reserve
Execute for action queries (queries that don't return records, such as
Insert, Delete and Update queries).

Just pass the Command object as the source argument of the Open
statement, like this:
objRs.Open  objCmd, ,adOpenStatic, adLockOptimistic

Incidently, you should tell ADO what type of command is being sent in
your Open statement. You set the CommandType of the Command object, so
you are covered there. However in your original Open statement, you
should have specified the same command type, like this:

objRs.Open sqlStr, objConn, adOpenStatic, adLockOptimistic,adCmdText

HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.



Sat, 26 Jun 2004 09:44:32 GMT  
 Problem using the ADO Command object
Hi Carl,

The problem is them if you are using Execute method of
Connection or Command object to open recordset, then, by
default, it will be opened as read-only, forwar-only
cursor on server side even if you specify other settings
for youe recordset. That type of cursor does not return
actual record number. To get actual record number you need
to open keyset or static cursor. Usually all client
cursors return actual record number. To open cursor on
client side you can use Open method of recordset to use
recordset's settings or to use Execute method of
connection, but in that case you will need to specify
CursorLocation of Connection object, not recordset's

Val

Quote:
>-----Original Message-----
>Delving now into ADO I am looking at using the Command

object to execute SQL
Quote:
>queries.  I have trouble getting them to work.

>The following code, using the Open method of a recordset,
works fine,
>returning the
>correct number of records (in objRs.RecordCount) from the
database table in
>the recordset:

>       Dim objConn As ADODB.Connection
>       Dim objRs As ADODB.Recordset
>       Dim sqlStr As String

>       Set objConn = New ADODB.Connection
>       Set objRs = New ADODB.Recordset

>       objConn.Provider = "Microsoft.Jet.OLEDB.4.0"

objConn.Open "C:\Inetpub\Databases\Timecard\Tdata2.mdb"

- Show quoted text -

Quote:

>       sqlStr = "SELECT * FROM tdata2 WHERE BADGENUM
= 'R21010'"

>' this open works fine, returns RecordCount with correct
number ...

>       objRs.Open sqlStr, objConn, adOpenStatic,
adLockOptimistic

>       objRs.Close
>       objConn.Close
>       Set objRs = Nothing
>       Set objConn = Nothing

>However, the following code, doing the same except using
the Command Object,
>returns
>a record count of -1 (obviously an error indication):

>       Dim objConn As ADODB.Connection
>       Dim objCmd As ADODB.Command
>       Dim sqlStr As String

>       Set objConn = New ADODB.Connection
>       Set objCmd = New ADODB.Command

>       objConn.Provider = "Microsoft.Jet.OLEDB.4.0"

objConn.Open "C:\Inetpub\Databases\Timecard\Tdata2.mdb"

- Show quoted text -

Quote:

>       sqlStr = "SELECT * FROM tdata2 WHERE BADGENUM
= 'R21010'"

>       objCmd.CommandType = adCmdText
>       objCmd.CommandText = sqlStr
>       objCmd.ActiveConnection = objConn

>' this call returns objRs with RecordCount set to -1 ...

>       Set objRs = objCmd.Execute

>       objRs.Close
>       objConn.Close
>       Set objRs = Nothing
>       Set objCmd = Nothing
>       Set objConn = Nothing

>Can anyone tell me why this seemling simple use of the
ADO Command object
>does
>not work as expected?  Are there some other attributes of
the Command object
>I need
>to set up first, or what?

>TIA - Regards,
>Carl

>.



Sat, 26 Jun 2004 20:36:31 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Using the ADO Command Object to send an Execute command

2. ADO Command Object using Multiple Connection Objects

3. Error Using ADO Command object

4. ADO: Using Command Object

5. Using ADO Command object and Access PROCEDURE to insert Memo/Binary fields

6. ADO, Oracle, MTS, using command object to open Recordset

7. Error using the Command-object of ADO

8. connection problem with ADO command object ......

9. Command Object Problem in ADO

10. Command Object problem (ADO)

11. Problem passing ADO command objects as parameters in MTS

12. Problem passing ADO command objects as parameters in MTS

 

 
Powered by phpBB® Forum Software