SQL query syntax with ORDER BY not working

Question

I am new to C #, but I know SQL well. I have a query that works fine in the SQL editor, but I cannot integrate it into my code. This eliminates incorrect SQL Exception syntax. The original SQL query that works fine:

select an excellent description from table_A, where id = '001', from the description;

A C # line would look like this:

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

The above query works in a C # program when I delete a part + "order by description. Here _selectedPlantIDis the only variable that gets the value in the program. I am pretty sure there should be some kind of problem with quotes, but for me everything looks great, so I was wondering if there is any other way to write this in C #?

Answer 1

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

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

But since it is open to SQL Injection (full example here ), consider using parameterized queries :

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

Answer 2

Here's what your SQL looks like in C # after replacing 1with _selectedPlantID:

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

Do you see the problem?

However, instead of adding space to “fix” the problem, use parameterized queries instead . This problem you just encountered is just one of the problems of "string and parameter concatenation"; the most dangerous, however, is SQL injection .

Answer 3

ORDER BY :

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

Answer 4

string.Format , :

_cmd.CommandText = string.Format("select distinct description from Table_A where Plant_ID ='{0}' order by description", _selectedPlantID);