'SelectCommand with Parameters provides empty result

Currently I will clean my code a little bit and VS told me, it is better to use the SqlParameter for the sql commands instead a compound string. So I decided to change my code, unfortunately now I don’t get a result and I don’t know why. Here is the piece of my code:

...    
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetSQLConnectionString());
SqlDataAdapter sqlSelect = new SqlDataAdapter();
try
{
    connection.Open();
    sqlSelect.SelectCommand = connection.CreateCommand();
    sqlSelect.SelectCommand.CommandText = "SELECT id, @FROM AS \"from\", @TO AS \"to\" FROM Dictionary WHERE @FROM LIKE @SEARCHSTRING";
    sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@FROM", this.from));
    sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@TO", this.to));
    sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@SEARCHSTRING", "'%" + this.SearchField.Text + "%'"));

    sqlSelect.Fill(dt);
    connection.Close();
}
catch(SqlException e)
...

I don’t get any exception. Why is dt empty after the search? (With a compound string, the select works.) What went wrong?

Greetz

c++sharpparametersselectcommand


Solution 1:[1]

You can't specify field names using parameters like that. In your where clause WHERE @FROM LIKE @SEARCHSTRING it is comparing the value of the parameter @FROM with the value of the parameter @SEARCHSTRING.

If the where clause evaluates to true you will get every record in the dictionary table, if it evaluates to false you will get no records. It will never treat the contents of @from as a field name in the dictionary table.

Solution 2:[2]

Why you have written query like this?

   "SELECT id, @FROM AS \"from\", @TO AS \"to\" FROM Dictionary WHERE @FROM LIKE @SEARCHSTRING";

you are trying to fetch @FROM from the table and also trying to pass it as a parameter, hows that supposed to work? Also why have you included slashes? they just make things messy, remove them. A Select query takes input parameters only with "WHERE" clause and nowhere else.

Try replacing it with this

"SELECT id, FROM AS 'from', TO AS 'to' FROM Dictionary WHERE FROM LIKE @SEARCHSTRING";

Also remove all but the last occurrences of:

sqlSelect.SelectCommand.Parameters.Add

Also take care that "FROM" is an SQL keyword as well, so make sure its being interpreted the right way by enclosing it in "[]".

Hope this helps...

Solution 3:[3]

This:

sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@SEARCHSTRING", "'%" + this.SearchField.Text + "%'"));

suppose to be:

sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@SEARCHSTRING", "%" + this.SearchField.Text + "%"));

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ben Robinson
Solution 2 Aman
Solution 3 Peter Csala

Related Questions