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

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

  • 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
    ... >> 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)
  • 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: Problem with the output of Split
    ... to the recordset and made them both advarcharjust to test this. ... The Array and Split statements here seem equivalent except when I try ... Dim varFieldNames As Variant ...
    (microsoft.public.vb.general.discussion)