Sql server stored procedure output parameter to ms access vba

[munkee]

Hi all,

I use the following function to execute a stored procedure which inserts records in to my sql database. I am trying to return the primary key value so that I can display it on my page however I keep getting the error that the "item is not found in the current collection".

My function is:

Function Execute_Stored_Procedure(Proc_Name As String, Proc_Values As String, Optional ByVal Output_Return As Boolean)

   On Error GoTo Execute_Stored_Procedure_Error

 Dim MyDb As DAO.Database, MyQ As QueryDef
   Dim sql_send As String
   
   Set MyDb = CurrentDb()

       Set MyQ = MyDb.QueryDefs("qTemp_Stored_Proc")



    Dim stServer, stDatabase, stUsername, stPassword As String
    Dim stConnect As String
    
    stServer = "xxx.xxx.xxxx.net"
    stDatabase = "xxx"
    stUsername = "xxx"
    stPassword = "4B:mV6hpz_\2q=</Y%?b"
    stConnect = "ODBC;DRIVER={SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword


    sql_send = "exec " & Proc_Name & " " & Proc_Values

   ' Set the SQL property and concatenate the variables.
   MyQ.Connect = stConnect
   
   If Output_Return = False Then
   MsgBox "no supply"
   Else
   MsgBox "supply"
   sql_send = "declare @NCC_OUTPUT int " & sql_send
      End If
   Debug.Print sql_send
   
   MyQ.SQL = sql_send
   MyQ.ReturnsRecords = False
   
   MyQ.Execute
   
   Dim X As Integer
   X = MyQ.Parameters("@NCC_OUTPUT")


  ' Debug.Print MyQ.SQL
   
  Set MyQ = Nothing

Done:
   Exit Function

Sorry that the code is a bit rough but I have been testing it over and over.

The stored procedure is as follows and this runs fine:

ALTER PROCEDURE [dbo].[sp_Submit_NCC] 
	-- Add the parameters for the stored procedure here
	@DteOccur datetime, 
	@Pdetected nvarchar(50),
	@DeptRaisedBy int,
	@DeptResp int,
	@NCDescrip nvarchar(255),
	@NCCause nvarchar(255),
	@NCImmediateAct nvarchar(255),
	@NCLocation nvarchar(100),
	@PNumOrRef nvarchar(30),
	@EventCat int,
	@ReportedEmailAddy nvarchar(100),
	@NCC_Output_ID INT OUTPUT
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @NCC_ID int

    -- Insert statements for procedure here
	INSERT INTO tbllog (DteOccur, Pdetected, DeptRaisedBy, DeptResp, NCDescrip, NCCause, NCImmediateAct, NCLocation, PNumOrRef, EventCat, ReportedEmailAddy)
	VALUES (@DteOccur, @Pdetected, @DeptRaisedBy, @DeptResp, @NCDescrip, @NCCause, @NCImmediateAct, @NCLocation, @PNumOrRef, @EventCat, @ReportedEmailAddy)

SELECT @NCC_ID=SCOPE_IDENTITY()

	INSERT INTO tblStatusTiming (NCIDLINK, StatusType)
	VALUES (@NCC_ID, 1)
	
Set @NCC_Output_ID = SCOPE_IDENTITY()
	
END

The execution line for the function is:

?Execute_Stored_Procedure("sp_Submit_NCC","@DteOccur='12/11/2011', @PDetected='Chris', @DeptRaisedBy=2, @DeptResp=2, @NCDescrip='test', @NCCause='test', @NCImmediateAct='test', @NCLocation='test', @PNumOrRef='test', @EventCat=2, @ReportedEmailAddy='', @NCC_Output_ID=@NCC_Output OUTPUT",True)

Overall the pass through query being sent is:

declare @NCC_OUTPUT int exec sp_Submit_NCC @DteOccur='12/11/2011', @PDetected='Chris', @DeptRaisedBy=2, @DeptResp=2, @NCDescrip='test', @NCCause='test', @NCImmediateAct='test', @NCLocation='test', @PNumOrRef='test', @EventCat=2, @ReportedEmailAddy='', @NCC_Output_ID=@NCC_Output OUTPUT

The error is on the line:

  X = MyQ.Parameters("@NCC_OUTPUT")

In my function.. I have also tried adding .value on to the end of the .parameters with no luck.

Thank you for any help.

Chris

[ADezii]

This isn't exactly my area of expertise, but I was under the impression that Pass Through Queries provide no mechanism for dealing with Output Parameters or the Return Value of a Stored Procedure.

[jimatqsi]

Adezii may be right, I'm not sure, I recall being stumped when I tried to run a stored procedure from VBA. But I suppose a clever programmer would just change the stored procedure to drop the result in a table instead of returning it.

What line of code does the error occur on? What happens if you edit query qTemp_Stored_Pr oc? Does it have a spelling problem?

Jim

[NeoPa]

ADezii:
I was under the impression that Pass Through Queries provide no mechanism for dealing with Output Parameters or the Return Value of a Stored Procedure.

Pass-Through queries have properties Returns Records and Log Messages. The former indicates there is a cursor to display and the latter creates a log table on the fly for messages displayed (which can include returned values I believe).

[munkee]

I have moved on a small amount with this I have altered my stored procedure and when running this within Sql server query analyser I am returning the desired value.

The trouble now is getting this in to ms access via my function which I cant seem to get my head around.

