Re: how to retrieve [RecordsAffected] count from cmd.Excecute in V

Glad to hear it works. I was panicking a bit yesterday when I tested your
code and got zeros, thinking that maybe my own code was no longer working as
it was originally built with ADO 2.1. However, checking it over revealed it
still worked (phew!), and that property setting was needed.

Dan

Rich wrote on Tue, 25 Apr 2006 11:46:02 -0700:

Success! that was it! I am so happy now! Finally, I can stop using
output vars and reduce the complexity of my SPs, etc. Thanks very much
for your help!

Rich

"Daniel Crichton" wrote:

I dug out my old code, and found the reason it doesn't work for you - you
need to set the options on the command object

cmd.Execute j, , adExecuteNoRecords

This tells ADO that no records are being returned, so it stores the count
of affected records in the RecordsAffected value.

Dan

Rich wrote on Tue, 25 Apr 2006 08:19:03 -0700:

Thanks. My problem is that my brain is just not engaging. The reason I
made the original j a variant was because Longs and Integers by default
are 0. A variant by default is Null. So I am not sure if 0 is real or
not. May I request if you could show me a sample Sp that just does a
simple insert from tbl1 to tbl2 and the related ADO code to invoke the
SP and returns the RecordsAffected count (or at least returns a count
for you)? Here is what I have tried that does not return
the "RecordsAffected" count: ------------------------------------ Create
Proc
stp_test1 As Insert Into tbl2(fld1, fl2, fld3) Selet fld1, fld2, fld3
From tbl1 Go ----------------------------------- And here is the related
ADO code to invoke the Proc and get the "Records Affected" count --
which the proc gets invoked but I am not getting a "Records Affected"
Count. Note: the proc above does not have "Set Nocount On"

Dim cmd As New ADODB.Command, j As Long
cmd.ActiveConnection = CurrentProject.Connection
cmd.ActiveConnection.CursorLocation = adUseClient '--I have also tried
adUseServe
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_test1"
cmd.Execute(j)
Debug.Print j '--prints 0 and I have 10 records inserted into tbl2

Is there anything I could rearrange in the code for the proc or the ADO
code?

"Daniel Crichton" wrote:

ADO should handle getting the value back for you into the variable. Did
you try declaring it as a Long instead of Variant?

Are you doing a SET NOCOUNT ON anywhere in the connection before
running this proc?

The only other thing I can think of is that the proc is confusing ADO
by returning a recordset. As you don't need this (you're using .Execute
without assigning the result to a Recordset object) try removing the
SELECT statement in your proc. I don't use SELECTs in my non-recordset
returning procs where I use RecordsAffected - as I never do anything
with the returned data, there's no point in returning it and
wasting resources.

Dan

Rich wrote on Mon, 24 Apr 2006 13:47:01 -0700:

The SP is real basic
------------------------------------------
Create Proc stp_test1
As
Insert Into tbl1(fld1, fld2, fld3)
Select fdl1, fld2, fld3 from tbl2

Go
-------------------------------------------

Is there some @@ object I need to use get the recordsAffected count?

"Daniel Crichton" wrote:

Rich wrote on Thu, 20 Apr 2006 13:59:01 -0700:

Dim cmd As New ADODB.Command, j As variant
...
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_Proc1"
'--Proc1 inserts rows into a table, for example
cmd.Excecute(j) '--[RecordsAffected]
Debug.Print j

j is always null. In Query Analyzer the message tab will say "29
records affected" when I execute the SP. How can I retrieve that
count from cmd.Execute in my VB app? If I use an ADODB recordset
Object I have to use

cmd.ActiveConnection.CursorLocation = adUseClient

to get a value from RS.RecordCount other than 0. I tried
cmd.ActiveConnection.CursorLocation = adUseClient and adUseServer
for the RecordsAffected count, but neither returned a value
for RecordsAffected. Any suggestions appreciated.

MSDN defines RecordsAffected as being a Long, so first thing I'd do
is change the variable definition in case that is causing a problem.

What is the code in stp_Proc1? I've used the RecordsAffected value
many times to determine if my proc does anything and so far I've
never seen it return null, always a 0 or a number.

Dan



.

Relevant Pages

  • Re: sp_reset_connection
    ... ODBC, ADO, OLE DB, et al. use. ... I don't believe it's anything to worry ... > down my issue I see that this proc is run in between each time this user ...
    (microsoft.public.sqlserver.server)
  • Re: how to retrieve [RecordsAffected] count from cmd.Excecute in V
    ... ADO should handle getting the value back for you into the variable. ... The only other thing I can think of is that the proc is confusing ADO by ... assigning the result to a Recordset object) try removing the SELECT ... cmd.ActiveConnection.CursorLocation = adUseClient ...
    (microsoft.public.data.ado)
  • Re: Performance Problem Using ADO and Stored Procs
    ... >I have a stored proc that executes in < 15 seconds through Query Analyzer. ... If I execute this proc ... > of CPU activity on the server hosting SQL server for the SQL server ... > Private Function RunProc(vntDB As Variant, strProcName As String, ...
    (microsoft.public.data.ado)
  • Re: how to retrieve [RecordsAffected] count from cmd.Excecute in V
    ... simple insert from tbl1 to tbl2 and the related ADO code to invoke the SP ... And here is the related ADO code to invoke the Proc and get the "Records ... cmd.ActiveConnection.CursorLocation = adUseClient '--I have also tried ...
    (microsoft.public.data.ado)
  • Re: how to retrieve [RecordsAffected] count from cmd.Excecute in V
    ... simple insert from tbl1 to tbl2 and the related ADO code to invoke the SP ... And here is the related ADO code to invoke the Proc and get the "Records ... cmd.ActiveConnection.CursorLocation = adUseClient '--I have also tried ...
    (microsoft.public.data.ado)