Solved

Output-parameter via VBA cmd.Parameters always returns null

Posted on 2016-09-30
5
66 Views
Last Modified: 2016-10-18
hi,
still trouble with StoredProcs and VBA, using SQLServer 2012 and Access2010

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_NAME					AS nvarchar(100)	= NULL
	, @ParAppEnv						AS integer 				= 0
	, @ParOUT_NumberRowsDELETED			AS 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 @RowsDeleted				AS 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

Open in new window


and this in VBA to read the output-parameter. The code is adapted from Access 2000 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

Open in new window


and yes, I tried to name the parameter the same as in the StoredProc (tried with and without @).
The output parameter always returns null and I don't know why. [w00t]

When I create a similar but very simple StoredProc I can read the output parameter correctly.
What drives me crazy if I debug the StoredProc in SSMS all is fine
Any ideas?
0
Comment
Question by:candide
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41824502
Hi,

maybe
Set prm = cmd.CreateParameter("ParOUT_NumberRowsDELETED", adInteger, adParamOutput)
cmd.Parameters.Append prm	
	
cmd.Execute RecordsAffected:=lngReturnRowsAffected, Options:=adExecuteNoRecords
	
fctCallSP_CLEAR = Nz(cmd.Parameters("ParOUT_NumberRowsDELETED"), 0)

Open in new window


or

[code]Set prm = cmd.CreateParameter("RowsDELETED", adInteger, adParamOutput)
cmd.Parameters.Append prm	
	
cmd.Execute RecordsAffected:=lngReturnRowsAffected, Options:=adExecuteNoRecords
	
fctCallSP_CLEAR = Nz(cmd.Parameters("RowsDELETED"), 0)

Open in new window

[/code]Regards
0
 

Author Comment

by:candide
ID: 41824551
hi rgonzo1971,
as I wrote I've also tried to rename the parameters in the CreateParameter method as you wrote, no success.
Here a screenshot of the vba-debug window:
vba debug window after cmd.ExecuteAs you can see here the first two parameters (input) can be read, the third parameter (item3; output) always returns empty. btw lngReturnRowsAffected has the value -1.
0
 

Author Comment

by:candide
ID: 41824671
hi folks,
what I now found out is that the above StoredProc SP_CLEAR is calling another StoredProc2. The Try-Catch block is not listed in my SP-code example above for simplicity.

When testing StoredProc2 it returns an
ERROR_NUMBER(): 7314    :-(
which means:


"The OLE DB provider "%ls" for linked server "%ls" does not contain the table "%ls". The table either does not exist or the current user does not have permissions on that table."

Someone has changed my permissions on tables - ouch! I first have to get these permissions which were existing some weeks ago...
Please wait until thursday when I get a call with the db-admin
thx
0
 

Accepted Solution

by:
candide earned 0 total points
ID: 41833042
hi folks,
solution found: permissions / db access was lost because of a linked server problem which the db admin fixed. Now all is fine :-)
0
 

Author Closing Comment

by:candide
ID: 41847978
the issue was not the code it was a permission problem on the SQL Server linked server which could not be resolved by any code
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question