SQL like Clause does not work with SqlParameter

Following the recommendation that @Maniero made in this question I decided to parameterize the queries SQL of my program.

See:

    private void btnConsulta_Click(object sender, EventArgs e)
    {
        if (optID.Checked) //Consulta por ID
        {
            if (nmrID.Text == "0" || nmrID.Text == "")
                cnxCli.sel = "select * from cliente;";
            else
            {
                cnxCli.sel = "Select * from cliente " +
                                 "WHERE IDCliente = @ID";

                cnxCli.selCmd.CommandText = cnxCli.sel;
                cnxCli.selCmd.Parameters.Clear();
                cnxCli.selCmd.Parameters.Add("@ID", SqlDbType.Int);
                cnxCli.selCmd.Parameters["@ID"].Value = nmrID.Text;
            }
            cnxCli.clienteDataTable.Clear();
            cnxCli.clienteAdapter.Fill(cnxCli.clienteDataTable);
            gridPrincipal.Update();

        }
        else if(optNome.Checked)//Consulta por Nome
        {
            if (txtNome.Text == "")
                cnxCli.sel = "select * from cliente";
            else
            {
                cnxCli.sel = "Select * from cliente " +
                             "WHERE Nome like '%@Nome%'";

                cnxCli.selCmd.CommandText = cnxCli.sel;
                cnxCli.selCmd.Parameters.Clear();
                cnxCli.selCmd.Parameters.Add("@Nome", SqlDbType.Text);
                cnxCli.selCmd.Parameters["@Nome"].Value = txtNome.Text;
            }
            cnxCli.clienteDataTable.Clear();
            cnxCli.clienteAdapter.Fill(cnxCli.clienteDataTable);
            gridPrincipal.Update();
        }
    }

It turns out that the Code section after else, which should return all records containing the name related to the parameter @Nome, returns only an empty table. As you can see the query by ID was done the same way, and it returns the result of select normal.

I did a check, set a breakpoint right after the line cnxCli.selCmd.Parameters["@Nome"].Value = txtNome.Text; to check if everything was right.

I checked that cnxCli.selCmd.CommandText contains the string "Select * from cliente WHERE Nome like '%@Nome%'", as expected. I then imagined that something wrong happened with the value of the parameter @Nome. But the value of the parameter was the text found in txtNome.Text, also as expected.

Then I mounted the query in SQL Server, imagining that I had mounted the like '%%' in the wrong way, but everything's normal, too.

Where am I wrong?

EDIT: SOLUTION FOUND

I found a similar problem in Stack Overflow in English. Use of SqlParameter in SQL like clause not working

For some reason that only God knows which, when I do something like Select * from Cliente WHERE Nome like '@Nome' or Nome like '%@Nome%', what actually happens is that the parameter is not replaced by its value, so the query will be executed searching for the name of the parameter in the database.

The way to solve the problem is by doing this:

cnxCli.sel = "Select * from cliente " +
             "WHERE Nome like @Nome";//sem aspas ou %%, apenas o nome do parâmetro

Then you must modify the value of the parameter when you assign it.

cnxCli.selCmd.Parameters["@Nome"].Value = "%"+ txtNome.Text + "%";

I particularly found it pretty weird, but it worked.

Author: Maniero, 2015-12-20

4 answers

One of the ways to solve:

cnxCli.sel = "SELECT * FROM cliente WHERE Nome LIKE CONCAT('%', @Nome, '%')";
 4
Author: bfavaretto, 2016-06-28 19:00:55

Replace:

cnxCli.sel = "Select * from cliente WHERE Nome like '%@Nome%'";

By:

cnxCli.sel = "Select * from cliente WHERE Nome like '%' + @Nome + '%'";

Reference 1

Reference 2

 3
Author: Luã Govinda Mendes Souza, 2017-05-23 12:37:27

The LIKE should be placed as follows:

cnxCli.sel = "Select * from cliente WHERE Nome like @Nome ";

cnxCli.selCmd.Parameters.Clear();
cnxCli.selCmd.Parameters.Add("@Nome", SqlDbType.Text);
cnxCli.selCmd.Parameters["@Nome"].Value = '%' +  txtNome.Text + '%';
 3
Author: Zenga, 2016-09-06 13:00:26

It seems to me that your problem is in WHERE.

Replace:

cnxCli.sel = "Select * from cliente WHERE Nome like '%@Nome%'";

By:

cnxCli.sel = "Select * from cliente WHERE Nome like @Nome";
 2
Author: Leandro Angelo, 2016-09-06 12:59:06