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”.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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"];
---
}
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0