.Net windows form app mysql database query-CodePudding

I am learning C# and as part of a exercise with .Net I need to give the user a form with from , to and amount fields and when user hits the transfer button. The application should deduct the amount from the from account_id and add the money to To account_id

This is my button function code

string mysql_conn_string = "server=localhost;user=root;database=vsp;port=3306;password=password";
                MySqlConnection con = new MySqlConnection(mysql_conn_string);
                con.Open();
                string s1 = "update bank set amount = amount - "   textBox3.Text   "where account_id = "   textBox1.Text;
                string s2 = "update bank set amount = amount   "   textBox3.Text   "where account_id = "   textBox2.Text;

                MySqlTransaction tx = con.BeginTransaction();
                MySqlCommand cmd = new MySqlCommand(s1, con, tx);
                int a = cmd.ExecuteNonQuery();
                MySqlCommand cmd2 = new MySqlCommand(s2, con, tx);
                int b = cmd2.ExecuteNonQuery();

                if (a == 0 || b == 0)
                {
                    tx.Rollback();
                    MessageBox.Show("Rolling bacck");
                }
                else
                {
                    tx.Commit();
                    MessageBox.Show("Transaction Succesful");
                }
                con.Close();

I am getting the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySqlserver version for the right syntax to use near 'account_id = 2` at line 1

But the same sql statements are working correctly in mysql workbench and not showing any errors.

My table has account_id and amount columns.

Both are of INT

I have tried changing the textbox type to INT by using both Int32.Parse and Int64.Parse

CodePudding user response：

From what I'm seeing, the error isn't on the WHERE condition but on the data because you need add one space. You have this:

string s1 = "update bank set amount = amount - "   textBox3.Text   "where account_id = "   textBox1.Text;
string s2 = "update bank set amount = amount   "   textBox3.Text   "where account_id = "   textBox2.Text;

Then, when the query is built you will have this:

s1 = "update bank set amount = amount - 100where account_id = 1";

As you can see, the WHERE and the text on textBox3 are together, try add one space like this:

string s1 = "update bank set amount = amount - "   textBox3.Text   " where account_id = "   textBox1.Text;