Database revert to original value after closing Windows Form

Question

I encountered a weird problem here. Whenever I run the update query, in the Windows Form I could see the updated value in the DataGridView. But after I stop the program, I check my Database through SQL Server, it doesn't show up the updated value.

try
{
    using (SqlConnection conn = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\SDM.mdf;Integrated Security=True;Connect Timeout=30"))
    {
        int updateValue = Int32.Parse(numericUpDown.Text);
        string productname = productnamecb.Text;
        string getvalue = "SELECT Product_UnitStock FROM Product WHERE Product_Name ='" + productname + "'";
        SqlCommand cmd = new SqlCommand(getvalue, conn);
        conn.Open();
        SqlDataReader rd = cmd.ExecuteReader();
        if (rd.HasRows)
        {
            rd.Read(); // read first row
            var oldvalue = rd.GetInt32(0);
            conn.Close();
            int total = oldvalue + updateValue;
            string updateData = "UPDATE Product SET Product_UnitStock = " + total + " WHERE Product_Name = '" + productname + "'";
            SqlCommand cmmd = new SqlCommand(updateData, conn);

            conn.Open();
            cmmd.ExecuteNonQuery();
            conn.Close();
        }
        else
        {
            MessageBox.Show("System Error", "System Error");
        }
    }
}
catch (SqlException ex)
{
    MessageBox.Show(ex.ToString());
}

This is the value seen in the Windows Form through DataGridView. As you can see for the 1st row, the Product Stock is "92".

But whenever I close the program, I check through the SQL Server it returns to the default value which is "80".

Sql Server

I think that you could find your answer here http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460 — Steve, Dec 10 '14 at 16:41
Also, *please* don't build values into your SQL like that. Use parameterized SQL instead, for many reasons, including avoiding SQL Injection attacks. — Jon Skeet, Dec 10 '14 at 16:44
"I could see the updated value in the DataGridView" - but can you see that updated value **in SQL** not in gridview while your program is running? Have you checked it? — Andrey Korneyev, Dec 10 '14 at 16:49
@JonSkeet its ok, it just a prototype for my school assignment. :) — Chuah Cheng Jun, Dec 10 '14 at 16:50
now I already identified the problem as stated by Steve. Thank you very much for the response! — Chuah Cheng Jun, Dec 10 '14 at 16:51
@ChuahChengJun: Even so, get into the right habit *straight away*. Even if it only ends up in a school assignment, if I were marking this code I would definitely mark it down for having a very common, widely understood, easily fixed security hole. (And one which makes the code harder to understand at the same time...) — Jon Skeet, Dec 10 '14 at 16:51

score 0 · Answer 1 · answered Dec 10 '14 at 17:08

From your code snippet, you never actually re-read the value out of the database again, so the form stays are the user last left it. You do write the data to the database, but my guess is that you auto-rollback the transaction (there is no transaction handling explicit in your code).

Database revert to original value after closing Windows Form

1 Answers1