CODEWITHSUNDEEP
c#sql-serverstored-procedures
Husain Alhamali
Husain Alhamali

Reputation: 823

Cannot get return value from stored procedure

I know this question has been asked many many times, I've gone through most of them but none resolved my issue

I have a stored procedure called LoginValidation which takes 2 parameters, username and password and returns the user_id of the corresponding user.

Here is the procedure code:

    @username nvarchar(50),
    @password nvarchar(50),
    @userID int output
AS
    SELECT @userID = user_id
    FROM Users_Master 
    WHERE user_name = @username and password = @password;

    RETURN

But, when I'm trying to use this procedure in C# the following error occurs:

Procedure or function 'LoginValidation' expects parameter '@userID', which was not supplied

Here is my C# code:

protected void btnLogin_Click(object sender, EventArgs e)
{
    cmd.Connection = con;

    con.Open();

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "LoginValidation";

    cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = txtmobile.Text;
    cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = txtpwd.Text;

    SqlParameter returnParm = cmd.Parameters.AddWithValue("@userID", SqlDbType.VarChar);
    returnParm.Direction = ParameterDirection.ReturnValue;

    string id = returnParm.Value.ToString();

    try
    {
        dr = cmd.ExecuteReader();
    }
    catch (SqlException EX)
    {
        string ff = EX.Message;
    }

    if (dr.HasRows)
    {
        while (dr.Read())
        {
            Session["name"] = dr["user_name"].ToString();

            lblmsg.Text = "Login successful: " + id;
        }
    }
    else
    {
        lblmsg.Text = "Invalid username/password";
    }

    dr.Close();
    con.Close();
}

Any suggestions please?

Upvotes: 1

Views: 1137

Answers (4)

Zein Makki
Zein Makki

Reputation: 30052

Be aware that you're using an incorrect method AddWithValue while passing the data-type. You need to change that to:

SqlParameter returnParm = cmd.Parameters.Add("@userID", SqlDbType.VarChar);
returnParm.Direction = ParameterDirection.Output;

Also you need to set the Direction to Output

Then you need to read the value after executing the query:

dr = cmd.ExecuteReader();
string id = returnParm.Value.ToString();

Docs:

Input: The parameter is an input parameter.

InputOutput: The parameter is capable of both input and output.

Output: The parameter is an output parameter.

ReturnValue: The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.

Upvotes: 2

Deepak Sharma
Deepak Sharma

Reputation: 419

you are doing mistake. Call

string id = returnParm.Value.ToString();

after 

dr = cmd.ExecuteReader();

Upvotes: 1

Deepak Sharma
Deepak Sharma

Reputation: 419

First you decide that you want to get output parameter or return value parameter.

  1. to get return value parameter. You don't have to pass any extra parameter to stored procedure. just add below lines

    SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();

 int id = (int) returnParameter.Value;

  2. to get output parameter. All the steps you done are fine. just replace

    returnParm.Direction = ParameterDirection.ReturnValue;

with

  returnParm.Direction = ParameterDirection.Output;

Upvotes: 1

romain-aga
romain-aga

Reputation: 1561

Try with returnParm.Direction = ParameterDirection.Output;

Upvotes: 2

Related Questions