CODEWITHSUNDEEP
c#sql-serverstored-procedures
dsb
dsb

Reputation: 2517

stored procedure not working properly error converting datatype

Trying to get Username by UserId - I have the following stored procedure:

ALTER PROCEDURE [dbo].[GET_UsernameByUserId_SP](
        @UserId             int,
        @ExecutionResult    nvarchar(64)    OUTPUT
        )
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SET @ExecutionResult = (SELECT TOP 1 Username FROM UserProfile WHERE UserId = @Userid);

END

Executed by the following method:

    public string CallSpRetStr(String spName, SqlParameter[] sqlParams)
    {
        string sRet = null;

        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.CommandText = spName;

        myCommand.Parameters.Clear();
        myCommand.Parameters.AddRange(sqlParams);

        myCommand.Parameters.AddWithValue("@ExecutionResult", DbType.String);
        myCommand.Parameters[myCommand.Parameters.Count - 1].Direction = ParameterDirection.Output;

        try
        {
            if (myConnection.State == ConnectionState.Open)
            {
                myCommand.ExecuteNonQuery();
            }
            else
            {
                OpenConnection();
                myCommand.ExecuteNonQuery();
                CloseConnection();
            }
            sRet = myCommand.Parameters["@ExecutionResult"].Value.ToString();
        }
        catch (Exception ex)
        {
            CloseConnection();
        }

        return sRet;
    }

Called by the following method:

    public string GetUsernameByUserId(int UserId)
    {
        SqlParameter[] parameters = new SqlParameter[1];
        parameters[0] = new SqlParameter("@UserId", UserId);

        return dal.CallSpRetStr("GET_UsernameByUserId_SP", parameters);
    }

At runtime I get the following error message (caught by the try-catch in CallSpRetStr): 'Error converting datatype nvarchar to int'.

I've been banging my head in the wall for more than hour now, trying crazy things etc. I have two questions: 1. Does anyone understand what is the problem in all the above? 2. Is anyone aware of a better way to get the username by the userid?

Thanks in advance.

Upvotes: 1

Views: 220

Answers (1)

Steve
Steve

Reputation: 216293

This line is incorrect 

myCommand.Parameters.AddWithValue("@ExecutionResult", DbType.String);

should be 

myCommand.Parameters.AddWithValue("@ExecutionResult", new string(' ', 64);

The AddWithValue method expects, for its second parameter, the current value for the named parameter but you pass an enum (DbType.String == (int)16). AddWithValue then tries to build a parameter with the datatype corresponding to the value passed and thus creates an integer parameter. Of course this is not acceptable by your stored procedure that expects a nvarchar type

Also I would remove any possible misunderstanding on which parameter is the output one using the return value from the AddWithValue instead of an indexing on the parameter collection

 SqlParameter p myCommand.Parameters.AddWithValue("@ExecutionResult", new string(' ', 64);
 p.Direction = ParameterDirection.Output;

Notice that we need to create a string of the correct size because AddWithValue doesn't know the expected size of the parameter from the stored procedure and so it creates the parameter with the size equals to the length of the string passed.

Upvotes: 4

Related Questions