Bug 3740 - CommandType.StoredProcedure with out parameters doesnt return the values
Summary: CommandType.StoredProcedure with out parameters doesnt return the values
Status: NEW
Alias: None
Product: Class Libraries
Classification: Mono
Component: System.Data (show other bugs)
Version: unspecified
Hardware: PC Windows
: --- normal
Target Milestone: Untriaged
Assignee: Bugzilla
URL:
Depends on:
Blocks:
 
Reported: 2012-03-02 20:36 UTC by arjan
Modified: 2012-03-02 20:36 UTC (History)
1 user (show)

See Also:
Tags:
Is this bug a regression?: ---
Last known good build:


Attachments

Description arjan 2012-03-02 20:36:11 UTC
Here's part of my (8 year old) code:

   Dim params As New ArrayList
        params.Add(New SqlParameter("@username", SqlDbType.VarChar, 255))
        params(params.Count - 1).Value = UserName
        params.Add(New SqlParameter("@password", SqlDbType.VarChar, 255))
        params(params.Count - 1).Value = Password
        params.Add(New SqlParameter("@SessionId", SqlDbType.VarChar, 255))
        params(params.Count - 1).Direction = ParameterDirection.Output
        params.Add(New SqlParameter("@UserId", SqlDbType.Int))
        params(params.Count - 1).Direction = ParameterDirection.Output
        params.Add(New SqlParameter("@StatusCode", SqlDbType.Int))
        params(params.Count - 1).Direction = ParameterDirection.Output
        params.Add(New SqlParameter("@StatusString", SqlDbType.VarChar, 255))
        params(params.Count - 1).Direction = ParameterDirection.Output
        Run("His", "Login", params)
        SessionId = CStr(params(2).Value)
        UserId = CInt(params(3).Value)
        StatusCode = CInt(params(4).Value)
        StatusString = CStr(params(5).Value)

Run does:

    Dim myCommand As SqlCommand = New SqlCommand(SP_Name, myConnection)
            myCommand.CommandType = CommandType.StoredProcedure
            Dim param As SqlParameter
            For Each param In Params
                myCommand.Parameters.Add(param)
            Next
            myCommand.Connection.Open()
            myCommand.ExecuteNonQuery()

After this, userid is still 0, though the stored procedure does return a new value. MS does get the new value. 

Workaround:

Get the stored procedure to return values by SELECTing them.

SELECT @SessionId, @UserId,@StatusCode,@StatusString 

(I vaguely remember that Oracle stored procedures can't return records, but whatever)

Note You need to log in before you can comment on or make changes to this bug.