@@ROWCOUNT in StoredProc returns 1, in VBA returns -1

• candide

  Ten Centuries

  Points: 1164

  More actions

  September 5, 2016 at 3:08 pm

  #77436

  hello,

  this strange behaviour I can't explain:

  CREATE PROCEDURE [dbo].[sp1_UPDATE]

  @ParCRUD_TypAS int= 0

  , @ParPRINTER_NAME_OLDAS nvarchar(100)= NULL

  , @ParPRINTER_NAMEAS nvarchar(100) = NULL

  , @ParSYSDRUCKERAS nvarchar(50)= NULL

  , @ParDSServerAS nvarchar(50)= NULL

  , @ParDSDatenbankAS nvarchar(50)= NULL

  , @ParDSTabelleAS nvarchar(50)= NULL

  , @ParAppEnvAS int = 0

  DECLARE @NumberDeletedRowsAS int= 0

  DECLARE @CountRowsAS int= 0

  -- part 1 DELETE

  EXECUTE @NumberDeletedRows = dbo.sp3_CLEAR @ParPRINTER_NAME_OLD, @ParAppEnv

  --SELECT @NumberDeletedRows ...

  .

  .

  .

  -- part 2 INSERT

  EXECUTE dbo.sp2_INSERT 0

  , @ParPRINTER_NAME

  , NULL

  , @ParDSServer

  , @ParDSDatenbank

  , @ParDSTabelle

  SET @CountRows = @@ROWCOUNT

  Return @CountRows

  I executed the StoredProc in SSMS, the Return Value: 1 is OK! The sp2_Insert is done correctly.

  And the @NumberDeletedRows has the correct value. But this style doesn't work in part2 for the INSERT.

  Now to the Access-VBA:

  Set cnn = New ADODB.Connection

  Set cmd = New ADODB.Command

  Call sSQLServerVerbinden(cnn, 3, gstrDatenbank)

  Set cmd.ActiveConnection = cnn

  cmd.CommandText = "sp1_UPDATE"

  cmd.CommandType = adCmdStoredProc

  Set prm = cmd.CreateParameter("CRUD_Typ", adInteger, adParamInput, , mcintCRUD_Type)

  cmd.Parameters.Append prm

  Set prm = cmd.CreateParameter("PRINTER_NAME_OLD", adVarWChar, adParamInput, 100, INstrPRTNameOLD)

  cmd.Parameters.Append prm

  Set prm = cmd.CreateParameter("PRINTER_NAME", adVarWChar, adParamInput, 100, INstrPRTNameLang)

  cmd.Parameters.Append prm

  Set prm = cmd.CreateParameter("SYSDRUCKER", adVarWChar, adParamInput, 50, INstrSYSDRUCKER)

  cmd.Parameters.Append prm

  Set prm = cmd.CreateParameter("DSServer", adVarWChar, adParamInput, 50, instrDSServer)

  cmd.Parameters.Append prm

  Set prm = cmd.CreateParameter("DSDatenbank", adVarWChar, adParamInput, 50, instrDSDatenbank)

  cmd.Parameters.Append prm

  Set prm = cmd.CreateParameter("DSTabelle", adVarWChar, adParamInput, 50, instrDSTabelle)

  cmd.Parameters.Append prm

  Set prm = cmd.CreateParameter("APPENV", adInteger, adParamInput, , glngAPP_ENVIRONMENT)

  cmd.Parameters.Append prm

  cmd.Execute RecordsAffected:=lngReturnRowsAffected, Options:=adExecuteNoRecords

  When I call this function in VBA it never returns 1 like in SSMS. It returns for lngReturnRowsAffected the value -1,

  nevertheless the INSERT is done correctly. I checked the parameters more than once they are defined correctly.

  Also tried

  Return @@ROWCOUNT

  instead of

  SET @CountRows = @@ROWCOUNT

  Return @CountRows

  I'm stuck. Whats the way to return the @@ROWCOUNT from the EXEC command in VBA?

  --
  candide
  ________Panta rhei

• candide

  Ten Centuries

  Points: 1164

  More actions

  September 6, 2016 at 4:17 am

  #1899282

  for me it's essential to know this behaviour:

  ' Access Developer Handbook Vol. 2 S. 265:

  ' 1.

  ' If you use the SET NOCOUNT ON statement within a SQL Server StoredProc,

  ' RecordsAffected will be set to -1 if the procedure succeeded and 0 if it did not

  '

  ' 2.

  ' Under certain conditions, RecordsAffected can report a spurious number. This might happen, for example,

  ' if the query or stored procedure causes a trigger to be executed or a stored procedure includes multiple SQL statements!!!

  The solution is to use OUTPUT parameter(s):

  in the SP:

  , @ParOUT_RowsINSERTEDAS int OUTPUT

  .

  .

  EXECUTE @ParOUT_RowsINSERTED = dbo.sp2_INSERT 0

  , @ParPRINTER_NAME

  , NULL

  , @ParDSServer

  , @ParDSDatenbank

  , @ParDSTabelle

  in VBA add these lines:

  Set prm = cmd.CreateParameter("NumberRowsINSERTED", adInteger, adParamOutput)

  cmd.Parameters.Append prm

  cmd.Execute RecordsAffected:=lngReturnRowsAffected, Options:=adExecuteNoRecords

  ' Attention:

  ' as the StoredProc uses multiple SQL statements, the value of lngReturnRowsAffected is -1 (SP succeeded)

  ' => so use the OUTPUT parameter!

  'fctINS = lngReturnRowsAffected

  fctINS = cmd.Parameters("NumberRowsINSERTED")

  I'm still learning...:cool:

  --
  candide
  ________Panta rhei