ASP NET MVC Error construction INSERT with FOREIGN KEY

Question

I have a problem with getting identity primary key from a logged user with using sql queries without Enity Framework. So when i put login and password of user from my database UserDataController copy UserEmail (login) to WorkSpaceController and put it in public ActionResult UploadFile(string useremail) in useremail data. So I can use it in query in WorkSpaceController to get an ID with this email. But the problem is I can't use UserID that i got as a parameter for insert query. Look at the code below

UserDataController:

public class UserDataController : Controller
{
    string constr = ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
    // GET: UserData
    [HttpGet]
    public ActionResult Login()
    {
        return View();
    }
}
    [HttpPost]
    public ActionResult Login(UserDataClass log) //Авторизация
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "Data Source=DESKTOP-LRLFA5K\\SQLEXPRESS;Initial Catalog=FileCloud;Integrated Security=True;MultipleActiveResultSets=True";
        SqlDataReader dr;
        con.Open();
        SqlCommand com = new SqlCommand("select * from UserData where UserEmail ='" + log.UserEmail + "' and UserPassword ='" + log.UserPassword + "'", con);
        dr = com.ExecuteReader();
        if (dr.Read())
        {
            Session["useremail"] = log.UserEmail.ToString();
            return RedirectToAction("UploadFile", "WorkSpace", new { useremail = log.UserEmail.ToString()});
        }
        else
        {
            ViewData["Message"] = "Неправильное имя пользователя или пароль";
        }
        con.Close();
        return View();
    }
}

WorkSpaceController:

public class WorkSpaceController : Controller
{
    int currentid;
    string constr = ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
    WorkSpaceClass ws = new WorkSpaceClass();
    List<WorkSpaceClass> _ws = new List<WorkSpaceClass>();
    // GET: WorkSpace
    public ActionResult Add()
    {
        return View();
    }
    public ActionResult UploadFile(string useremail)
    {
        UserDataClass info = new UserDataClass();
        info.UserEmail = useremail;
        SqlConnection sqlcon = new SqlConnection(constr);
        string comman = "select UserID from UserData where UserEmail = '" + info.UserEmail + "'"; //??? Вероятно, Email пуст...
        using (SqlCommand sqlcom = new SqlCommand(comman, sqlcon))
        {
            sqlcon.Open();
            currentid = Convert.ToInt32(sqlcom.ExecuteScalar());
        }
        //ws.UserID = id; //UserID не читается
        ViewBag.UserID = currentid;
        sqlcon.Close();
        return View();
    }
    [HttpPost]
    public ActionResult UploadFile(HttpPostedFileBase doc, string useremail) //Загружаем текстовый файл в БД
    {
        ViewBag.UserEmail = useremail;
        if (doc != null)
        {
            ws.FileName = Path.GetFileName(doc.FileName);
            ws.FileData = new byte[doc.ContentLength];
            doc.InputStream.Read(ws.FileData, 0, doc.ContentLength);
            ws.FileExtension = Path.GetExtension(ws.FileName);
            DateTime FileDate = DateTime.Now;

            SqlConnection sqlcon = new SqlConnection(constr);

            ws.FileDate = FileDate.ToString("dd/MM/yyyy");
            if (ws.FileExtension == ".doc" || ws.FileExtension == ".docx" || ws.FileExtension == ".txt" || ws.FileExtension == ".pdf")
            {
                string FilePath = Path.Combine(Server.MapPath("~/FileData"), ws.FileName); //Указываем дирректорию хранения файла
                doc.SaveAs(FilePath); 
                string command = "insert into FileData(FileName, FileData, FileExtension, FileDate, UserID) values(@FileName, @FileData, @FileExtension, @FileDate, @UserID)";
                sqlcon.Open();
                SqlCommand sqlcom = new SqlCommand(command, sqlcon);
                sqlcom.Parameters.Add("@FileName", SqlDbType.VarChar).Value = ws.FileName;
                sqlcom.Parameters.Add("@FileData", SqlDbType.VarBinary).Value = ws.FileData;
                sqlcom.Parameters.Add("@FileExtension", SqlDbType.VarChar).Value = ws.FileExtension;
                sqlcom.Parameters.Add("@FileDate", SqlDbType.VarChar).Value = ws.FileDate;
                sqlcom.Parameters.Add("@UserID", SqlDbType.Int).Value = currentid; //AN ERROR IS HERE
                sqlcom.ExecuteNonQuery(); //BUT THIS ROW IS DISPLAY ON ERROR LOG AND COLORED AS RED
                sqlcon.Close();
            }
        }
        return View(ws);
    }

WorkSpaceClass:

public class WorkSpaceClass
{
    public string FileName { get; set; }
    public byte[] FileData { get; set; }
    public string FileExtension { get; set; }
    public string FileDate { get; set; }
    public int UserID { get; set; }
}

WorkSpaceClass:

public class UserDataClass
{
    public int UserID { get; set; }
    [Display(Name = "Имя")]
    [Required(ErrorMessage = "Введите имя пользователя")]
    public string UserName { get; set; }
    [Display(Name = "Email")]
    [Required(ErrorMessage = "Введите Email")]
    public string UserEmail { get; set; }
    [Display(Name = "Пароль")]
    [Required(ErrorMessage = "Введите пароль")]
    public string UserPassword { get; set; }
    [Display(Name = "Подтверждение пароля")]
    [Required(ErrorMessage = "Подтвердите пароль")]
    public string ConfirmPassword { get; set; }
    [Display(Name = "Выберите изображение")]
    public byte[] AvatarData { get; set; }
}

This is columns types from SQL Server

So if I put any int number like 1 or 2 in a row (sqlcom.Parameters.Add("@UserID", SqlDbType.Int).Value = currentid;) on "currentid" place file will be uploaded to a current user with this id. I think the problem is the row "currentid = Convert.ToInt32(sqlcom.ExecuteScalar());" but I can't imagine how to resolve it. Hope u help guys, I stuck with it for a 2 days already. I'm a new in MVC ASP.NET so this error can be a typically for you... With ViewBag i tested values that i got and it was the right id int value from current email that on "useremail".

Answer 1

I haven't tested your code but the error explains itself. This code is inserting FileData for a UserID that doesn't exists in table UserData; and the reason for that it's the value for currentid getting lost.

When you reach UploadFile(HttpPostedFileBase doc, string useremail) you're in a new request so ViewData and ViewBag won't help and currentid got lost. You need either TempData, Session or a new parameter in this controller action method to retrieve value for UserId. If you have previously saved the value in TempData, try this:

if(TempData["UserId"] != null)
{
  ---
  ---
  sqlcom.Parameters.Add("@UserID", SqlDbType.Int).Value = (int)TempData["UserId"];
  ---
}