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.


Show source
| c#   | sql-server   | database   | winforms   2016-12-04 19:12 1 Answers

Answers ( 1 )

  1. 2016-12-04 19:12

    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.

◀ Go back