September 5, 2016 at 3:08 pm
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
September 6, 2016 at 4:17 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply