Output-parameter via VBA cmd.Parameters always returns null

• candide

  Ten Centuries

  Points: 1164

  More actions

  September 30, 2016 at 2:07 am

  #77481

  hi,

  still trouble with StoredProcs and VBA.

  First my StoredProc:

  /****** Object: StoredProcedure [dbo].[SP_CLEAR] Script Date: 30.09.2016 00:52:59 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  --

  --

  -- =============================================

  CREATE PROCEDURE [dbo].[SP_CLEAR]

  -- Add the parameters for the stored procedure here

  @ParPRINTER_NAMEAS nvarchar(100)= NULL

  , @ParAppEnvAS integer = 0

  , @ParOUT_NumberRowsDELETEDAS integer OUTPUT

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  --SET NOCOUNT ON;

  -- !!!!!!!!!!!!! !!!!!!!!!!!!! !!!!!!!!!!!!! !!!!!!!!!!!!!

  -- IMPORTANT for CRUD-operations to return the number of affected rows

  SET NOCOUNT OFF;

  -- !!!!!!!!!!!!! !!!!!!!!!!!!! !!!!!!!!!!!!! !!!!!!!!!!!!!

  -- 1. DECLARATIONS

  DECLARE @RowsDeletedAS int= 0

  -- ...

  -- 2. do some CURSOR handling

  OPEN DS_cursor;

  -- ...

  -- ...

  SET @RowsDeleted = @RowsDeleted + 1

  -- ...

  -- FINIS

  CLOSE DS_cursor;

  DEALLOCATE DS_cursor;

  SET @ParOUT_NumberRowsDELETED = @RowsDeleted

  SELECT @ParOUT_NumberRowsDELETED AS MyParOut

  END

  and this in VBA to read the output-parameter. The code is from Access Developer's Handbook Vol.2

  Public Function fctCallSP_CLEAR(ByVal INstrPRTNameLang As String) As Integer

  Dim cnn As ADODB.Connection

  Dim cmd As ADODB.Command

  Dim prm As ADODB.Parameter

  Dim lngReturnRowsAffected As Long

  On Error GoTo fctCallSP_CLEAR_Error

  Set cnn = New ADODB.Connection

  Set cmd = New ADODB.Command

  Call sSQLServerVerbinden(cnn, 3, gstrDatenbank)

  Set cmd.ActiveConnection = cnn

  cmd.CommandText = "SP_CLEAR"

  cmd.CommandType = adCmdStoredProc

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

  cmd.Parameters.Append prm

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

  cmd.Parameters.Append prm

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

  cmd.Parameters.Append prm

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

  fctCallSP_CLEAR = Nz(cmd.Parameters("NumberRowsDELETED"), 0)

  ExitHere:

  On Error Resume Next

  '-- FINIS

  Set prm = Nothing

  Set cmd = Nothing

  If cnn.State = adStateOpen Then

  cnn.Close

  Set cnn = Nothing

  End If

  Exit Function

  fctCallSP_CLEAR_Error:

  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fctCallSP_CLEAR_Error of Klassenmodul clsPSIpenta_Tables_CRUD"

  End Function

  the output parameter always returns null and I don't know why.:w00t: When I create a more simple StoredProc I can read the output parameter correctly.

  Any ideas?

  --
  candide
  ________Panta rhei

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  September 30, 2016 at 2:15 am

  #1904097

  Well, I would start by having your parameter names match the ones in the SP.

  Maybe it is just a mistake in how you have posted this, but the procedure names don't match either...we can't help if the code is just wrong 😉

  Then I would set nocount on - despite what the comments say, you are not going to get anything good from having that set off.

  See where that gets you.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]