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

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
    ... the original j a variant was because Longs and Integers by default are 0. ... And here is the related ADO code to invoke the Proc and get the "Records ... assigning the result to a Recordset object) try removing the SELECT ...
    (microsoft.public.data.ado)
  • How to get past an error?
    ... sql error but still need to return a recordset or print statement that ... statemets and depending on the type of error, might see the recordset.. ... in batchs.. ... PS try to make this proc send back the error,print and get the record ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Determining column names and types of recordset returned by stored procedure
    ... then wraps the call to the proc with SET NO_BROWSETABLE and SET FMTONLY ... the code above actually returns one empty recordset for each ... Visual Studio would only show TextResults as a resulting ... >> Studio 2003 Server explorer is reliably providing the information ...
    (microsoft.public.sqlserver.programming)
  • Re: Effects on RST in Calling Proc
    ... the original procedure in a way that is likely to survive the ... In the original proc, the recordset is snapshot and is passed to the called proc so that another field can be searched for a value that is being tested in the calling proc. ... I try as much as possible to avoid data manipulation by row processing and prefer set processing, ie, I prefer to execute an update or insert statement as opposed to going through each row in a recordset and doing ...
    (comp.databases.ms-access)
  • Re: Effects on RST in Calling Proc
    ... >> Obviously, the snapshot avoids that, but I think it's better to ... the recordset is snapshot and is passed to ... > the called proc so that another field can be searched for a value ... you avoid the issue entirely. ...
    (comp.databases.ms-access)