Why is RecordsAffected=-1

I must be blind because I can't figure out why RecordsAffected=-1 in this trivial example.

Here's my SQL Server 2000 table definition:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAmenitySpec]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAmenitySpec]
GO

CREATE TABLE [dbo].[tblAmenitySpec] (
      [AmenityID] [int] NOT NULL ,
      [AmenityTypeSpecID] [int] NOT NULL ,
      [Flg] [bit] NOT NULL ,
      [Qty] [decimal](18, 4) NULL ,
      [AmenityAttrValueID] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblAmenitySpec] WITH NOCHECK ADD
      CONSTRAINT [PK_tblAmenitySpec] PRIMARY KEY  CLUSTERED
      (
            [AmenityID],
            [AmenityTypeSpecID]
      )  ON [PRIMARY]
GO

Here's the update stored procedure:

CREATE PROCEDURE dbo.proctblAmenitySpec_UpdateForAmenityIDAmenityTypeSpecID
      (
      @AmenityID INT,
      @AmenityTypeSpecID       INT,
      @Flg  BIT ,
      @Qty  DECIMAL(18,4) ,
      @AmenityAttrValueID      INT
      )
AS
-------------------------------------------------
-- local variables
-------------------------------------------------
DECLARE
      @Proc            VARCHAR(50),
      @RowCount      INT,
      @ErrNbr            INT,
      @ErrMsg            VARCHAR(255)
-------------------------------------------------
-- initialize
-------------------------------------------------
SET @Proc = 'dbo.proctblAmenitySpec_UpdateForAmenityIDAmenityTypeSpecID'
SET NOCOUNT ON
-------------------------------------------------
-- update
-------------------------------------------------
UPDATE       tblAmenitySpec
SET      Flg = @Flg,
      Qty = @Qty,
      AmenityAttrValueID = @AmenityAttrValueID
WHERE      AmenityID = @AmenityID AND
      AmenityTypeSpecID = @AmenityTypeSpecID
-- handle errors
SELECT      @ErrNbr = @@ERROR, @RowCount = @@ROWCOUNT
IF @ErrNbr <> 0  BEGIN
      SET @ErrMsg = 'failed to update'
      GOTO Proc_Err
END
-------------------------------------------------
-- all is good
-------------------------------------------------
RETURN 0
-------------------------------------------------
-- error handler
-------------------------------------------------
Proc_Err:
RAISERROR (@ErrMsg,16,1)
RETURN @ErrNbr
GO

Here' the script to load data into the table:

INSERT tblAmenitySpec VALUES (1,1,1,1,null)

Here's my snippet of Access XP VBA code which demonstrates the problem:

Public Sub RecAff _
    ( _
        ByVal vstrDataSource As String, _
        ByVal vstrInitialCatalog As String, _
        ByVal vstrUserID As String, _
        ByVal vstrPassword As String, _
        ByVal vfUseProc As Boolean _
    )
   
    Dim lngAmenityID As Long
    Dim lngAmenityTypeSpecID As Long
    Dim strConnection As String
    Dim fFlg As Boolean
    Dim dblQty As Double
    Dim lngRecAff As Long
    Dim varValueID As Variant
   
    Dim cmd As ADODB.Command
   
    strConnection = "Provider=sqloledb;" & _
                             "Data Source=" & vstrDataSource & ";" & _
                             "Initial Catalog=" & vstrInitialCatalog & ";" & _
                             "User Id=" & vstrUserID & ";" & _
                             "Password=" & vstrPassword & ";"
    lngAmenityID = 1
    lngAmenityTypeSpecID = 1
    fFlg = 1
    dblQty = 999
    varValueID = Null
    Set cmd = New ADODB.Command
    With cmd
        If vfUseProc Then
            .CommandText = "proctblAmenitySpec_UpdateForAmenityIDAmenityTypeSpecID"
            .CommandType = adCmdStoredProc
            .Parameters.Append .CreateParameter(, adInteger, adParamReturnValue)
            .Parameters.Append .CreateParameter("@AmenityID", adInteger, adParamInput, , lngAmenityID)
            .Parameters.Append .CreateParameter("@AmenityTypeSpecID", adInteger, adParamInput, , lngAmenityTypeSpecID)
            .Parameters.Append .CreateParameter("@Flg", adBoolean, adParamInput, , fFlg)
            .Parameters.Append .CreateParameter("@Qty", adDouble, adParamInput, , dblQty)
            .Parameters.Append .CreateParameter("@AmenityAttrValueID", adInteger, adParamInput, , varValueID)
        Else
            .CommandText = "UPDATE tblAmenitySpec" _
                         & " SET Flg=" & Abs(fFlg) _
                         & ",Qty=" & dblQty _
                         & ",AmenityAttrValueID=" & IIf(IsNull(varValueID), "NULL", varValueID) _
                         & " WHERE AmenityID=" & lngAmenityID _
                         & " AND AmenityTypeSpecID=" & lngAmenityTypeSpecID
            .CommandType = adCmdText
        End If
        .ActiveConnection = strConnection
        .Execute lngRecAff
    End With
    Debug.Print lngRecAff
End Sub

The dynamic SQL update works and the recordsaffected value from the adodb command is 1 when I call the above vba code with:

call recaff ("(local)","databasename","userid","password",False)

The stored procedure update works but the recordsaffected value from the adodb command is -1 when I call the above vba code with:

call recaff ("(local)","databasename","userid","password",True)

Why do I get -1?

rmkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SidFishesCommented:
this works for .recordcount probably will work for .recordsaffected


 With cmd
            .CursorLocation = adUseClient
0
SidFishesCommented:
as for why:

"default cursor type returned from a connection.execute call is a forward-only, read-only recordset which does not support the .RecordCount (and .recordsaffected??) property." - TimCottee
0
marlindCommented:
In Microsoft Access true is = -1.
0
rmkAuthor Commented:
This command executes an update stored procedure; therefore .cursorlocation is not applicable and a recordset is not applicable. Whether I pass true, -1, or 1 to the stored procedure is immaterial because mdac will do the conversion per the adBoolean argument.

The real answer is that copy and paste while working 20 hour days makes your eyes bac. Simply removing the SET NOCOUNT ON from the stored procedure solves the problem.
0
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.