Solved

Output-parameter via VBA cmd.Parameters always returns null

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

777 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