none
Output Parameters Return Null in .Net 4.0, looks like a ADO Bug (maybe?) RRS feed

  • Question

  • I found that if my stored procedure has the @_Debug variable (set to 1) and if part of the sp has a select * from [worktable] and a SELECT "Variable" = @_Variable (protected by @_Debug) then the Output variables in my VB.Net program return NULL.  If I set @_Debug = 0 so the debug code doesn't run then the parameters work.  What gives? 

                With sqlCmd
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.Add(New SqlParameter("@_iSessionID", SqlDbType.Int))
                    .Parameters("@_iSessionID").Value = lblSessionID.Text
                    .Parameters.Add(New SqlParameter("@_iCompanyID", SqlDbType.VarChar, 3))
                    .Parameters("@_iCompanyID").Value = msCompanyID
                    .Parameters.Add(New SqlParameter("@_iUserID", SqlDbType.VarChar, 30))
                    .Parameters("@_iUserID").Value = Environment.UserName
                    ' Output parameters
                    .Parameters.Add(New SqlParameter("@_oBatchID", SqlDbType.VarChar, 15))
                    .Parameters("@_oBatchID").Direction = ParameterDirection.Output
                    .Parameters.Add(New SqlParameter("@_oRetVal", SqlDbType.Int))
                    .Parameters("@_oRetVal").Direction = ParameterDirection.Output
                    .CommandType = CommandType.StoredProcedure
                    conMAS.Open()
                    .ExecuteReader()
                    msBatchID = .Parameters("@_oBatchID").Value
                    fniCreateInvoices = .Parameters(4).Value
                End With

     

    Thanks,

    John

    Monday, April 4, 2011 2:19 PM

Answers

  • This is just how SQL Server works.  It sends the output parameters last, after all of the resultsets from the stored procedure.  So you have to drain the responses (select statements) to get to the output parameters.  See this topic for details on how to drain the results:

     

    http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

    Basically you need to keep calling NextResult until there are no more.

     

    Matt


    Matt
    Sunday, April 10, 2011 10:42 PM
    Moderator

All replies

  • Hi Hanrahan,

    Welcome!

    I'm not very clear about your problem, I didn't see the @_Dbug parameter in your code, would you please give some sql schema and more information to let me repro your question. Here are some links about how to get output parameters from stored procedure, I hope it's help:

    http://www.programmingado.net/a-145/Retrieving-SQL-Server-stored-procedure-output-parameters.aspx

    http://www.codeproject.com/KB/cs/simplecodeasp.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, April 5, 2011 7:19 AM
    Moderator
  • In the sp (sparCreateInvoices_Toy):

    CREATE PROCEDURE sparCreateInvoices_Toy(
     @_iSessionID  INT,
     @_iCompanyID  VARCHAR(3),
     @_iUserID   VARCHAR(30),
     @_oBatchID   VARCHAR(30) OUTPUT,
     @_oRetVal   INT  OUTPUT)
    AS
    BEGIN -- Begin SP

    DECLARE  @_Debug   SMALLINT
    SELECT  @_Debug = 1

    IF @_Debug = 1
     SELECT * FROM tarRapidCMEntry_Toy WHERE SessionID = @_iSessionID

    <Code>

    IF @_Debug = 1
      SELECT "@_ApplyToInvcID" = @_ApplyToInvcID

    <Code>

    If in the stored procedure @_Debug = 1 then the return values won't pass back to the VB.Net program, if @_Debug is set to 0 then it works fine.  The return values work fine inside of SQL with Debug on or off.

    Thank you,

    John

    Tuesday, April 5, 2011 2:13 PM
  • Hi Hanrahan,

    Thanks for your feedback.

    I think there may two ways to lead your problem.

    1. multiple result sets from Stored Procedure, ADO.net will retrieve the first result set by default, when @_Debug is set to 0, It works. I think you should use IDataReader.NextResult() to retrieve the return values.

    ==================================================

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/cdaee35a-f18d-4c7c-bcfd-a6c29d2a0c62/

    http://www.itjungle.com/mgo/mgo121703-story01.html

    =================================================

    2. different user with different permissions

    ===============================================

    http://stackoverflow.com/questions/370165/datareader-returns-no-results-in-vs-yet-stored-procedure-returns-multiple-result

    ==============================================

    By the way, Based on your code, I change Selete * into Selete @pam=

    Create procedure SelectEmployee
    (
    @employeeId int,@name nvarchar(20) output,@mdate datetime output
    )
    as 
    begin
    declare @_Debug smallint
    select @_Debug=1
    IF @_Debug=1
    select @name=Name,@mdate=ModifyTime from Employees where EmployeeId=@employeeId
    end

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.




    Wednesday, April 6, 2011 3:04 AM
    Moderator
  • Alan,

    It seems to me you don't understand what I've done and why.  @_Debug is used for debuging and is very useful when you run a stored procedure in the SQL Management Studio.  It has nothing to do with what I want returned to my vb program. 

    If ADO is ignoring what I've set the OUTPUT variables to in my stored procedure then that's a bug.  Maybe a documented somewhere bug, but a bug.  If you'd like I can walk you or someone else at Microsoft through it.  It is somewhat difficult to put it all into a Forum post.

    Thanks for checking it out.

    John

    Wednesday, April 6, 2011 3:27 PM
  • This is just how SQL Server works.  It sends the output parameters last, after all of the resultsets from the stored procedure.  So you have to drain the responses (select statements) to get to the output parameters.  See this topic for details on how to drain the results:

     

    http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

    Basically you need to keep calling NextResult until there are no more.

     

    Matt


    Matt
    Sunday, April 10, 2011 10:42 PM
    Moderator
  • I have got to say, that developing modules within DotNetNuke, I find this is also more of a "Bug" than a design feature.

    I have a situation where I would like to be able to page data in DNN, and the sensible thing to do when I request the first page of data would be for the SP to return the number of pages which it calculates in an output parameter,  as well as the record set.

    All standard DNN modules call SPs in a standard format in their data provider, and then pass the returned DataReader into the Dotnetnuke CBO to populate the Buisiness Objects.

    However, due to this feature, I have to either re-write the CBO's Fill functions, which are in the DNN core, and which could change, OR I have to call a second stored procedure just to get the page size.

    Wouldn't it make more sense for SQL to return the parameters in the first record set, not after them all?

    Wednesday, May 30, 2012 1:54 PM