ADO.NET - Trouble Getting Output Parameter

Question

My DBA created the following Stored Proc which he insists works fine when called in SQL Server:

CREATE procedure [dbo].[GetParentID]
    @SSHIP_AppID as varchar(50),
    @ParentID as varchar(150) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ParentID = a.iBuild_GUID

    FROM dbo.XRef_iBuild_SSHIP as a
    WHERE a.SSHIP_appId = @SSHIP_AppID
    AND a.SSHIP_appId <> ''
END

I have created the following ADO.NET Wrapper but I am having trouble getting the output parameter. I keep getting back "OUTPUT" as its value:

 private string GetParentId(string appId)
        {
            var connection = new SqlConnection();
            string parentId = String.Empty;
            try
            {
                connection.ConnectionString = "...)
                var command = new SqlCommand("GetParentId", connection);

                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@SSHIP_AppID", appId));

                command.Parameters.Add(new SqlParameter("@ParentID", ParameterDirection.Output));

                connection.Open();

                command.ExecuteNonQuery();

                parentId = (command.Parameters["@ParentId"].Value).ToString();
            }
            catch (Exception ex)
            {
                LogError(appId, ex.ToString(), "Interface12 - Cannot get ParentId", null, 0);
            }
            finally
            {
                connection.Close();
            }
            return parentId;
        }
    }

What am I doing wrong?

One error is command.Parameters[@ParentId"] should be ]"@ParentID"] but I still get the same errot — user2471435
– user2471435, Commented Nov 22, 2013 at 14:32

usr · Accepted Answer · 2013-11-22 14:35:50Z

1

In new SqlParameter("@ParentID", ParameterDirection.Output) the 2nd argument is treated as the object value argument and apparently converted to a string.

(This implicit conversion is, in my opinion, a design flaw in ADO.NET. It should throw an exception for any unknown input type.).

Choose a better overload.

answered Nov 22, 2013 at 14:35

usr

172k38 gold badges251 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ADO.NET - Trouble Getting Output Parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related