The new stored procedure:

	-- Add the parameters for the stored procedure here
	@DteOccur datetime, 
	@Pdetected nvarchar(50),
	@DeptRaisedBy int,
	@DeptResp int,
	@NCDescrip nvarchar(255),
	@NCCause nvarchar(255),
	@NCImmediateAct nvarchar(255),
	@NCLocation nvarchar(100),
	@PNumOrRef nvarchar(30),
	@EventCat int,
	@ReportedEmailAddy nvarchar(100)
	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @NCC_ID int

    -- Insert statements for procedure here
	INSERT INTO tbllog (DteOccur, Pdetected, DeptRaisedBy, DeptResp, NCDescrip, NCCause, NCImmediateAct, NCLocation, PNumOrRef, EventCat, ReportedEmailAddy)
	VALUES (@DteOccur, @Pdetected, @DeptRaisedBy, @DeptResp, @NCDescrip, @NCCause, @NCImmediateAct, @NCLocation, @PNumOrRef, @EventCat, @ReportedEmailAddy)

SET @NCC_ID=SCOPE_IDENTITY()

	INSERT INTO tblStatusTiming (NCIDLINK, StatusType)
	VALUES (@NCC_ID, 1)
	
Select SCOPE_IDENTITY() As Test
	
END

Used the scope identity to select from the table and assign to a column name of "Test".

Executing the following returns Test with the row value of the last Autonumber.

exec sp_Submit_NCC @DteOccur='12/11/2011', @PDetected='Chris', @DeptRaisedBy=2, @DeptResp=2, @NCDescrip='test', @NCCause='test', @NCImmediateAct='test', @NCLocation='test', @PNumOrRef='test', @EventCat=2, @ReportedEmailAddy=''

Output: Test 55

Once again though same error within my vba function of "item not found in current collection" :(

MyQ.SQL = sql_send
   MyQ.ReturnsRecords = False
 MyQ.Execute
 MsgBox MyQ.Parameters("Test").value

If I try to set the .returnsrecords = true I get an error regarding "cannot execute a select query" which is fine because you can only "execute" an insert/update/delete/append and a simple select does not get executed, which part of my stored procedure is doing.

[munkee]

OK managed to get this working by switching away from DAO and using ADO as I found a pretty decent tutorial regarding its usage. I have pretty much 0 knowledge of ADO so it would be nice to have found out the solution using DAO but the structure seems quite easy to understand in the new method so for now this will have to do.

My completed code:

Function ado_test()
Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    
    Dim stServer, stDatabase, stUsername, stPassword As String
    Dim stConnect As String
    
    stServer = "xxx.xxx.xxxx.net"
    stDatabase = "xxxxx"
    stUsername = "xxxxx"
    stPassword = "4B:mV6hpz_\2q=</Y%?b"
    stConnect = "DRIVER={SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword

   cnn.connectionString = stConnect
   Debug.Print stConnect
   cnn.Open
   cnn.CursorLocation = adUseClient
   
   
  With cmd
    .ActiveConnection = cnn
    .CommandText = "sp_Submit_NCC"
    .CommandType = adCmdStoredProc
    '.Parameters.Refresh
    .Parameters("@DteOccur").value = "12/11/2011"
    .Parameters("@PDetected").value = "'Chris'"
    .Parameters("@DeptRaisedBy").value = "2"
    .Parameters("@DeptResp").value = "2"
    .Parameters("@NcDescrip").value = "'Chris'"
    .Parameters("@NCCause").value = "'Chris'"
    .Parameters("@NCImmediateAct").value = "'Chris'"
    .Parameters("@NCLocation").value = "'Chris'"
    .Parameters("@PNumOrRef").value = "'Chris'"
    .Parameters("@EventCat").value = "2"
    .Parameters("@ReportedEmailAddy").value = "''"
    Set rs = .Execute()
  End With

  'Retrieve the ID the new record is stored at
  Dim id As Integer
  
  id = Nz(rs!test, 0)
  MsgBox id
End Function

[ADezii]

I realize that this is a long shot, and I'm not even sure if it is possible. For Validation purposes, within your context, can you integrate Code such as the following that will display the Name of each Parameter in the Parameters Collection of the QueryDef Object?

1. QueryDef SQL:
   

   SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.MI
   FROM Employees
   WHERE (((Employees.LastName)=[Enter Last Name]) AND ((Employees.FirstName)=[Enter First Name]));

2. Code to retrieve Parameter Names:
   

   Dim qdf As DAO.QueryDef
   Dim prm As DAO.Parameter
   
   Set qdf = CurrentDb.QueryDefs("qryEmployees")
   
   For Each prm In qdf.Parameters
     Debug.Print prm.Name
   Next

   [Enter Last Name]
   [Enter First Name]

3. OUTPUT:
   

   [Enter Last Name]
   [Enter First Name]

[NeoPa]

I would strongly recommend using DAO for all those case where Access, and only Access (Jet SQL), is used but for all other situations use ADODB.

[munkee]

Thank you for the input NeoPa. I assume this means Jet runs perfectly fine and is adept at working with the client side object manipulation etc but ADODB is the one for working with the server

[NeoPa]

I wouldn't phrase it quite that way, but if it helps :-
DAO works best in an exclusively Jet environment. That includes linked tables to outside sources.
ADODB works better when working directly with outside sources.

It would all be client-side essentially, as that's where the code executes.