select query with where clause in ado.net using asp.net mv

Question

this is my select query with where clause every time I call it the datatable is empty and list count is 0 please correct my select query or ado.net code. I want to get data from the SQL server from these parameters but I don't know my SQL query is wrong or the ado.net code is wrong

public List<AdsModel> GetAds(string _location, Int64 _maxprice, Int64 _minprice, int _maxarea, int _minarea)
        {
            connection();
            List<AdsModel> AdsModelList = new List<AdsModel>();
            SqlCommand cmd = new SqlCommand("select * from propertydata_tbl where Location like '%@location%' and Price >= @minprice and Price <= @maxprice and Area >= @minarea and Area <= @maxarea", con);
            cmd.Parameters.AddWithValue("@location", _location);
            cmd.Parameters.AddWithValue("@minprice", _minprice);
            cmd.Parameters.AddWithValue("@maxprice", _maxprice);
            cmd.Parameters.AddWithValue("@minarea", _minarea);
            cmd.Parameters.AddWithValue("@maxarea", _maxarea);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            con.Open();
            da.Fill(dt);
            con.Close();

            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {

                    AdsModelList.Add(

                        new AdsModel
                        {
                            id = Convert.ToInt32(dr["Id"]),
                            price = Convert.ToInt64(dr["Price"]),
                            location = Convert.ToString(dr["Location"]),
                            area = Convert.ToInt32(dr["Area"]),
                            postdate = Convert.ToString(dr["Postdate"]),
                            titlelink = Convert.ToString(dr["Titleline"]),
                            adlink = Convert.ToString(dr["Adlink"])
                        }

                        );
                }

            }

            return AdsModelList;
        }

Does this answer your question? [how to get LIKE clause to work in ADO.NET and SQL Server](https://stackoverflow.com/questions/2589283/how-to-get-like-clause-to-work-in-ado-net-and-sql-server) — devNull, Aug 31 '20 at 21:51

score 0 · Accepted Answer · answered Aug 31 '20 at 21:33

ADO doesn't interpolate strings, and regards everything inside single quotes as a string literal sent to SQL. You should extract the @location from this literal:

SqlCommand cmd = new SqlCommand("select * from propertydata_tbl where Location like '%' + @location + '%' and Price >= @minprice and Price <= @maxprice and Area >= @minarea and Area <= @maxarea", con);
// Here --------------------------------------------------------------------------------^-----------^

select query with where clause in ado.net using asp.net mv

1 Answers1