I am having a problem with the sql update statement in c#. This click event tests between either "edit" or "new" mode and then uses the proper sql statement. The Insert statement works fine but the Update statement does not update. I created a break point and stepped thru and it does not throw any errors and looks like it executes but the database is not updated. I don't know if I am over thinking it and the obvious is right in front of me.

The code for the click event is below.

Thanks in advance for your help.

protected void btnCustomerUpdate_Click(object sender, EventArgs e)
    {
        string Mode = Request.QueryString["MODE"];

        if (Mode == "E")
        {
            int CustomerID = int.Parse(Request.QueryString["ID"]);

            //Declare the connection object
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyData"].ConnectionString);

            //Define you query
            //string sql = "UPDATE Customer SET FirstName=@FirstName Where CustomerID=@CustomerID";
            string sql = "UPDATE Customer SET FirstName=@FirstName, LastName=@LastName, Email=@Email, Password=@Password, Phone=@Phone, Fax=@Fax, Address1=@Address1, Address2=@Address2, City=@City, State=@State, Zip=@Zip WHERE CustomerID=@CustomerID";

            //Declare the Command
            SqlCommand cmd = new SqlCommand(sql, Conn);

            try
            {
                //Make the connection 
                Conn.Open();

                //Add the parameters needed for the SQL query
                cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
                cmd.Parameters.AddWithValue("@FirstName", txtFName.Text);
                cmd.Parameters.AddWithValue("@LastName", txtLName.Text);
                cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
                cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
                cmd.Parameters.AddWithValue("@Phone", txtPhone.Text);
                cmd.Parameters.AddWithValue("@Fax", txtFax.Text);
                cmd.Parameters.AddWithValue("@Address1", txtAddress.Text);
                cmd.Parameters.AddWithValue("@Address2", txtAddress2.Text);
                cmd.Parameters.AddWithValue("@City", txtCity.Text);
                cmd.Parameters.AddWithValue("@State", txtState.Text);
                cmd.Parameters.AddWithValue("@Zip", txtZipCode.Text);

                //Execute the query
                int NumRows = 0;
                NumRows = cmd.ExecuteNonQuery();

                Conn.Close();

            }
            catch (Exception ex)
            {
                lblConnResults.Text = ex.Message;
            }

            //lblConnResults.Text = "Updated " + NumRows.ToString() + " record";

            Response.Redirect("~/Customers.aspx");

        }
        else if (Mode == "N")
        {
            //Declare the connection object
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyData"].ConnectionString);

            //Define you query
            string sql = "INSERT INTO Customer(FirstName, LastName, Email, Password, Phone, Fax, Address1, Address2, City, State, Zip) VALUES(@FirstName, @LastName, @Email, @Password, @Phone, @Fax, @Address1, @Address2, @City, @State, @Zip)";

            //Declare the Command
            SqlCommand cmd = new SqlCommand(sql, Conn);

            try
            {
                //Make the connection 
                Conn.Open();

                //Add the parameters needed for the SQL query
                cmd.Parameters.AddWithValue("@FirstName", txtFName.Text);
                cmd.Parameters.AddWithValue("@LastName", txtLName.Text);
                cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
                cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
                cmd.Parameters.AddWithValue("@Phone", txtPhone.Text);
                cmd.Parameters.AddWithValue("@Fax", txtFax.Text);
                cmd.Parameters.AddWithValue("@Address1", txtAddress.Text);
                cmd.Parameters.AddWithValue("@Address2", txtAddress2.Text);
                cmd.Parameters.AddWithValue("@City", txtCity.Text);
                cmd.Parameters.AddWithValue("@State", txtState.Text);
                cmd.Parameters.AddWithValue("@Zip", txtZipCode.Text);

                //Execute the query
                int NumRows = 0;
                NumRows = cmd.ExecuteNonQuery();

                Conn.Close();
            }
            catch (Exception ex)
            {
                lblConnResults.Text = ex.Message;
            }

            //lblConnResults.Text = "Inserted " + NumRows.ToString() + " record";

            Response.Redirect("~/Customers.aspx");

        }

    }

Hi,

The give code is working fine in both the places (insert & update). Could you check the values that you are passing like Insert and Update is having different values for same customer_id?

Thank you.

Yes i guess the problem is in where clause of your update code. Check that query string returned customer id exist in your database.

Yes i guess the problem is in where clause of your update code. Check that query string returned customer id exist in your database.

Yes the query string returns the Customer ID. I tried the shorter version of the Update statement like this, "UPDATE Customer SET FirstName='John' Where CustomerID=@CustomerID", to check and this worked fine.

Then I tried "UPDATE Customer SET FirstName=@FirstName Where CustomerID = 1203", this did not work.

I just don't see where it is going wrong. Thank you for your help.

Post the CREATE TABLE SQL for you Customer table. Is the CustomerId an integral field?

String qUpdate = "Update HPMLPORTAL.SECURITY set USERS = '" + USERS + "' , ALLOWED = '" + ALLOWED + "' WHERE page = 'EMP-GATEPASS'"; m using this statement for update but its updating and changing the whole record in db and cs any solution ??? :(

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.