CODEWITHSUNDEEP
vb.netparameter-passingsqlcommand
phalanx
phalanx

Reputation: 497

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

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?

Upvotes: 3

Views: 100

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415860

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

Upvotes: 3

Don Thomas Boyle
Don Thomas Boyle

Reputation: 3045

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.

Upvotes: 1

Related Questions