Works perfectly except changes are not saved to the SQL database

Question

Changes are not saved to the SQL database

Why would I want to use '@' in the sql statement instead of the way that I have the statement?

Code:

    private void button_Save_Customer_Click(object sender, EventArgs e)
    {
        sqlString = Properties.Settings.Default.ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(sqlString);

        try
        {
            string customer_Ship_ID = customer_Ship_IDTextBox.ToString();
            string customer_Ship_Address = customer_Ship_AddressTextBox.Text;
            SQL = "UPDATE Customer_Ship SET Customer_Ship_Address = customer_Ship_Address WHERE Customer_Ship_ID = customer_Ship_ID";
            SqlCommand sqlCommand = new SqlCommand(SQL, sqlConnection);
            sqlCommand.Parameters.AddWithValue("Customer_Ship_ID", customer_Ship_ID);
            sqlCommand.Parameters.AddWithValue("Customer_Ship_Address", customer_Ship_Address);
            sqlCommand.CommandText = SQL;

            sqlConnection.Open();
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();

            MessageBox.Show("Record Updated");
        }
        catch (Exception err)
        {
            MessageBox.Show(err.Message);
        }

See [the fabulous MSDN docs on the `UPDATE` statement](http://msdn.microsoft.com/en-us//library/ms177523.aspx) to get your syntax right ... — marc_s, Oct 12 '14 at 09:02

blfuentes · Accepted Answer · 2014-10-13T06:50:49.973

1

Here you can check the MSDN reference for the update command.

Use parameters, Why?

Also check that you need to open and close the connection object, not the command.

In case you want to update the rows with the Customer_ID = "something" you could do like this:

The code (updated after your changes):

private void button_Save_Customer_Click(object sender, EventArgs e)
{
    string sqlString = Properties.Settings.Default.ConnectionString;
    SqlConnection sqlConnection = new SqlConnection(sqlString);
    try
    {
        int customer_Ship_ID;
        if(int.TryParse(customer_Ship_IDTextBox.Text, out customer_Ship_ID))
        {
            string customer_Ship_Address = customer_Ship_AddressTextBox.Text;
            // Customer_Ship: Database's table
            // Customer_Ship_Address, Customer_Ship_ID: fields of your table in database
            // @Customer_Ship_Address, @Customer_Ship_ID: parameters of the sqlcommand
            // customer_Ship_ID, customer_Ship_Address: values of the parameters
            string SQL = "UPDATE Customer_Ship SET Customer_Ship_Address = @Customer_Ship_Address WHERE Customer_Ship_ID = @Customer_Ship_ID";
            SqlCommand sqlCommand = new SqlCommand(SQL, sqlConnection);
            sqlCommand.Parameters.AddWithValue("Customer_Ship_ID", customer_Ship_ID);
            sqlCommand.Parameters.AddWithValue("Customer_Ship_Address", customer_Ship_Address);
            sqlCommand.CommandText = SQL;

            sqlConnection.Open();
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();

            MessageBox.Show("Record Updated");
        }
        else
        {
            // The id of the textbox is not an integer...
        }
    }
    catch (Exception err)
    {
        MessageBox.Show(err.Message);
    }
}

edited Oct 13 '14 at 06:50

answered Oct 12 '14 at 09:32

blfuentes

2,731
5
44
72

thanks for the help. I have changed my original code above showing what I have now. I don't get any error messages but the changes are not made to the database. also, why use '@' for the sql statement instead of the way that I have it originally? – Dave Hampel Oct 12 '14 at 21:04
For the @: http://stackoverflow.com/questions/556133/whats-the-in-front-of-a-string-in-c does your table contain a customer_id with same value of the where in the update statement? – blfuentes Oct 12 '14 at 21:14
Check again your code with my answer. Your are missing the '@' for the parameters. The @ tells what should be replaced with the value: @"UPDATE Customer_Ship SET Customer_Ship_Address = @Customer_Ship_AddressTextBox WHERE Customer_ID = @Customer_IDTextBox"; Also uncomment the parameter for the customer_id and change the open to use the sqlConnection.Open() instead of the command – blfuentes Oct 12 '14 at 21:23
I updated my answer. Take a look and verify you have the same code. – blfuentes Oct 12 '14 at 21:27
customer_id question is yes, the datatable has Customer_ID and the text box on the form is named customer_IDTextBox. I should probably be using Customer_Ship_ID because its the key field. the Customer_ID in the Customer_Ship datatable is to connect to the customer name in Customer datatable. eventually I will have a one to many due to multiple locations. checking the other code now. thanks very much for the help – Dave Hampel Oct 12 '14 at 22:31
ok, did everything you wanted me to do including the uncomment the parameter for the customer_id. I am not tying to change the customer_id just the Customer_Ship_Address. now I get an error 'Must declare the scalar variable"customer_Ship_AddressTextBox. – Dave Hampel Oct 12 '14 at 22:51
Check the SQL string carefully... The names with the @ at the beginning should appear with the same name in the parameters.addwithvalue – blfuentes Oct 13 '14 at 00:49
I get an error, cant convert cusomter_Ship_ID to int. before this error it appeared to work properly but the change to Customer_Ship_Address would not save to the sql datatable – Dave Hampel Oct 13 '14 at 05:11
Ok, try now. The code will try to parse the textbox with the id to an integer. Make sure you write a valid Id. – blfuentes Oct 13 '14 at 06:29
Thanks very much, i have been battling this for 3 months(i am a complete newby at C# and appreciate the help. I didn't use the complete IF statement i just used "int.TryParse(customer_Ship_IDTextBox.Text, out customer_Ship_ID)". now i just need to add the rest of the columns to the SQL statement and the AddWithValue for each column. – Dave Hampel Oct 14 '14 at 00:53

score 0 · Answer 2 · answered Oct 12 '14 at 06:41

Seems like your syntax isn't correct. Here's the syntax for the Update:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

So, Update, what to set, and WHERE to set (which you seem to be missing).

For more, have a look here.

score -3 · Answer 3 · answered Oct 12 '14 at 06:39

-3

Check your update query

Change it like

string SQL = string.format("UPDATE Customer_Ship SET Customer_Ship_Address='{0}'",putUrVaue);

answered Oct 12 '14 at 06:39

Binson Eldhose

749
1
6
14

Works perfectly except changes are not saved to the SQL database

3 Answers3