CODEWITHSUNDEEP
c#sql-serverado.net
Katp00p
Katp00p

Reputation: 93

C# executenonquery does not update database

The following code works, i get the messagebox saying the product has been updated, however no changes are made in the database.

This was copied and pasted from another method that works in the same database, and that method works fine.

    private void btnProductSave_Click(object sender, EventArgs e)
    {
        if (txtProductName.Text != "")
        {
            var confirmResult = MessageBox.Show("Are you sure you want to save changes to this product?", "Confirm Save", MessageBoxButtons.YesNo);
            if (confirmResult == DialogResult.Yes)
            {
                SqlConnection con = null;
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["PVAdmin.Properties.Settings.PricerConnectionString"].ConnectionString);
                string sql2 = "UPDATE Products SET productName = @productName WHERE productID = @productID";

                SqlCommand myCommand2 = new SqlCommand(sql2, con);
                myCommand2.Parameters.AddWithValue("@productName", txtProductName.Text);
                myCommand2.Parameters.AddWithValue("@productID", lblProductID.Text);

                con.Open();
                myCommand2.ExecuteNonQuery();
                con.Close();

                MessageBox.Show("Product saved.");
                this.LoadProduct((int)cboProductSelect.SelectedValue);
            }
            else
            {
                MessageBox.Show("No changes were saved.");
            }
        }
    }

Upvotes: 0

Views: 1985

Answers (1)

DRapp
DRapp

Reputation: 48139

I have seen this before and what it ends up being is the sql engine appears to update itself with itself. Since your @parm is the same as the column, sql is saying update me with me.

To fix, try SLIGHTLY renaming your parameters such as 

UPDATE Products SET productName = @pProductName WHERE productID = @pPproductID";
myCommand2.Parameters.AddWithValue("@pProductName", txtProductName.Text);
myCommand2.Parameters.AddWithValue("@pProductID", lblProductID.Text);

this way, SQL does not have a column "pProductName" and will in-fact go to the parameter... likewise with "pProductID". Very subtle, but is probably exactly what you are encountering. Also, which database are you connecting to. Different engines use different token character for parameter passing... "?", "@", ":" as just a few I know of...

Upvotes: 2

Related Questions