Strange result return from sql server

Question

Hello everybody,

I am working on my project and having strange result with asp.net C# and stored procedure that returns scalar variable. In short, I am trying to return user name, however I get only the first letter from the name, which is strange.

I went trough the code couple dozen of times; however i still cannot find the error. I would really appreciate if somebody can find the mystake

Here is my stored procedure:

set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [one_two].[team5GetLostPassword]
(
    @user_password varchar(50),
    @email varchar(50),
    @user_name varchar(50) out,
    @user_exist int out
)
AS
BEGIN
    declare @user_fname varchar(50);
    declare @user_lname varchar(50);
    declare @user_id int;

    set @user_exist = 0;
    SET NOCOUNT ON;

    IF EXISTS(select user_email from team5_user_detail where user_email LIKE @email)
    begin
        set @user_id = (select userid from team5_user_detail where user_email Like @email);
        update team5_user Set user_password = @user_password where userid = @user_id;
        set @user_name = (select user_lname from team5_user_detail where userid = @user_id);
        set @user_exist = 1;
    end
END

and my C# code

protected bool IsValidUser()
{
    myConn = new SqlConnection(myConStr);
    myCommand = new System.Data.SqlClient.SqlCommand("team5GetLostPassword", myConn);
    myCommand.CommandType = CommandType.StoredProcedure;
    myCommand.Parameters.AddWithValue("@user_password", userPasswordEncrypted);
    myCommand.Parameters.AddWithValue("@email", userEmailString);

    myCommand.Parameters.AddWithValue("@user_name", "").Direction = ParameterDirection.Output;
    myCommand.Parameters.AddWithValue("@user_exist", 0).Direction = ParameterDirection.Output;

    try
    {
        myConn.Open();
        myCommand.ExecuteScalar();
        userExist = myCommand.Parameters["@user_exist"].Value.ToString();
        userName = myCommand.Parameters["@user_name"].Value.ToString();
    }
    catch (Exception exep)
    {
    }
    finally
    {
        myConn.Close();
    }

    if (userExist.Contains("1"))
        return true;
    else
        return false;
}

Answer 1

You need to set the Size property of the SqlParameter to the length of data you want returned.

I don't think you want to use ExecuteScalar() in this case. ExecuteScalar() is used when you want to return the first column of the first row in the result set. Since you are trying get values from output parameters, you want to use ExecuteNonQuery().

Answer 2

Try setting the Length property of the user name parameter to 50.