Problem getting ADO to return the value from a stored procedure 1

[Katerine]

Hi,
I'm new to adp files and fairly new to stored procedures, although I've been coding in ADO for years. I'm having this horrible problem that's been stumping me for hours.

I have a stored procedure which takes the current user login (in my case, "Geek Katie"), and a user option (in this case, "Employee Search") and returns the integer value in the OptionValue column in a table.

- When I run this in Management Studio, it works perfectly.
- When I run this by double-clicking the stored procedure in the ADP file, it runs perfectly, but returns nothing. Unless I add a select statement to the stored procedure, in which case it works perfectly.
- When I run this through ADO (through some rather convoluted code that I've set up so that I can reuse code a lot), it gets and stores the correct parameters (I've confirmed this by examining the "comm" object while stepping through), and it runs... but then it returns Null. Every time. WHY is it returning Null???

Here's all the relevant code. I've snipped a bit, but left everything relevant.

Here's the stored procedure:

USE [UnionMembership]
GO

/****** Object:  StoredProcedure [dbo].[sp_gen_All_GetUserOption]    Script Date: 07/21/2010 19:02:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_gen_All_GetUserOption]
	@OptionType varchar(30),
	@StaffLogin varchar(30),
	@OptionValue int = 0 output
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
	SET NOCOUNT ON;

    -- Note: if the select statement returns empty, then @OptionValue will be Null.
	set @OptionValue = (SELECT OptionValue FROM Lookup_UserOptions WHERE (Lookup_UserOptions.StaffLogin = @StaffLogin) AND (Lookup_UserOptions.OptionType = @OptionType))
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspLogError];
END CATCH;
END
GO

Here's the function that I've been testing (the one that successfully calls the procedure with the correct parameters, but returns Null):

Public spSetSelected As New StoredProcedure

Public Function GetUserOption(sOptionName As String) As Variant
On Error GoTo Err_GetUserOption

   Dim sCurrUser As String, vUserOption As Variant
   
   sCurrUser = GetCurrentUser
   spSetSelected.OpenConnectionLocal
   spSetSelected.sp_name = "sp_gen_All_GetUserOption"
   spSetSelected.add_param "@StaffLogin", adVarChar, adParamInput, sCurrUser, 30
   spSetSelected.add_param "@OptionType", adVarChar, adParamInput, sOptionName, 30
   spSetSelected.add_param "@OptionValue", adInteger, adParamInputOutput
   vUserOption = spSetSelected.Run_SP_Command_Reader_Value("@OptionValue")
   spSetSelected.CloseStoredProcedure
   GetUserOption = vUserOption
   Debug.Print vUserOption
   
   
Exit_GetUserOption:
   Exit Function
   
Err_GetUserOption:
   MsgBox Err.Description, , "GetUserOption: " & Err.Number
   Resume Exit_GetUserOption
End Function

And here's the "StoredProcedure" class module that's being called:

Option Compare Database
Option Explicit

Private comm As New ADODB.Command
Private cnSQL As New ADODB.Connection
Private m_sp_name As String

Private Sub Class_Initialize()

   Set comm = New Command

End Sub

Public Sub OpenConnectionLocal()
   Set cnSQL = CurrentProject.Connection
End Sub

Public Sub OpenConnection(sServer As String, sDatabase As String, sUID As String, sPwd As String, bolTrusted As Boolean) 'snipped - not relevant
End Sub

'add each parameter to the comm object
Public Sub add_param(param_name As String, param_type As DataTypeEnum, param_direction As ParameterDirectionEnum, Optional param_value As Variant, Optional param_size As Variant)

   Dim param As New Parameter
   param.Name = param_name
   param.type = param_type
   param.Direction = param_direction
   If Not IsMissing(param_value) Then
      param.Value = param_value
   End If
   If Not IsMissing(param_size) Then
      If IsNumeric(param_size) Then
         param.size = CLng(param_size)
      End If
   End If

   comm.Parameters.Append param
End Sub
   
Public Function Run_SP_Command_Reader() As ADODB.Recordset
'Snipped - not relevant   
End Function

'For recordsets that return values: run the procedure returning the resulting value
Public Function Run_SP_Command_Reader_Value(sOutputParamName As String) As Variant
   
   Dim vReturnValue As Variant
   
   comm.Execute
   vReturnValue = comm.Parameters(sOutputParamName).Value
   Run_SP_Command_Reader_Value = vReturnValue
   
End Function

'run the procedure returning the results to the passed recordset
Public Sub Run_SP_Command_No_Return()

   comm.Execute

End Sub

Public Sub CloseStoredProcedure()
   Set comm = Nothing
   cnSQL.Close
   Set cnSQL = Nothing
End Sub

Private Sub begin_setup()

   If m_sp_name = "" Then Stop
   
   With comm
      .CommandText = m_sp_name
      .CommandType = adCmdStoredProc
      .ActiveConnection = cnSQL
   
   End With

End Sub

Public Property Get sp_name() As String
   sp_name = m_sp_name
End Property

Public Property Let sp_name(ByVal vNewValue As String)
   m_sp_name = vNewValue
   Call begin_setup

End Property

FYI, I've successfully run the Run_SP_Command_No_Return method against this database in the past. This is the first time I've tried to get Run_SP_Command_Reader_Value to work, and it's not working.

Here's what I know:
When stepping through, during the Run_SP_Command_Reader_Value method, after the "comm.Execute" line, when I examine the comm object, I see that there are three parameters. @OptionType = "Employee Search" - which is correct. @StaffLogin = "Geek Katie" - which is correct. @OptionValue is null, which is not correct.

In the Lookup_UserOptions table, there is currently a single record. In that record, StaffLogin = Geek Katie , OptionType = Employee Search , and OptionValue = 1 . Therefore, when I call this procedure with the parameters "Geek Katie" and "Employee Search", it should return 1. And it does. Unless I'm calling from the ADP file.

I have tried changing the OptionValue from sql_variant (which it was originally) to int. I have tried changing the parameters to use CreateParameter instead of setting each parameter setting individually. I have tried creating return values in the stored procedure, and adding a select statement so it returns a recordset, and updating the code so that it reads from the recordset instead of reading an output parameter. I have tried changing the input parameters to "N'Geek Katie'" and "N'Employee Search'". All with the same results. It always returns Null.

Please tell me what I'm doing wrong - I'd very much appreciate it!

Katie

 

[Katerine]

Oh, and I'm running SQL Server Express 2008, and the ADP file is in Microsoft Access 2000 (9.0.2720).

Thanks again for any help!

Katie

 

[PWise]

what happens when you switch these 2 lines

[s]spSetSelected.CloseStoredProcedure   
GetUserOption = vUserOption[/s]
GetUserOption = vUserOption

spSetSelected.CloseStoredProcedure

or how about changing in th Sp to
and running as a recordset

   -- Note: if the select statement returns empty, then @OptionValue will be Null.    
Select  @OptionValue =OptionValue 
FROM Lookup_UserOptions 
WHERE (Lookup_UserOptions.StaffLogin = @StaffLogin) 
AND (Lookup_UserOptions.OptionType = @OptionType)
Select  @OptionValue as OptionValue

 

[Katerine]

PWise:
Thanks for the tips - unfortunately, the first has no effect, and the second tip was something I'd already tried (but forgot to mention).

Since my last post, I've tried a number of things, and even though it still doesn't work, I feel like I'm getting in the right direction. Below is a brief summary of the changes I made, followed by the new code, followed by an examination of the command object when I'm stepping through. I'd still really like any help that can be provided.

Changes since my last post:
[ul]
[li]Started running the ADP in Access 2002 SP3 instead of Access 2000[/li]
[li]Changed the ADO library from 2.1 to 2.6, and commented out the "append" command for the output parameter, as it was no longer needed with ADO 2.6.[/li]
[li]Added a comm.Parameters.Refresh command after comm.Execute in the Run_SP_Command_Reader_Value function in the StoredProcedure class.[/li]
[li]Changed the order of adding the parameters, so that I add the parameters in the same order I declare them in the stored procedure. Surprisingly, this actually had an effect, in that the @OptionValue parameter now returns empty instead of null. Sometimes. Not always. Either way, though, it's still not what I want.[/li]
[li]Added a return value to the stored procedure, so that the return value is the same as the @OptionValue.[/li][/ul]

Here's the code that's changed. If it's not included here, than it's still the same as what I've got in my first post.

The new GetUserOption code:

Public Function GetUserOption(sOptionName As String) As Variant
On Error GoTo Err_GetUserOption

   Dim sCurrUser As String, vUserOption As Variant
   
   sCurrUser = GetCurrentUser
   spSetSelected.OpenConnectionLocal
   spSetSelected.sp_name = "sp_gen_All_GetUserOption"
   spSetSelected.add_param "@OptionType", adVarChar, adParamInput, sOptionName, 30
   spSetSelected.add_param "@StaffLogin", adVarChar, adParamInput, sCurrUser, 30
'   spSetSelected.add_param "@OptionValue", adInteger, adParamOutput
   vUserOption = spSetSelected.Run_SP_Command_Reader_Value("@OptionValue")
   spSetSelected.CloseStoredProcedure
   GetUserOption = vUserOption
   Debug.Print vUserOption
   
   
Exit_GetUserOption:
   Exit Function
   
Err_GetUserOption:
   MsgBox Err.Description, , "GetUserOption: " & Err.Number
   Resume Exit_GetUserOption
End Function

The new Run code in the StoredProcedure class module:

Public Function Run_SP_Command_Reader_Value(sOutputParamName As String) As Variant
   
   Dim vReturnValue As Variant
   
   comm.Execute
   comm.Parameters.Refresh
   vReturnValue = comm.Parameters(sOutputParamName).Value
   Run_SP_Command_Reader_Value = vReturnValue
   
End Function

The new stored procedure:

USE [UnionMembership]
GO
/****** Object:  StoredProcedure [dbo].[sp_gen_All_GetUserOption]    Script Date: 07/26/2010 12:54:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<snipped>
-- Create date: 7/21/2010
-- Description:	This returns the requested user option for the requested user.
-- =============================================
ALTER PROCEDURE [dbo].[sp_gen_All_GetUserOption]
	-- Add the parameters for the stored procedure here
	@OptionType varchar(30),
	@StaffLogin varchar(30),
	@OptionValue int = 0 output
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    -- Note: if the select statement returns empty, then @OptionValue will be Null.
	set @OptionValue = (SELECT OptionValue FROM Lookup_UserOptions WHERE (Lookup_UserOptions.StaffLogin = @StaffLogin) AND (Lookup_UserOptions.OptionType = @OptionType))
	RETURN (SELECT OptionValue FROM Lookup_UserOptions WHERE (Lookup_UserOptions.StaffLogin = @StaffLogin) AND (Lookup_UserOptions.OptionType = @OptionType))
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspLogError];
END CATCH;
END

Some info from examining the comm object while stepping through:
When stepping through, after the "comm.Execute" line:
The ActiveConnection property, which is set equal to CurrentProject.Connection, has the connection string, "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=KATIE-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=UnionMembership;Data Provider=SQLOLEDB.1". CursorLocation = adUseClientBatch, IsolationLevel = adXactReadCommitted, Mode = adModeUnknown, Version = "6.0"
The CommandText is, "{ call sp_gen_All_GetUserOption(?, ?) }".
The CommandType is adCmdStoredProc.
The name of the comm object is "".
NamedParameters = False.
There are two parameters:
- Item 1: Name = "@OptionType", Direction = adParamInput, Type = adVarChar, Value="Employee Search". Size = 30.
- Item 2: Name = "@StaffLogin", Direction = adParamInput, Type = adVarChar, Value="Geek Katie". Size = 30.
Prepared = False.
State = 0.

After the "comm.Parameters.Refresh" line, the parameters change. There are now 4 of them, and they're all empty or null:
- Item 1: Name = "@RETURN_VALUE", Direction = adParamReturnValue, Type = adInteger, Value = Empty.
- Item 2: Name = "@OptionType", Direction = adParamInput, Type = adVarChar, Value = Empty. Size = 30.
- Item 3: Name = "@StaffLogin", Direction = adParamInput, Type = adVarChar, Value = Empty. Size = 30.
- Item 4: Name = "@OptionValue", Direction = adParamInputOutput, Type = adInteger, Value = Null.


Again, if I run the stored procedure in SQL Server Management Studio with the parameters, "Employee Search" and "Geek Katie" (for @OptionType and @StaffLogin, respectively), I get the correct results: @OptionValue = 1, and @Return_Value = 1. It's only when I run it from the ADP file that it returns null. Please help! I can't do anything else until I get this fixed!

Many thanks again...

Katie

 

[PWise]

this is what I use

Alter procedure SpTomorow

@today datetime,
@Tommorow Datetime =null output

as

Select @Tommorow=@today+1
Select @Tommorow
return

Option Compare Database
Option Explicit
Public cnn As New ADODB.Connection
Dim cmd As New ADODB.Command

Function InitializeAdo()
If cnn.State = adStateClosed Then
    cnn.ConnectionTimeout = 0
    cnn.Open CurrentProject.Connection
End If
End Function


Function ExecuteAdoOutput(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Command
'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
InitializeAdo
cmd.CommandText = AdoString
Set cmd.ActiveConnection = cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
    cmd.Parameters(Prams) = AdoPrams(Prams)
Next Prams
cmd.Execute
Set ExecuteAdoOutput = cmd
End Function

Sub TestOutput()
Const Tomorowdate = 2
Dim Tomorow As ADODB.Command
Set Tomorow = ExecuteAdoOutput("SpTomorow", 4, 0, Now)
MsgBox Tomorow(Tomorowdate)
End Sub

 

[Katerine]

Thank you!

I made a couple of modifications (I needed to pass a 0 as the first parameter to account for @RETURN_VALUE), and it worked like a charm. You are magnificent.

Katie