Solved

Output-parameter via VBA cmd.Parameters always returns null

Posted on 2016-09-30
5
37 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 49

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

947 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

22 Experts available now in Live!

Get 1:1 Help Now