[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Why is RecordsAffected=-1

Posted on 2004-11-18
6
Medium Priority
?
640 Views
Last Modified: 2008-02-01
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?

0
Comment
Question by:rmk
5 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 12615350
this works for .recordcount probably will work for .recordsaffected


 With cmd
            .CursorLocation = adUseClient
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12615412
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
 
LVL 2

Expert Comment

by:marlind
ID: 12615873
In Microsoft Access true is = -1.
0
 

Author Comment

by:rmk
ID: 12616012
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14194540
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question