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