Error: You have an error in your SQL syntax - but SQL statement works

Question

I am trying to iteratively update a MySql table in my c# app in a for loop. I am getting this error:

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '935 WHERE ID=1' at line 1

However, when I run my SQL statement in PhpMyAdmin, it is working well and it updates particular place in my table.

CODE:

for (int i = 1; i <= 10; i++)
        {
            string queryMean = " SELECT (RT1+RT2+RT3+RT4+RT5+RT6+RT7+RT8+RT9+RT10+RT11+RT12+RT13+RT14+RT15+RT16+RT17+RT18+RT19+RT20+RT21+RT22+RT23+RT24+RT25+RT26+RT27+RT28+RT29+RT30+RT31+RT32+RT33+RT34+RT35+RT36+RT37+RT38+RT39+RT40+RT41+RT42+RT43+RT44+RT45+RT46+RT47+RT48+RT49+RT50+RT51+RT52+RT53+RT54+RT55+RT56+RT57+RT58+RT59+RT60+RT61+RT62+RT63+RT64+RT65+RT66+RT67+RT68+RT69+RT70+RT71+RT72+RT73+RT74+RT75+RT76+RT77)/77 AS priem FROM reflextime WHERE reflextime.ID=" + i;
            MySqlCommand cmd = new MySqlCommand(queryMean, conect);

            readerMean = cmd.ExecuteReader();
            readerMean.Read();
            string result = readerMean["priem"].ToString();
            double priem = Convert.ToDouble(result);
            double priemer = Math.Round(priem, 3);
            readerMean.Close();

            string query2 = "UPDATE feture SET priemer="+priemer+" WHERE ID="+i;
            MySqlCommand cmdx = new MySqlCommand(query2, conect);
            cmdx.ExecuteScalar();   // here is the exception thrown
        }

Thank you.

Is `priemer` column type is character? And please use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. — Soner Gönül, Apr 23 '14 at 09:43
First thing to do: start using parameterized SQL instead of including values in the SQL directly. Aside from anything else, your code is currently culture-sensitive. — Jon Skeet, Apr 23 '14 at 09:43
which one of the two queries actually fails? I guess the update? Could you please print out the queries you get when they fail? — Olli, Apr 23 '14 at 09:45
How on earth do you end up with 77 columns all, apparently, containing compatible/comparable values, and not start to think that there must be a better way to model this? — Damien_The_Unbeliever, Apr 23 '14 at 09:47
What is the query who fails? You could isolate one of them to the problem. And as It said up use parametrized Sql. — , Apr 23 '14 at 09:51
@Damien_The_Unbeliever I know that this is not a pretty solution at all, but I did not find any other way to compute mean of values from all columns WHERE ID = something. If you are aware of something, Please let me know and I will be very thankful — Martin Nemeth, Apr 23 '14 at 09:52
Almost always, what it means is that instead of having columns `ID, RT1, RT2, RT3`, etc, what you ought to have are just the columns `ID,x,RT` where `x` is the column that will contain `1`, `2`, `3`, etc, and given a suitable name. Essentially, it looks like you've ended up with *data* embedded in your table's *metadata*. Computing the average for 77 *rows* values for a single column is a built-in operator, `AVG(RT)`. — Damien_The_Unbeliever, Apr 23 '14 at 09:58

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

Be aware that if this runs on your machine (slovak culture settings) that the value in double priemer gets translated to string as e.g. "586,935". That is not what you want, as , has different meaning in SQL than .. Simplest way to fix the problem is to use

string query2 = "UPDATE feture SET priemer="+priemer.ToString(CultureInfo.InvariantCulture)+" WHERE ID="+i;

You may have found this issue, if you printed the query2 before posting it here, by the way.

Be also aware that this is in no way a best practice to call a query in C#, a nice clue for how to create a parametrised query may be found here.

edited May 23 '17 at 11:57

Community

1
1

answered Apr 23 '14 at 09:51

Tomas Pastircak

2,867
16
28

1

If you aren't going to provide a solution that uses SQL parameters you should at least mention that building SQL queries in this way is a very bad idea. – DGibbs Apr 23 '14 at 09:56
OK, I'll mention it, but it was already mentioned in the comments - I didn't want to copy what someone else has already said. – Tomas Pastircak Apr 23 '14 at 10:01
Many people don't bother to read the comments and instead look straight at the answers. – DGibbs Apr 23 '14 at 10:06

Error: You have an error in your SQL syntax - but SQL statement works

1 Answers1