MySQL syntax error using MySqlCommand in ASP.NET

Question

Below I have the SQL query which I have tested is working fine

Select 
    data_info.user_id, data_info.type_id, data_info.specific_title,
    data_info.content, life_area.type_id, life_area.type_id
From
    data_info 
Left Join
    life_area On data_info.type_id = life_area.id
Where 
    data_info.user_id = '0001'

Now I am trying to convert the working SQL into asp.net MySqlCommand, but it fails to work and show syntax error

MySqlCommand cmd = new MySqlCommand(
    "Select data_info.user_id,data_info.type_id,data_info.specific_title,data_info.content,"+
    "life_area.type_id,life_area.type_id" +
    "FROM data_info LEFT JOIN life_area ON data_info.type_id = life_area.id" +
    "Where data_info.user_id='0001'" , conn);

Print out the SQL string from your code, and look at it closely, especially stuff like missing comas or spaces. — Mat
– Mat, Commented Jun 1, 2018 at 14:59
I dont see spaces between strings, like this ... area.type_id" (space?) + "FROM ... — tym32167
– tym32167, Commented Jun 1, 2018 at 15:01
Looks like you're missing a space between the end of the select list and the FROM for starters. I'd suggest creating a verbatim string instead (put a '@' before it) so you can include line breaks instead of the concatenation mess you currently have. Also in the future you really should include the error message you get. — juharr
– juharr, Commented Jun 1, 2018 at 15:01
@juharr, since i m just starting to learn asp.net , care to explain a little bit more, thanks — epiphany
– epiphany, Commented Jun 1, 2018 at 15:07
@epiphany Well specifically your query is "... life_area.type_idFROM ..." because your second string does not end with a space and your third does not start with one. If you put a @ before the string it allows it to span multiple lines and it is called a verbatim string Also you got an error message when you tried to run this code, you should always include error messages in questions. — juharr
– juharr, Commented Jun 1, 2018 at 15:11

Bradley Grainger · Accepted Answer · 2018-06-02 07:14:30Z

1

You’re combining the strings together without spaces in crucial areas, e.g. life_area.type_id' + 'FROM data_info' gives you life_area.type_idFROM data_info; this is making your SQL invalid. Using a @(verbatim string) string to honour the carriage returns is likely the best solution:

MySqlCommand cmd = new MySqlCommand(
    @"Select data_info.user_id,data_info.type_id,data_info.specific_title,data_info.content,
    life_area.type_id,life_area.type_id
    FROM data_info LEFT JOIN life_area ON data_info.type_id = life_area.id 
    Where data_info.user_id='0001'" , conn);

edited Jun 2, 2018 at 7:14

Bradley Grainger

28.3k4 gold badges95 silver badges117 bronze badges

answered Jun 1, 2018 at 15:08

Liam

30k28 gold badges143 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

juharr Over a year ago

It's actually called a verbatim string or verbatim string literal. String literal means something else by itself.

Collectives™ on Stack Overflow

MySQL syntax error using MySqlCommand in ASP.NET

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related