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;
}
}