C# How to update an access table

Question

How should the UPDATE statement with multiple columns, that have to be updated? (Because a row has changed a lot of it in it)

I tried some search on this board, but in the other questions were sometimes "[ ]" around the columns, sometimes not, but both versions don´t work in my code.

Here´s the code:

            try 
            {
                string name = mtb_Name.Text.ToString();
                string altname = mtb_AltName.Text.ToString();
                string licht = cb_Licht.SelectedItem.ToString();
                string boden = cb_Boden.SelectedItem.ToString();
                string haerte = cb_Haerte.SelectedItem.ToString();
                string pg = cb_PG.SelectedItem.ToString();
                string hoehe = mtb_Hoehe.Text.ToString();
                string form = cb_Form.SelectedItem.ToString();
                string zuechter = cb_Zuechter.SelectedItem.ToString();
                string land = cb_Land.SelectedItem.ToString();
                string gruppe = cb_Gruppe.SelectedItem.ToString();

//the connection works, I can add stuff in other tables and delete stuff everywhere

                parent.GetDBConnection().Open();
                OleDbCommand Query = new OleDbCommand();
                Query.Connection = parent.GetDBConnection();
                Query.Parameters.Clear();

//I build this string at the moment for testing purposes only and 
//converted everything and putted in a string to be sure.
//later it will be replaced with the Parameters.Add(,)
//I also did test it with [AltNameRose] as columname instead of AltNameRose
//or wrote instead of the ' ' a "\"" around the strings, 
//but doesn´t seem to be the problem.

                Query.CommandText = "UPDATE tb_Rose SET" +
                    " ,AltNameRose = '" + altname +
                    "' ,NameZuechter = '" + haerte +
                    "' ,Boden = '" + boden +
                    "' ,Wuchshoehe = '" + hoehe +
                    "' ,Farbe = '" + " " +
                    "' ,Foto = '" + " " +
                    "' ,Licht = '" + licht +
                    "' ,Preisgruppe = '" + pg +
                    "' ,Gruppe = '" + gruppe +
                    " WHERE NameRose = '" + name + "'";

//the CommandText seems to be missing something, but I don´t know what.

                MessageBox.Show(Query.CommandText.ToString());
                Query.ExecuteNonQuery();
                parent.GetDBConnection().Close();
                MessageBox.Show("Rose successfully edited.");
                this.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                parent.GetDBConnection().Close();
            }

The thrown exception is

"Syntaxerror in UPDATE-Command

The table tb_Rose looks like this: NameRose|AltNameRose|NameZuechter|Frosthaerte|Boden|Wuchshoehe|Farbe|Foto||Licht|Preisgruppe|Gruppe

On another question stood, that the order, how I pass the columns is important too, but it doesn´t work out for me.

Has anyone a small hint where the mistake is?

Answer 1

The syntax of Update statement is

Update table_name SET
                  column_name = value,
                  column_name = value,
                  ....
                  Where condition

In your query You put a , after the SET keyword.

there is a , after the SET keyword.. try below code

Query.CommandText = "UPDATE tb_Rose SET" +
                    " AltNameRose = '" + altname +
                    "' ,NameZuechter = '" + haerte +
                    "' ,Boden = '" + boden +
                    "' ,Wuchshoehe = '" + hoehe +
                    "' ,Farbe = '" + " " +
                    "' ,Foto = '" + " " +
                    "' ,Licht = '" + licht +
                    "' ,Preisgruppe = '" + pg +
                    "' ,Gruppe = '" + gruppe +
                    " WHERE NameRose = '" + name + "'";

Answer 2

You don't need comma just before your first column. That's why your;

" ,AltNameRose = '"

should be

" AltNameRose = '"

But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Also use using statement to dispose your connections and commands automatically instead of calling .Close() methods manually.