Update customer records

Hi,

In my app I have an update action to update the customers records in the back-end. Further, as I know it's supposed to save any updates or non (null) when one field wasn't changed. However, when I try to click save I'm getting a sql error, that the "MobileNumber" was not supplied.

Here is my data tier class:

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

my Action is as the following :

[HttpGet]
public async Task EditRiders(int id)
{
var userStore = new UserStore();
var userManager = new UserManager(new UserStore(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 EditRiders(RidersModel rider)
{
var userStore = new UserStore();
var userManager = new UserManager(new UserStore(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);
}

Is there any reason for that?

I tried to provide the parameter variables in the order that they appear in the query statement, but that didn't help.

I would appreciate your help. Thank you.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hadeel e6y ago
yes, I did. It didn't work.
However, I tried to check with an if statment to allow null values for the mobile number and it works.
However, it turned out that I'm changing my values as an admin insted of changing the user info.
(I'm logged in with an admin role). And know I couldn't access the available customers view page (the tables).
Salman Beg6y ago
Hey. Did you tried what Jignesh mentioned code?
Hadeel e6y ago
this is an Update method isn't supposed to accept null value or values that didn't change?

Besides there is a value for the mobile number in my tables, but for some reason it's not supplied.

Thank You.
Rajanikant Hawaldar6y ago
Before execute query you should do null check for parameters with if condition, To pass parameters use cmd.Parameters.AddWithValue
Salman Beg6y ago
Hey. You supplied the parameter but maybe it's null or empty. Put a breakpoint and check whether you are getting value or not. Particular check this value, rider.mobileNumber . Thanks.
Jignesh Kumar6y ago
Hi,

Please try this one instead of cmd.parameter.Add(),
cmd.Parameters.AddWithValue("@ID", rider.usersId);
cmd.Parameters.AddWithValue("@firstName",rider.firstName);
cmd.Parameters.AddWithValue("@lastName", rider.lastName);
cmd.Parameters.AddWithValue("@email", rider.email);
cmd.Parameters.AddWithValue("@Address", rider.Address);
cmd.Parameters.AddWithValue("@city",  rider.city);
cmd.Parameters.AddWithValue("@state", rider.state);
cmd.Parameters.AddWithValue("@zipCode", rider.zipCode);
cmd.Parameters.AddWithValue("@MobileNumber",rider.mobileNumber);
cmd.Parameters.AddWithValue("@UserName", rider.UserName);
cmd.Parameters.AddWithValue("@users_Id", rider.userID);