Update customer records

Question

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

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.

Hadeel e · Answer

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 Beg · Answer

Hey. Did you tried what Jignesh mentioned code?

Hadeel e · Answer

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 Hawaldar · Answer

Before execute query you should do null check for parameters with if condition, To pass parameters use cmd.Parameters.AddWithValue

Salman Beg · Answer

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 Kumar · Answer

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