SqlParameter not working with LIKE Operator ASP.NET [duplicate]

Question

I have this query to fetch a few rows from a table in SQL Server database. But the query always return 0 rows. There is no error but this query doesn't match any rows.

string name = //string data from http request


 var apriori = db.Aprioris.SqlQuery("Select top 5 * from Apriori where Antecedent LIKE '%@name%' ", new SqlParameter("@name", name)).ToList();

i've tried using = instead of LIKE then It worked but in this contest I should use the LIKE keyword because I need a partial match.

I also tried this one

string name = "\'"+"%"+prod.Name+"%"+"\'";//'%stringval%'
var apriori = db.Aprioris.SqlQuery("Select top 5 * from Apriori where Antecedent LIKE @name ", new SqlParameter("@name", name)).ToList();

But none of it worked, Anybody know why it doesn't work?

There is nothing wrong with my data in table because this one works perfectly

var apriori = db.Aprioris.SqlQuery("Select top 5 * from Apriori where Antecedent LIKE '%meatballs%' ").ToList();

Adarsh D · Accepted Answer · 2020-05-02 09:07:28Z

0

I replaced the data type of name from string to SqlDbType and it worked. This is the modified code

            SqlParameter name = new SqlParameter();
            name.ParameterName = "@name";
            name.SqlDbType = System.Data.SqlDbType.VarChar;
            name.Value = "%" + prod.Name + "%";// prod.Name from http request
            var apriori = db.Aprioris.SqlQuery("Select top 5 * from Apriori where Antecedent LIKE @name", name).ToList();

answered May 2, 2020 at 9:07

Adarsh D

5917 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Farista Latuconsina · Accepted Answer · 2020-05-02 10:26:05Z

0

Probably all you want to do is like this:

string name = //string data from http request


 var apriori = db.Aprioris.SqlQuery("Select top 5 * from Apriori where Antecedent LIKE @name ", new SqlParameter("@name", "%" + name + "%")).ToList();

Since you need to add the "%" in the value of the parameter, not in the query. And also the name of the parameter in the query string should not be surrounded by quotes.

answered May 2, 2020 at 10:26

Farista Latuconsina

3733 silver badges13 bronze badges

Collectives™ on Stack Overflow

SqlParameter not working with LIKE Operator ASP.NET [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related