0

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
  • 511
  • 6
  • 14

2 Answers2

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();
Adarsh D
  • 511
  • 6
  • 14
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.

Farista Latuconsina
  • 194
  • 9