LIKE SQL clause does not work with SqlParameter

Question

LIKE SQL clause does not work with SqlParameter

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (2 votes)
#4 by (1 votes)

8

Following the recommendation that @bigown did in this question I decided to parameterize the SQL queries 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 @Nome parameter, returns only one empty table. As you can see the query by ID was done the same way, and it returns the result of the normal select.

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

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

Then I put the query in SQL Server, figuring that I had mounted like '%%' wrongly, but everything is normal too.

Where am I going wrong?

EDIT: SOLUTION FOUND

I found a similar problem in Stack Overflow in English.

For some reason that 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 looking 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 change the value of the parameter when we assign it.

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

I particularly found this quite weird, but it worked.

c# winforms

asked by anonymous 20.12.2015 / 17:13

4 answers

Callback being executed the moment you load the page How do I create a homepage for my GITHUB libraries?

score 3 · Answer 1

3

One of the ways to solve:

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

28.06.2016 / 21:00

score 2 · Answer 2

2

Replace:

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

By:

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

Reference 1

Reference 2

04.03.2016 / 12:54

score 2 · Answer 3

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 + '%';

score 1 · Answer 4

1

I think 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";

21.12.2015 / 13:14