[Solved] ASP.Net Output Parameter Error: An SqlParameter with ParameterName is not contained by this SqlParameterCollection

Last Reply one month ago By dharmendr

Posted one month ago

On directly executing storedProcedure I am getting output as I need, but when I am accessing the stored Procedure from controller then It's giving me an error as 

An SqlParameter with ParameterName '@role' is not contained by this SqlParameterCollection.

below is my sp: 

ALTER procedure [dbo].[usp_login]
 @flag char(1)=null,
 @unm varchar(30)=null,
 @pwd varchar(128)=null,
 @salt varchar(30)=null,
 @syshashpwd varchar(128)=null,
 @role char(1)=null OUTPUT,
 @status int=null OUTPUT,
 @uid int=null OUTPUT
as
BEGIN
     .......
END

controller code: 

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Login(LoginVM entity)
{
    string OldHASHValue = String.Empty;
    string SALT = String.Empty;
    string expectedHashString = String.Empty;
    int status = 0;
    string role = string.Empty;
    int userid = 0;
    try
    {
        //using (db = new DBLoginEntities())
        //{  
        // Ensure we have a valid viewModel to work with  
        if (!ModelState.IsValid)
        {
            return View(entity);
        }

        var userInfo = String.Empty;//db.UserMasters.Where(s => s.Username == entity.Username.Trim()).FirstOrDefault();  
        var saltVAL = String.Empty;//db.tblsalts.FirstOrDefault();

        //Retrive SALT Value From Database
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["roconstr"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_login";
                cmd.Connection = conn;
                cmd.Parameters.AddWithValue("@flag", "S");
                conn.Open();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        SALT = dr["saltvalue"].ToString();
                    }
                }
                conn.Close();
            }
        }

        //Generate Hashed Pwd
        expectedHashString = Get_HASH_SHA512(entity.Password, entity.Username, SALT);

        //Pass values to usp_login procedure to validate user
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["roconstr"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_login";
                cmd.Connection = conn;
                cmd.Parameters.AddWithValue("@flag", "L");
                cmd.Parameters.AddWithValue("@unm", entity.Username);
                cmd.Parameters.AddWithValue("@pwd", entity.Password);
                cmd.Parameters.AddWithValue("@salt", SALT);
                cmd.Parameters.AddWithValue("@syshashpwd", expectedHashString);
                cmd.Parameters["@role"].Direction = ParameterDirection.Output;
                cmd.Parameters["@status"].Direction = ParameterDirection.Output;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                status = Convert.ToInt32(cmd.Parameters["@status"].Value);
                if (status == 0)
                {
                    //Login Fail
                    ViewBag.Message = string.Format("Access Denied! Wrong Credential");
                    //TempData["ErrorMSG"] = "<script>alert('Access Denied! Wrong Credential');</script>";  
                    return View(entity);
                }
                else
                {
                    role = Convert.ToString(cmd.Parameters["@role"].Value);
                    userid = Convert.ToInt32(cmd.Parameters["@uid"].Value);
                    SignInRemember(entity.Username, entity.isRemember);

                    //Set A Unique ID in session
                    Session["UserID"] = userid;
                    Session["UserRoll"] = role;
                    Session["Username"] = entity.Username;

                    // If we got this far, something failed, redisplay form  
                    return RedirectToAction("Dashboard", "Dashboard");
                }

            }
        }
        // }  
    }
    catch
    {
        throw;
    }
}

 

Posted one month ago
Sumeet says:
cmd.Parameters.AddWithValue("@syshashpwd", expectedHashString);
                            cmd.Parameters["@role"].Direction = ParameterDirection.Output;
                            cmd.Parameters["@status"].Direction = ParameterDirection.Output;

 You have not added the out parameters to the SqlParameterCollection.

First you have to add the parameters to the SqlParameterCollection and then set the parameter direction.

Change with below code.

cmd.Parameters.AddWithValue("@syshashpwd", expectedHashString);
cmd.Parameters.Add("@role", SqlDbType.Char, 1);
cmd.Parameters["@role"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@status", SqlDbType.Int);
cmd.Parameters["@status"].Direction = ParameterDirection.Output;

For more details refer below article.

How to return Output parameter from Stored Procedure in ASP.Net using C# and VB.Net