How to pass "%" as part of a parameter to a SqlCommand?

Question

When using SqlDataSource it is made this way:

SqlDataSource1.SelectCommand = "SELECT field,field FROM TABLE WHERE name LIKE @name"
SqlDataSource1.SelectParameters.Clear()
SqlDataSource1.SelectParameters.Add( _
            New Parameter("name", DbType.String, "%" + TextBox1.Text + "%"))

But if I try:

command.Parameters.Add( _
    New Parameter("name", SqlDbType.VarChar, "%" + TextBox1.Text + "%"))

or

command.Parameters.Add(New Parameter("name", DbType.String, "%" + TextBox1.Text + "%"))

it fails. How to do it with SqlCommand?

score 3 · Answer 1 · answered Sep 12 '13 at 16:49

3

Try structuring your sql statement like this:

SELECT field,field FROM TABLE WHERE name LIKE N'%' + @name + N'%'

Resulting in VB that looks like this:

SqlDataSource1.SelectCommand = "SELECT field,field FROM TABLE WHERE name LIKE N'%' + @name + N'%'"
SqlDataSource1.SelectParameters.Clear()
SqlDataSource1.SelectParameters.Add("@Name", SqlDbType.NVarChar, 50).Value = TextBox1.Text

answered Sep 12 '13 at 16:49

Joel Coehoorn

399,467
113
570
794

I'm really curious why `N'%'` instead of just `'%'` ? – Don Thomas Boyle Sep 12 '13 at 16:59
2

@DonThomasBoyle I'm assuming the `name` column is an NVarChar type instead of just a Varchar (If it's not, it probably should be). If you don't have the `N`, the type of the resulting string literal is a varchar, and Sql Server would have to do extra work to convert it to NVarchar when it combines it with the rest of the string. In the worst case, missing the `N` can cause a type mismatch that prevents sql server from using an index, resulting in a _significant_ drop in performance. In this case, the initial wildcard already breaks the index, so any difference is likely to be small. – Joel Coehoorn Sep 12 '13 at 17:00

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

Try adding parameters another way

Command.Parameters.Add("@Name", SqlDbType.VarChar).Value = "%" & TextBox1.Text & "%"

Additionally just to not cause any issues try using & instead of +. Not that i can be certain that % is considered a number or arrhythmic condition but it seems to be explained Here, well.

edited May 23 '17 at 10:25

Community

1
1

answered Sep 12 '13 at 16:32

Don Thomas Boyle

3,055
3
32
54

I also prefer that overload `.Add()`, but for this issue it isn't really any different than what already doesn't work for him. – Joel Coehoorn Sep 12 '13 at 16:49
Yes it is , especially in the operators, also this is tested and works on my end. – Don Thomas Boyle Sep 12 '13 at 16:50

How to pass "%" as part of a parameter to a SqlCommand?

2 Answers2