syntax error on mysql query width Select and Limit

Question

I get a error that a query on a mysql database has a wrong syntax. The code should check if a string {Rfid} is present in a Tags database and if not create it. The exception is caused in the first MySqlcmd.CommandText But i dont see it, they're normal mysql verbs, the only speciality perhaps might be that Rfid can contain spaces, but i fail to see why that would give a problem.

OpenConnection(); //a function to connect, no problems here.
MySqlDataReader reader;
MySqlcmd.CommandText = $"SELECT * FROM 'Tags' WHERE 'UHFRFID'=\"'{Rfid}'\"   LIMIT 1;";
reader = MySqlcmd.ExecuteReader();  // >> crashes

        //create a new tag in table, if its unknown.
        if (!reader.HasRows)
        {
            try
            {
                MySqlcmd.CommandText = $"INSERT INTO `Tags` (`TagId`,  `UHFRFID`, `Gate1`) VALUES(NULL,\"'{Rfid}'\",  NULL);";
                MySqlcmd.CommandType = System.Data.CommandType.Text;
                MySqlcmd.ExecuteNonQuery();
            }
            catch
            { }

The MySQL error i get is:

MySql.Data.MySqlClient.MySqlException: '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 ''Tags' WHERE 'UHFRFID'='tag 13 00000' LIMIT 1' at line 1'

If your question is not a duplicate then please edit it to explain _why_. You may also ping the person who closed it by putting an @ in front of their username. I'm sure they'd be more than happy to reopen if they made a mistake - but you need to explain why it is not a duplicate not just assert it! — Stephen Kennedy, Mar 05 '18 at 17:44

fubo · Answer 1 · 2018-03-05T13:08:14.353

1

replace

MySqlcmd.CommandText = $"SELECT * FROM 'Tags' WHERE 'UHFRFID'=\"'{Rfid}'\"    LIMIT 1;";

with

MySqlcmd.CommandText = $"SELECT * FROM `Tags` WHERE `UHFRFID`=\"'{Rfid}'\"    LIMIT 1;";

Tags and UHFRFID are table/column names and not string values.

You have to use back ticks ` instead of single quotes '

edited Mar 05 '18 at 13:08

answered Mar 05 '18 at 10:59

fubo

44,811
17
103
137

Well no Tags was a table, but the single quotes made the query look like if it where a column. just noted it after posting it here, but your on the same track so i reward it. – Peter Mar 05 '18 at 11:09
@user3800527 right, i've updated my answer thank you – fubo Mar 05 '18 at 12:46

syntax error on mysql query width Select and Limit

1 Answers1