2 Replies Latest reply: Jan 21, 2010 8:05 PM by 746752 RSS

    DataAdapter Updates Fail... but returns a RowsAffected count of 1

    746752
      I have an Update form for a small Table. The update Keys
      consists of the primary key (intAutoCountyNr) and a DateTimeStamp.
      I'm using a Stored Procedure to perform the Updates (Oracle & SQL Server
      see below)

      To test Conncurrency updating, I load the record in a Form, then
      using TOAD/SQLServer Studio or similar editor I physically change the
      DateTimeStamp in the database to something other than the original

      After I physically change DateTimeStamp, I populate the SP Parms,
      then issue the following Update command:

      Dim intRowsAffected As Integer = mobjAdapter.Update(mobjDataTable)

      Both the SQLServer and Oracle procedures were always returning a 1 for
      intRowsAffected, even though the updates were failing.... I did some research
      and found the fix for SQLServer... SET NOCOUNT OFF

      Is there a comparable setting or work around in Oracle PL/SQL?




      /*---------------------------------------------------------------------
      SQL Server Procedure
      */---------------------------------------------------------------------
      ALTER PROCEDURE [dbo].[usp_tblCountyAutoNr_Update]
      (
      @intAutoCountyNr Int,
      @strCountyName NVarChar(51),
      @intRegion Int,
      @strUpdatedBy NVarChar(20),
      @dtmDateTimeStamp SmallDateTime,
      @intAutoCountyNr_Orig Int,
      @dtmDateTimeStamp_Orig SmallDateTime
      )
      AS

      SET NOCOUNT OFF -- Fixes Problem in SQLServer

      BEGIN

      UPDATE tblCountyAutoNr
      SET
      strCountyName=@strCountyName,
      intRegion=@intRegion,
      strUpdatedBy=@strUpdatedBy,
      dtmDateTimeStamp=@dtmDateTimeStamp

      WHERE (intAutoCountyNr = @intAutoCountyNr_Orig)
      AND (dtmDateTimeStamp = @dtmDateTimeStamp_Orig)
      END




      /*---------------------------------------------------------------------
      Oracle Procedure
      */---------------------------------------------------------------------
      PROCEDURE PROC_TBLCOUNTYAUTONR_UPDATE
      (
      p_intautocountynr tblcountyautonr.intautocountynr%TYPE,
      p_strcountyname tblcountyautonr.strcountyname%TYPE,
      p_intregion tblcountyautonr.intregion%TYPE,
      p_strupdatedby tblcountyautonr.strupdatedby%TYPE,
      p_dtmdatetimestamp tblcountyautonr.dtmdatetimestamp%TYPE,
      p_intautocountynr_Orig tblcountyautonr.intautocountynr%TYPE,
      p_dtmdatetimestamp_Orig tblcountyautonr.dtmdatetimestamp%TYPE
      )
      IS

      BEGIN

      UPDATE TBLCOUNTYAUTONR
      SET
      STRCOUNTYNAME=p_strcountyname,
      INTREGION=p_intregion,
      STRUPDATEDBY=p_strupdatedby,
      DTMDATETIMESTAMP=p_dtmdatetimestamp

      WHERE (intautocountynr = p_intautocountynr_orig)
      AND (dtmdatetimestamp = p_dtmdatetimestamp_orig) ;


      END PROC_TBLCOUNTYAUTONR_UPDATE;