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