How can I declare cookie

Question

I am trying to make this code work fine as i can but i couldnt,i am using cookie and i want to rebind my ListView depending on cookie Location but i am getting error message: "Must declare the scalar variable "@Location"."

protected void Sortcarbtn_Click(object sender, EventArgs e)
    {
        HttpCookie cookie = Request.Cookies.Get("Location");
        using (SqlConnection carcon = new SqlConnection(ConfigurationManager.ConnectionStrings["BeravaConnectionString"].ConnectionString))

            if (cookie != null)
            {
                string CarSqlST = @"SELECT DISTINCT AdsID, Section, Category, Country, Maker, Gear, Condition, Status, State, City, AdsTit, 
                SUBSTRING(AdsDesc,1,155) as AdsDesc, Year, AdsPrice, Img1 From ads Where 1=1 AND Category=@CATE AND Country = @Location ";


                var Location = Convert.ToString(cookie["Location"]);
                var cat = Convert.ToString(Request.QueryString["cat"]);

                string condition = "";

                if (barndcardrlst.SelectedValue != "")
                {
                    condition += " and Maker='" + barndcardrlst.SelectedValue + "'";
                }
                if (GearDrDw.SelectedValue != "")
                {
                    condition += " and Gear='" + GearDrDw.SelectedValue + "'";
                }


                if (carstatedrdolst.SelectedValue != "")
                {
                    condition += " and State='" + carstatedrdolst.SelectedValue + "'";
                }
                if (citiesdrdolst.SelectedValue != "")
                {
                    condition += " and City='" + citiesdrdolst.SelectedValue + "'";
                }


                if (CarCondDrDw.SelectedValue != "")
                {
                    condition += " and Condition='" + CarCondDrDw.SelectedValue + "'";
                }
                if (CarstusDRDL.SelectedValue != "")
                {
                    condition += " and Status='" + CarstusDRDL.SelectedValue + "'";
                }
                if ((CarPriceFrmDrDw.SelectedValue != "") && (CarPriceToDrDw.SelectedValue != ""))
                {
                    condition += " and AdsPrice BETWEEN " + CarPriceFrmDrDw.SelectedValue + " AND " + CarPriceToDrDw.SelectedValue;
                }

                if ((CarYearfrmDrDw.SelectedValue != "") && (CarYeartoDrDw.SelectedValue != ""))
                {
                    condition += " and Year BETWEEN " + CarYearfrmDrDw.SelectedValue + " AND " + CarYeartoDrDw.SelectedValue;
                }


                DataTable cdt = new DataTable();
                carcon.Open();
                SqlCommand ccmd = new SqlCommand();
                ccmd.Connection = carcon;
                ccmd.CommandType = CommandType.Text;
                ccmd.Parameters.AddWithValue("@Country", Location);
                ccmd.Parameters.AddWithValue("@CATE", cat);
                ccmd.CommandText = CarSqlST + condition;
                SqlDataAdapter ad = new SqlDataAdapter();
                ad.SelectCommand = ccmd;

                ad.Fill(cdt);
                cateshowlistview.DataSource = cdt;
                cateshowlistview.DataBind();

            }


    }

Answer 1

Change "@Country" in

ccmd.Parameters.AddWithValue("@Country", Location); 

to be "@Location"

ccmd.Parameters.AddWithValue("@Location", Location); 

you defined the Country in the SQL Statement to be @Location

string CarSqlST = @"SELECT ... AND Category=@CATE AND Country = @Location ";

Update

To prevent SQL Injection hacks and to allow for SQL to reuse the query options all the fileters where you are concat the string together you should just use SQL Parameters. To make it easy I create a parameters dictionary to add to. Then at the end loop through the dictionary to fill in the SQL Parameters. I also switched it to string builder since could be doing a lot of string concats. I didn't test this code because I don't have your objects or tables or connections.

using (var carcon = new SqlConnection(ConfigurationManager.ConnectionStrings["BeravaConnectionString"].ConnectionString)))
{
    if (cookie != null)
    {
        // Parameters for SQL
        var parameters = new Dictionary<string, object>();

        // string builder to build up SQL Statement
        var CarSqlST = new StringBuilder(
            "SELECT DISTINCT AdsID, Section, Category, Country, Maker, Gear, Condition, Status, State, City, AdsTit, " +
            "SUBSTRING(AdsDesc,1,155) as AdsDesc, Year, AdsPrice, Img1 From ads " +
            "Where Category = @pCATE AND Country = @pLocation ");

        parameters.Add("@pCATE", Request.QueryString["cat"].ToString());
        parameters.Add("@pLocation", cookie["Location"]);

        if (barndcardrlst.SelectedValue != "")
        {
            CarSqlST.Append(" and Maker= @pMaker");
            parameters.Add("@pMaker", barndcardrlst.SelectedValue);
        }

        if (GearDrDw.SelectedValue != "")
        {
            CarSqlST.Append(" and Gear= @pGear");
            parameters.Add("@pGear", GearDrDw.SelectedValue);
        }

        if (carstatedrdolst.SelectedValue != "")
        {
            CarSqlST.Append(" and State= @pState");
            parameters.Add("@pState", carstatedrdolst.SelectedValue);
        }

        if (citiesdrdolst.SelectedValue != "")
        {
            CarSqlST.Append(" and State= @pCity");
            parameters.Add("@pCity", citiesdrdolst.SelectedValue);
        }

        if (CarCondDrDw.SelectedValue != "")
        {
            CarSqlST.Append(" and Condition= @pCondition");
            parameters.Add("@pCondition", CarCondDrDw.SelectedValue);
        }

        if (CarstusDRDL.SelectedValue != "")
        {
            CarSqlST.Append(" and Status= @pStatus");
            parameters.Add("@pStatus", CarstusDRDL.SelectedValue);
        }

        if ((CarPriceFrmDrDw.SelectedValue != "") && (CarPriceToDrDw.SelectedValue != ""))
        {
            CarSqlST.Append(" and AdsPrice BETWEEN @pLowPrice AND @pHighPrice");
            parameters.Add("@pLowPrice", CarPriceFrmDrDw.SelectedValue);
            parameters.Add("@pHighPrice", CarPriceToDrDw.SelectedValue);
        }

        if ((CarYearfrmDrDw.SelectedValue != "") && (CarYeartoDrDw.SelectedValue != ""))
        {
            CarSqlST.Append(" and Year BETWEEN @pLowYear AND @pHighYear");
            parameters.Add("@pLowYear", CarYearfrmDrDw.SelectedValue);
            parameters.Add("@pHighYear", CarYeartoDrDw.SelectedValue);

        }

        DataTable cdt = new DataTable();
        SqlCommand ccmd = carcon.CreateCommand();;
        ccmd.CommandType = CommandType.Text;

        // Add all the parameters into this command
        foreach (var parameter in parameters)
        {
            ccmd.Parameters.Add(parameter.Key, parameter.Value);
        }

        // set the command text from string builder
        ccmd.CommandText = CarSqlST.ToString();

        SqlDataAdapter ad = new SqlDataAdapter();
        ad.SelectCommand = ccmd;
    }
}

You could have created the command at the top and filled in the sql parameters right away instead of the dictionary but I like the dictionary approach better in case something happens - exception or we need to bail we never created the SQL Command.