SQL LIKE clause does not work with Sqlparameter

Question

Following the recommendation that the @Maniero fez in that question I decided to parametrize the darlings SQL of my program.

Behold:

    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 the else, that 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, I set up a breakpoint right after the line cnxCli.selCmd.Parameters["@Nome"].Value = txtNome.Text; to make sure everything was all right.

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

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

Where am I going 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 parameter name 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 the value of the parameter when we assign it.

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

I personally thought it was pretty weird, but it worked.

Answer 1

One of the ways to resolve:

cnxCli.sel = "SELECT * FROM cliente WHERE Nome LIKE CONCAT('%', @Nome, '%')";

Answer 2

Substitute:

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

For:

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

Reference 1

Reference 2

Answer 3

The LIKE shall be set out 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 + '%';

Answer 4

It seems to me that your problem is in WHERE.

Substitute:

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

For:

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