更新客户记录

Question

在我的应用程序中，我有一个更新操作来更新后端的客户记录。此外，据我所知，当一个字段没有更改时，它应该保存任何更新或非(null(。然而，当我尝试单击保存时，我得到了一个sql错误，即没有提供"MobileNumber"。

这是我的数据层类：

#region Update rider's info
public bool updateRiders(RidersModel rider)
{

query = "UPDATE Users SET firstName = @firstName," +
" lastName = @lastName, email = @email, Address = @Address," +
" MobileNumber = @MobileNumber, city = @city," +
"state = @state, zipCode = @zipCode, UserName =@UserName, users_Id = @users_Id  " +
"WHERE usersId = @ID;";
conn = new SqlConnection(connectionString);
cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = rider.usersId;
cmd.Parameters.Add("@firstName", SqlDbType.VarChar, 255).Value = rider.firstName;
cmd.Parameters.Add("@lastName", SqlDbType.VarChar, 255).Value = rider.lastName;
cmd.Parameters.Add("@email", SqlDbType.VarChar, 255).Value = rider.email;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 255).Value = rider.Address;
cmd.Parameters.Add("@city", SqlDbType.VarChar, 255).Value = rider.city;
cmd.Parameters.Add("@state", SqlDbType.VarChar, 255).Value = rider.state;
cmd.Parameters.Add("@zipCode", SqlDbType.Int).Value = rider.zipCode;
cmd.Parameters.Add("@MobileNumber", SqlDbType.VarChar, 200).Value = rider.mobileNumber;
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = rider.UserName;
cmd.Parameters.Add("@users_Id", SqlDbType.NVarChar, 128).Value = rider.userID;

try
{
conn.Open();
rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
success = true;
}
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Close();
}
return success;
}

我的行动如下：

[HttpGet]
public async Task<ActionResult> EditRiders(int id)
{
var userStore = new UserStore<IdentityUser>();
var userManager = new UserManager<IdentityUser>(new UserStore<IdentityUser>(new RidesDbContext()));
IdentityUser theUser = new IdentityUser();
theUser = await userManager.FindByIdAsync(User.Identity.GetUserId());
RidersModel rider = new RidersModel();
RidersTier tier = new RidersTier();
rider.email = theUser.UserName;
rider.email = theUser.Email;
rider.mobileNumber = theUser.PhoneNumber;
rider.Password = theUser.PasswordHash;
rider.userID = theUser.Id;

rider = tier.getRidersById(id);

return View(rider);
}

[HttpPost]
public async Task<ActionResult> EditRiders(RidersModel rider)
{
var userStore = new UserStore<IdentityUser>();
var userManager = new UserManager<IdentityUser>(new UserStore<IdentityUser>(new RidesDbContext()));
IdentityUser theUser = new IdentityUser();

if (ModelState.IsValid)
{
theUser = await userManager.FindByIdAsync(User.Identity.GetUserId());
rider.email = theUser.UserName;
rider.email = theUser.Email;
rider.mobileNumber = theUser.PhoneNumber;
rider.Password = theUser.PasswordHash;
rider.userID = theUser.Id;
RidersTier tier = new RidersTier();
tier.updateRiders(rider);
}

TempData["ad"] = "User info has been updated";
return View(rider);
}

这有什么原因吗？

我将感谢你的帮助。非常感谢。

Answer 1

我认为您必须按照参数变量在sql语句中的出现顺序提供它们。我认为@ID很好，因为它在where子句中，但我不确定。尝试一下，如果不起作用，则将@ID参数放在最后：

query = @"
UPDATE Users 
SET 
firstName = @firstName,
lastName = @lastName, 
email = @email, 
Address = @Address,
MobileNumber = @MobileNumber, 
city = @city,
state = @state, 
zipCode = @zipCode, 
UserName = @UserName, 
users_Id = @users_Id  
WHERE usersId = @ID;
";
conn = new SqlConnection(connectionString);
cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = rider.usersId;
cmd.Parameters.Add("@firstName", SqlDbType.VarChar, 255).Value = rider.firstName;
cmd.Parameters.Add("@lastName", SqlDbType.VarChar, 255).Value = rider.lastName;
cmd.Parameters.Add("@email", SqlDbType.VarChar, 255).Value = rider.email;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 255).Value = rider.Address;
cmd.Parameters.Add("@MobileNumber", SqlDbType.VarChar, 200).Value = rider.mobileNumber;
cmd.Parameters.Add("@city", SqlDbType.VarChar, 255).Value = rider.city;
cmd.Parameters.Add("@state", SqlDbType.VarChar, 255).Value = rider.state;
cmd.Parameters.Add("@zipCode", SqlDbType.Int).Value = rider.zipCode;
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = rider.UserName;
cmd.Parameters.Add("@users_Id", SqlDbType.NVarChar, 128).Value = rider.userID;