How to update a value in local SQL Server database

Question

In my program I want the user to have the option to change the value (price in my example) in a local database, that I created.

I'm having a problem with it. The connection works fine and it debugs well and even shows the message that "saving ok", but it doesn't change at all in the database.

private void button2_Click(object sender, EventArgs e)
{            
    var con = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\sam\Desktop\hello\hello\DB.mdf;Integrated Security=True";

    using (SqlConnection myconnection = new SqlConnection(con))
    {
        try
        {
            myconnection.Open();     
            var query = string.Format("update DBTable set price='"+textBox2.Text+"' where ParamToCheck='"+comboBox5.Text+"'");
            SqlCommand cm = new SqlCommand(query, myconnection);

            cm.ExecuteNonQuery();

            MessageBox.Show("saved ok !!");     
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

The user can choose a string from the combobox5 and change his price from textBox2 by entering number - that what I want - but it doesn't change.

Are your table / column names really `DBTable` and `ParamToCheck`? Additionally: if price is not a varchar/nvarchar, there is no point in wrapping your value in quotes. — Marco, Dec 04 '16 at 18:46
The ExecuteNonQuery returns the number of rows updated by the query. Your message box "saved ok" doesn't tell you the truth. Check with _int rowsUpdated = cm.ExecuteNonQuery();_ and then look at the value of rowsUpdated. — Steve, Dec 04 '16 at 18:46
Further to what @ChrisF said, copy and paste the value of your query variable into SSMS and run it. It could do nothing and still not return an error. — bbrumm, Dec 04 '16 at 18:47

score 2 · Accepted Answer · edited May 23 '17 at 12:01

The correct way to write SQL queries is through the use of parameters and with the correct datatype for each column. Concatenating strings as you do now is a secure recipe to fall into many types of errors. The simple one is a failure in correctly understanding your values by the database engine. The worst one is called SQL Injection that could destroy your entire database.

var query = "update DBTable set price=@price where ParamToCheck=@prm";
using (SqlConnection myconnection = new SqlConnection(con))
using (SqlCommand cm = new SqlCommand(query, myconnection))
{
    try
    {

        myconnection.Open();     
        cm.Parameters.Add("@price", SqlDbType.Decimal).Value = Convert.ToDecimal(textBox2.Text);
        cm.Parameters.Add("@prm", SqlDbType.NVarChar).Value = comboBox5.Text;
        int rowsUpdated = cm.ExecuteNonQuery();
        if(rowsUpdated > 1)
             MessageBox.Show("saved ok !!");     
        else
             MessageBox.Show("No match for condition:" + comboBox5.Text);     
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Notice that I have simplified a lot your query text without using a concatenation of strings, instead there are two parameters placeholders that will be used by the database engine to complete your command. After that I have added two parameters of specific data type (decimal and nvarchar). These types should match your column's datatype for Price and ParamToCheck.

How to update a value in local SQL Server database

1 Answers1