SQL query syntax with ORDER BY not working

Question

I am new to C# but I know SQL quite well. I have a query that works fine in SQL editor, but I can't integrate it in my code. It throws wrong SQL syntax Exception. The original SQL query which works fine is :

select distinct description from Table_A where id = '001' order by description;

The C# string would be like:

_cmd.CommandText = "select distinct description from Table_A where Plant_ID =" + _selectedPlantID + "order by description";

The above query works in C# program when I remove the + "order by description part. Here _selectedPlantID is the only variable that gets value in the program. I am quite sure that there should be some quotes problem, but to me everything looks fine, so was wondering if there is any other specific way to write this in C#?

Answer 1

This is what your SQL looks like in C# after replacing 1 as the _selectedPlantID:

select distinct description from Table_A where id =1order by description

You see the problem?

However, instead of adding a space to "fix" the problem, please use parameterized queries instead. This issue you have just encountered is just one of the troubles of "concatenating strings and parameters"; the most dangerous, however, is SQL injection.

Answer 2

You are missing a space between " and 'order by, this would work :

 _cmd.CommandText = "select distinct description from Table_A where Plant_ID ='" + _selectedPlantID + "' order by description";

But since it is open to SQL Injection (comprehensive example here), consider using parametized queries instead :

_cmd.CommandText = "select distinct description from Table_A where Plant_ID = ? order by description";
command.Parameters.AddWithValue("@plant", _selectedPlantID );