SelectCommand with Parameters provides empty result

Question

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

Answer 1

As people have said here the issue is that you cant pass field names as parameters.

The approach you are taking is a bad idea for a couple of reasons, firstly when you pass a sql command in this way the server has to recompile it every time you execute that query, this puts extra load on the server and slows down performance. Secondly it is a risk to security transmitting your select statements like this as it gives anyone who intercepts it a look at your table structure. Thirdly using select statements like this means if you ever want to reuse the code you cant without a copy paste.

What I would reccomend is switching to a stored procedure. you can still pass in your parameters etc but it will improve your code as it takes the SQL out of the c# and leaves only what is relevant.

If you REALLY need to pass in fieldnames to be used within the select statement like this you can do this in SQL and build up a query string then execute it using sp_executesql.

Basically what you do is declare a query string like

DECLARE @queryString VARCHAR(3000)

SET @queryString ='SELECT id, '+@FROM+' AS from, '+@TO+' AS to FROM Dictionary WHERE +'@FROM+' LIKE %'+@SEARCHSTRING+'%'

then just use sp_executesql to execute the @queryString

You may need to cast the parameters as Varchar though if you get any errors whilst building up the querystring

Answer 2

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.

Answer 3

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...

Answer 4

This:

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

suppose to be:

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