CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
WeekendHacker
WeekendHacker

Reputation: 316

SqlException, Error converting data type nvarchar to int

Have a strange issue, think it's related to the OUT parameters in my stored procedure?

Stored procedure:

ALTER PROCEDURE [dbo].[uspProcessResetRequest] 
    @user uniqueidentifier, 
    @company uniqueidentifier,
    @mobile nvarchar(50) OUT, -- return phone number
    @system nvarchar(256) OUT -- return system name
AS
BEGIN
    SET NOCOUNT ON;

    SET @mobile = 'error' --for debugging
    SET @system = 'error' --for debugging

    -- Select matching row from ResetQueue into temporary table
    INSERT INTO ResetHistory ([User_ID], MobilePhone, Company_ID, [System], [Status], ResetIP)
        SELECT 
            rq.[User_ID], u.MobilePhone, u.Company_ID, rq.[System], '3', rq.ResetIP 
        FROM  
            ResetQueue rq
        INNER JOIN 
            [User] u ON rq.[User_ID] = u.ObjectGuid
        WHERE 
            u.Company_ID = @company 
            AND rq.[User_ID] = @user 
            AND rq.[Status] = '2'           

    SELECT @mobile = u.[MobilePhone] 
    FROM [user] u 
    WHERE u.ObjectGuid = @user

    SELECT @system = rq.[system] 
    FROM ResetQueue rq 
    WHERE rq.[User_ID] = @user

    DELETE FROM ResetQueue 
    WHERE [User_ID] = @user
END

Calling C# code:

try
{
    using (SqlConnection conn = new SqlConnection(strConnection))
    {
        using (SqlCommand cmd = new SqlCommand("dbo.uspProcessResetRequest", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@user", SqlDbType.UniqueIdentifier).Value = strUser;
            cmd.Parameters.AddWithValue("@company", SqlDbType.UniqueIdentifier).Value = strCompany;
            cmd.Parameters.AddWithValue("@mobile", SqlDbType.NVarChar).Direction = ParameterDirection.Output;
            cmd.Parameters.AddWithValue("@system", SqlDbType.NVarChar).Direction = ParameterDirection.Output;

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            string[] strResult = new string[] { (string)cmd.Parameters["@mobile"].Value, (string)cmd.Parameters["@system"].Value };

            return strResult;
        }
    }
}
catch (Exception err)
{
    string strError = err.Message; // Log error
    return new string[] { "", "" };
}

The stored procedure runs just fine - all code gets executed but cmd.ExecuteNonQuery(); throws an exception:

SqlException: Error converting data type nvarchar to int.

so I loose the output values.

I'm suspecting that the OUT parameters are facing some issues, but can't seem to crack this nut...

Most other posts I found for this issue relates to Text values being submitted to SqlInt parameters. This seems not the case with this one as the stored procedure actually executes and moves data around as expected.

Upvotes: 0

Views: 2658

Answers (2)

bastos.sergio
bastos.sergio

Reputation: 6764

Your C# code looks fine... The fact that the line cmd.ExecuteNonQuery(); throws an exception, signifies that the error must lie with some parameter used by the SP.

You will need to isolate your SP in steps to identify the problem area...

For instance, in this statement does the ResetHistory table have the same column types used in your select?

INSERT INTO ResetHistory ([User_ID], MobilePhone, Company_ID, [System], [Status], ResetIP)
SELECT rq.[User_ID], u.MobilePhone, u.Company_ID, rq.[System], '3', rq.ResetIP
FROM ResetQueue rq
...

In this statement, does the mobilephone column return a Varchar value?

SELECT u.[MobilePhone] 
FROM [user] u 
WHERE u.ObjectGuid = @user

In this statement, does the system column return a Varchar value?

SELECT rq.[system] 
FROM ResetQueue rq 
WHERE rq.[User_ID] = @user

Upvotes: 0

WeekendHacker
WeekendHacker

Reputation: 316

@Gwashoppa, thx for taking time to answer my question. To my understanding return values are not to be mixed with OUTPUT parameters. But in any case I found the solution - data type and length needs to be set explicitly:

cmd.Parameters["@mobile"].SqlDbType = SqlDbType.NVarChar;
cmd.Parameters["@mobile"].Size = 50;

I thought that happened in the AddWithValue function, but apparently not.

Upvotes: 1

Related Questions