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

Question

我试图在我的sql Statment中找到语法错误，但是我无法尝试添加（）和[]，但是它没有任何变化，所以当我在我的情况下，它可以帮助我解决这个错误正在收到此错误消息："关键字附近的不正确语法"one_answers"。"旁边：" 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();
            }

第二代码是

 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();
            }
    }

Answer 1

您的第一个片段中的这一行似乎是您的问题：

ccmd.CommandText = CarSqlST + condition;

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

Answer 2

尝试此

    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();
            }