CODEWITHSUNDEEP
c#sqlwpfinsert
Mr.T
Mr.T

Reputation: 1

WPF C# insert to mysql AddWithValue

I have an insert statement 

 command.CommandText = "  insert into system (ziel, comment, status,art) values (@eort,@comment,@statebox,@Eart) ";

Behind statebox is a Combobox. Every word which can be chosen in the Combobox is created as a tablerow in my database.

the values are created here:

         command.Parameters.AddWithValue("@eort",textBo3x.Text);
         command.Parameters.AddWithValue("@comment", textBox_Copy1.Text);
         command.Parameters.AddWithValue("@statebox", MyComboBox.Text);
         command.Parameters.AddWithValue("@Eart", MyComboBox_Copy1.Text);
         command.Parameters.AddWithValue("@thetime", thetime_insert.Text);

This works.

But I want to use the @values in the insert Statement like this:

 command.CommandText = "  insert into els (ziel, @statebox,comment,status,Eart) values (@eort,@thetime,@comment,@statebox,@Eart) ";

This gives me an mysql error.

It seems that the @values have '' at the words. How can i delete this?

Upvotes: 0

Views: 521

Answers (2)

Steve
Steve

Reputation: 216302

You cannot use a parameter to reference a field name. However, if you provide your user with a predetermined list of fields between he/she can choose then you can safely use a form of string concatenation to insert the field to update/insert into.

This means that you need to have a combobox without any editing capability but just a selection of the possible fields.

In your case, it seems that this combobox could be the one named MyComboBox

Thus

 command.CommandText = @"insert into els 
       (ziel, " + MyComboBox.SelectedItem.ToString() +  
       @",comment,status,Eart) values 
       (@eort,@thetime,@comment,@statebox,@Eart)";

Said that consider to remove the use of AddWithValue. This shortcuts has big drawbacks, in particular when you pass a string variable (Text) and expects it to correctly translate your text in a datetime value. Use the Add method instead converting and checking your inputs and specifying the correct datetype for the parameter.

DateTime dt;
if(!DateTime.TryParse(thetime_insert.Text, out dt);
    // Message, not a valid date....
else
  command.Parameters.Add("@thetime", MySqlDbType.Date).Value = dt;

Upvotes: 1

Rahul
Rahul

Reputation: 77886

Your INSERT statement as pointed below is wrong. You can't simply plug-in a dynamic column in your insert statement which doesn't exists in your table schema

insert into system (ziel, @statebox,comment,status,Eart)

This can only be done in case of SELECT statement and doing below is perfectly alright

select ziel, @statebox,comment,status,Eart from `system`

Well, if you have your column name in variable then build your query string like

string query = string.Format("insert into els (ziel, {0},comment,status,Eart) values ", column_name_variable);

Upvotes: 2

Related Questions