1

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;
}
1
  • Keltex, Thank you very much, your answer is solved the problem. I changed code to and added the size property, and it started to return me proper full user name. Thank you a lot Commented Mar 13, 2009 at 23:58

2 Answers 2

3

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().

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

3 Comments

I changed it to <code> myCommand.ExecuteNonQuery(); </code> But still getting only the first letter of the name.
But did you change the size property?
Keltex, Thank you very much, your answer is solved the problem. I changed code to and added the size property, and it started to return me proper full user name. Thank you a lot
1

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

1 Comment

I think you mean the Size property.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.