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

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: 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: invoking
    ... Make that "invoking a proc by space key, ... However, it won't invoke either button if focus is on X and Y, but it will invoke either button if focus is on Z". ... set class ...
    (comp.lang.tcl)
  • Re: invoked "break" outside of a loop
    ... Hence, when you invoke, it invokes a command that returns that special code, which is caught inside your proc, which leads to the error. ... I.e., the proc is converting the return from into a different type of return, since is invoked several levels down the return stack from the loop. ... puts "got here AA" ...
    (comp.lang.tcl)
  • Re: how to retrieve [RecordsAffected] count from cmd.Excecute in V
    ... This tells ADO that no records are being returned, ... A variant by default is Null. ... the proc above does not have "Set Nocount On" ... cmd.ActiveConnection.CursorLocation = adUseClient '--I have also tried ...
    (microsoft.public.data.ado)
  • Re: WLM for toddlers
    ... If the application environment is stopped, the PROC is never invoked. ... It will invoke more PROCs, if applicable, if the AE is started. ... This is independent of DB2, except DB2 will use the associated PROC for DDF work. ...
    (bit.listserv.ibm-main)