How to Update a table

Question

I am trying to update my database table ExpenseManagement. But it is not Updated.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;


public partial class UserProfile : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {

            txtUserId.Text = Request.Cookies["txtUserName"].Value;

            string con_string = @"data Source= 10.10.10.5; initial catalog= test; user= xx; password= xxxxxxxxx;";

            SqlConnection con = new SqlConnection(con_string);

            SqlCommand cmd = new SqlCommand("select FirstName, LastName, Password, EmailId, MobileNumber from ExpenseManagement where UserId ='"+txtUserId.Text+"'", con);

            cmd.Parameters.AddWithValue("@UserId", txtUserId.Text);

            con.Open();

            DataTable dt = new DataTable();

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(dt);

            txtFirstName.Text = dt.Rows[0]["FirstName"].ToString();

            txtLastName.Text = dt.Rows[0]["LastName"].ToString();

            txtPassword.Text= dt.Rows[0]["Password"].ToString();

            txtEmailId.Text = dt.Rows[0]["EmailId"].ToString();

            txtMobileNumber.Text = dt.Rows[0]["MobileNumber"].ToString();

            con.Close();

            txtUserId.Enabled = false;

            txtFirstName.Enabled=false;

            txtLastName.Enabled=false;

            txtPassword.Enabled = false;

            txtEmailId.Enabled = false;

            txtMobileNumber.Enabled = false;

            btnUpdate.Visible = false;
    }

    protected void Button1_Click1(object sender, EventArgs e)
    {

        txtUserId.Enabled = true;

        txtUserId.ReadOnly = true;

        txtFirstName.Enabled = true;

        txtLastName.Enabled = true;

        txtPassword.Enabled = true;

        txtMobileNumber.Enabled = true;

        txtEmailId.Enabled = true;

        btnUpdate.Visible = true;

        btnEdit.Visible = false;
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {

        string con_string = @"data Source= 10.10.10.5; initial catalog= test; user= xx; password= xxxxxxxxx;";

        SqlConnection con = new SqlConnection(con_string);

        string qryUpdate = "Update ExpenseManagement set FirstName= @FirstName, LastName=@LastName, Password=@Password, EmailId=@EmailId,MobileNumber=@MobileNumber where UserId= @UserId";

        SqlCommand cmd = new SqlCommand(qryUpdate, con);

        cmd.Parameters.AddWithValue("@UserId", txtUserId.Text);

        cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);

        cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);

        cmd.Parameters.AddWithValue("@Password", txtPassword.Text);

        cmd.Parameters.AddWithValue("@EmailId", txtEmailId.Text);

        cmd.Parameters.AddWithValue("@MobileNumber", txtMobileNumber.Text);

        con.Open();

        if (Page.IsValid)
        {

            cmd.ExecuteNonQuery();

            btnEdit.Visible = true;
        }

        con.Close();   
    }
}

I have next database fields:

UserId, FirstName, LastName, Password, EmailId, MobileNumber.

Answer 1

Missing the Page.IsPostBack check on the Page_Load event.

In ASP.NET, when you raise an event on a server side control, the Page_Load event is always executed before the code in the control event.

In your case, your user changes the textboxes, then presses the Update button. This raises the Page_Load event followed by the btnUpdate_Click event. Without a check on the property IsPostBack, the Page_Load event reloads the textboxes from the database with the original values effectively destroying the data typed by the user, then the button event code is called. But at this point the values in the textboxes are the original ones, so your code runs correctly, but doesn't change anything.

Change the Page_Load event adding

protected void Page_Load(object sender, EventArgs e)
{
      if(!IsPostBack)
      {
        txtUserId.Text = Request.Cookies["txtUserName"].Value;
        string con_string = @"data Source= 10.10.10.5; initial catalog= test; user= xx; password= xxxxxxxxx;";
        SqlConnection con = new SqlConnection(con_string);
        SqlCommand cmd = new SqlCommand(@"select FirstName, LastName, Password, 
                                          EmailId, MobileNumber 
                                          from ExpenseManagement 
                                          where UserId =@usedId", con);
        cmd.Parameters.AddWithValue("@UserId", txtUserId.Text);
        con.Open();
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        ......
        btnUpdate.Visible = false;
   }
}

Answer 2

I could recommend for the command to be more readable to user @ instead of concatenating strings with "+" sign as below:

protected void btnUpdate_Click(object sender, EventArgs e)
        {
            string con_string = @"data Source= 10.10.10.5;initial catalog= test; user= xx; password= xxxxxxxxx;";
            SqlConnection con = new SqlConnection(con_string);
            string qryUpdate = @"Update ExpenseManagement 
                                 set FirstName= @FirstName, 
                                    LastName=@LastName, 
                                    Password=@Password,
                                    EmailId=@EmailId,
                                    MobileNumber=@MobileNumber 
                                    where UserId= @UserId";
            SqlCommand cmd = new SqlCommand(qryUpdate, con);
            cmd.Parameters.AddWithValue("@UserId", Convert.ToInt32(txtUserId.Text));
            cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
            cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
            cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
            cmd.Parameters.AddWithValue("@EmailId", txtEmailId.Text);
            cmd.Parameters.AddWithValue("@MobileNumber", txtMobileNumber.Text);
            con.Open();

            if (Page.IsValid)
            {
                cmd.ExecuteNonQuery();
                btnEdit.Visible = true;
            }
            con.Close();
        }

Also I agree with RezaRahmati to convert the userId and other parameters to correct types you have defined in your database, table columns.

Answer 3

I think the problem is using cmd.Parameters.AddWithValue("@UserId", txtUserId.Text); because it add a parameter of type string (because txtUserId.Text is string) instead of int or long, so change it to cmd.Parameters.AddWithValue("@UserId", int.parse(txtUserId.Text)); or use cmd.Parameters.Add() which takes type as argument.