c＃和sql关键字'和'附近的语法不正确

I have tried to find the syntax error into my sql statment but i couldnt i tried to add () and [] but its same nothing change so could please help me with this error as i am getting this error message: " Incorrect syntax near the keyword 'and'. " next to : " ad.Fill(cdt);"

我试图在我的sql语句中找到语法错误,但我不能尝试添加()和[]但是它同样没有任何改变所以请帮我解决这个错误,因为我收到此错误消息:“附近的语法不正确关键字'和'。“旁边:”ad.Fill(cdt);“

HttpCookie cookie = Request.Cookies.Get("Location");
        using (SqlConnection carcon = new SqlConnection(ConfigurationManager.ConnectionStrings["BeravaConnectionString"].ConnectionString))

            if (cookie != null)
            {
            string CarSqlST = @"SELECT [JobNum], [Dept], [PubDate], [EndDate], [Employer],[VacCountry], [VacState], [VacCity],
            [Jobtitle], [CompLogo], SUBSTRING([jobdesc],1,40) as jobdesc FROM [jobs] Where 1=1 and [VacCountry] = [@Location] ORDER BY [PubDate] DESC ";


                var Location = cookie.Value;

                string condition = "";

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

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

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

            }

the 2nd code is

第二个代码是

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

            if (cookie != null)
            {
                string sql = @"SELECT [JobNum], [Dept], [PubDate], [EndDate], [Employer],[VacCountry], [VacState], [VacCity],
            [Jobtitle], [CompLogo], SUBSTRING([jobdesc],1,40) as jobdesc FROM [jobs] 
            Where [VacCountry] = @Location AND
            (@State IS NULL OR VacState = @State) AND
            (@City IS NULL OR VacCity = @City)                
            ORDER BY [PubDate] DESC ";

                DataTable cdt = new DataTable();
                SqlCommand ccmd = new SqlCommand(sql, carcon);
                var Location = cookie.Value;
                ccmd.Parameters.AddWithValue("@Location", Location);
                ccmd.Parameters.AddWithValue("@State", filterstathpjob.SelectedValue);
                ccmd.Parameters.AddWithValue("@City", filterJobhpjob.SelectedValue);

                SqlDataAdapter ad = new SqlDataAdapter(ccmd);
                ad.Fill(cdt);
                Joblistview.DataSource = cdt;
                Joblistview.DataBind();

            }
    }

2 个解决方案

#1

This line in your first snippet appears to be your problem:

您的第一个代码段中的这一行似乎是您的问题:

ccmd.CommandText = CarSqlST + condition;

You're adding a condition at the end of your SQL statement, after you've already done an ORDER BY. Your condition needs to be added before the ORDER BY.

在完成ORDER BY之后,在SQL语句的末尾添加条件。您需要在ORDER BY之前添加条件。

#2

Try this

    string CarSqlST = @"SELECT [JobNum], [Dept], [PubDate], [EndDate], [Employer],[VacCountry], [VacState], [VacCity],
    [Jobtitle], [CompLogo], 
    SUBSTRING([jobdesc],1,40) 
    as jobdesc FROM [jobs] Where 1=1 and [VacCountry] = [@Location] ";

    ................


ccmd.CommandText = CarSqlST + condition+" ORDER BY [PubDate] DESC " ;
                SqlDataAdapter ad = new SqlDataAdapter();
                ad.SelectCommand = ccmd;

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

            }

#1