Parameters in Query

Question

Parameters in Query

Navigation

#1 by (2 votes)

1

When assembling a Query using AddWithValue. Even if it's right, the return is not as expected.

In the example below, the return is Zero

var query = "SELECT nome,usuario,email,administrador FROM GSCUsuarios WHERE @parametroWhere = @parametroCondicao");

dataAdapter.SelectCommand.Parameters.AddWithValue("parametroWhere", parametroWhere);
dataAdapter.SelectCommand.Parameters.AddWithValue("parametroCondicao", parametroCondicao);

I decided to ask in the American stackoverflow and it was suggested to change the query to the following format

var query = string.Format("SELECT nome,usuario,email,administrador FROM GSCUsuarios WHERE {0} = @parametroCondicao", parametroWhere);

dataAdapter.SelectCommand.Parameters.AddWithValue("parametroCondicao", parametroCondicao);

What I would like to understand is because the first Query did not work. Did not AddWithValue just change the values?

Making another Query, using LIKE .. also did not work! How should this Query be mounted?

var query = string.Format("SELECT nome,usuario,email,administrador FROM GSCUsuarios WHERE {0} LIKE '@parametroCondicao%'", parametroWhere);

dataAdapter.SelectCommand.Parameters.AddWithValue("parametroCondicao", parametroCondicao);

Thank you

sql c# winforms

asked by anonymous 16.12.2015 / 21:26

1 answer

Message when the user is exiting the application DELETE function with php and mysql

score 2 · Accepted Answer

Why the first query does not work: parameters for SQL queries are interpreted according to their value; a string parameter is converted to a string in the query (*). Your first query is equivalent to:

SELECT nome,usuario,email,administrador
FROM GSCUsuarios
WHERE 'valorDoParametroWhere' = 'valorDoParametroCondicao'

And since the WHERE condition will never be true (unless the column value is equal to the column name), you always have 0 results.

Regarding the use of LIKE , you can use the following:

var query = string.Format(@"SELECT nome,usuario,email,administrador
                            FROM GSCUsuarios
                            WHERE {0} LIKE @parametroCondicao",
    parametroWhere);

dataAdapter.SelectCommand.Parameters.AddWithValue(
    "parametroCondicao",
    parametroCondicao + "%");

Note that using string.Format to create SQL commands is dangerous - just do this if you are absolutely sure that the parametroWhere value does not come directly from the user (or you will be subject to SQL injection attacks )

(*) Actually the query is compiled before the parameters are applied, but the idea is the same.