Calling Stored procedure with output

I have been trying to retrieve some information from my database, and also retrieve the return value. I know the Stored Procedure works fine.

The code I use is a modified piece I use for registering the user. It's going wrong at the cmd.ExecuteReader part of my code.

protected void btn_login_Click(object sender, ImageClickEventArgs e)
{
    //Actions after Submit button is clicked
    Page.Validate(((ImageButton)sender).ValidationGroup);

    if (Page.IsValid)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnectString"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("usp_validateUsers", conn);
            //Input Values
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("username", Uname.Text);
            cmd.Parameters.AddWithValue("password", pwd.Text);
            //Return Values
            SqlParameter retParam = cmd.Parameters.Add("@RetVal", SqlDbT开发者_如何学JAVAype.Int);
            retParam.Direction = ParameterDirection.ReturnValue;
            SqlParameter acsParam = cmd.Parameters.Add("@ac_status", SqlDbType.Int);
            acsParam.Direction = ParameterDirection.Output;
            SqlParameter nikParam = cmd.Parameters.Add("@memb_name", SqlDbType.VarChar);
            nikParam.Direction = ParameterDirection.Output;

            try
            {
                // Open Connection and execute Stored Proc
                conn.Open();
                ///////////SOMETHING GOES WRONG HERE///////////////
                cmd.ExecuteReader();

                //Retrieve Data
                int retVal = (int)retParam.Value;
                string nickname = nikParam.Value.ToString();
                string ac_stats = acsParam.Value.ToString();

                if (retVal != 0)
                {
                    //Invalid Username or password
                }
                else
                {
                    //Login User
                }
            }

            catch (Exception Error)
            {
                lbl_login.Text = "An error occured, please try again later";
                debug.Text = Error.Message;
            }

            finally
            {
                debug.Text = "\n Clossing Connection";
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    conn.Close();
                }

            }
        }
    }
}

When I just want to receive the return value I simply use cmd.ExecuteScalar(); I know how to receive data when I'm passing the SQL query to the SQL database, but it seems to be different when using Stored Procedures..

EDIT Probably could improve this code further but it really does what it should do.

ALTER PROCEDURE dbo.usp_validateUsers

@username varchar(10),
@password varchar(10),
@ac_status char(1) OUTPUT,
@memb_name varchar(15) OUTPUT

AS
IF EXISTS(SELECT * FROM MEMB_INFO WHERE (memb___id = @username))
BEGIN
    SELECT @ac_status = ac_status, @memb_name = memb_name
    FROM MEMB_INFO
    WHERE (memb___id = @username) AND (memb__pwd = @password)
    RETURN 0
END

ELSE
BEGIN
    return 1
END

When I use break points to catch possible exceptions in Visual Studio, It gives me: String[4]: The Size property has an invalid size of 0

The error you mentioned may be caused by the fact that you're not specifying the size of your VarChar parameters. Instead of having lines like this:

SqlParameter nikParam = cmd.Parameters.Add("@memb_name", SqlDbType.VarChar);

Try this:

SqlParameter nikParam = cmd.Parameters.Add("@memb_name", SqlDbType.VarChar, 15);

You need to create a SqlDataReader.

From Ins and Outs of using Stored Procedures in C#

The SqlDataReader class is used to read a forward only stream of records returned from the database. The SqlDataReader object is not instantiated directly through a constructor (hence the lack of the New key word) but rather through the ExecuteReader method of the SqlCommand object. Before calling the ExecuteReader method the connection to the database is established using the Open method of the SqlConnection object.

Try

SqlDataReader drLogins;
Conn.Open();
drLogins = cmd.ExecuteReader();

Your @ac_status is defined as integer in parameter. change it character or string.

SqlParameter acsParam = cmd.Parameters.Add("@ac_status", SqlDbType.Int);