Solved

Output-parameter via VBA cmd.Parameters always returns null

Posted on 2016-09-30
5
32 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
  • 4
5 Comments
 
LVL 48

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now