using (SqlConnection sqlcon = new SqlConnection(connectiondb))
{
sqlcon.Open();
SqlDataAdapter sqlda = new SqlDataAdapter("SELECT c.CaseKey, c.CaseCode, ct.Name, c.CaseName, c.CaseDetail, c.CaseDT, c.Requester, u.DeptName, u.Tel, cs.Name AS Expr1, ic.StaffName, ic.DateHope, ic.Solve, ic.Dates, ic.ExpectedDT FROM [Case] AS c LEFT OUTER JOIN CaseType AS ct ON c.CaseTypeKey = ct.CaseTypeKey LEFT JOIN Usr AS u ON c.UsrKey = u.UsrKey LEFT JOIN CaseStatus AS cs ON c.CaseStatusKey = cs.CaseStatusKey LEFT OUTER JOIN ItCase AS ic ON c.CaseKey = ic.CaseKey Where u.UsrKey like '%'+'" + DropDownList2.Text.Trim() + "'+'%' and ic.StaffName like '%'+'" + DropDownList3.Text.Trim()+ "'+'%' and cs.Code like '%'+'" + DropDownList1.Text.Trim() + "'+'%' and c.CaseDT BETWEEN '" + TextBox2.Text + "' AND '" + TextBox3.Text + "' ", sqlcon);
DataTable aaa = new DataTable();
sqlda.Fill(aaa);
GridView1.DataSource = aaa;
GridView1.DataBind();
}
Where u.UsrKey like '%'+'" + DropDownList2.Text.Trim() + "'+'%'
แก้เป็น
Where u.UsrKey like '%" + DropDownList2.Text.Trim() + "%'
หรือ
Where u.UsrKey like concat('%','" + DropDownList2.Text.Trim() + "','%')
ผมไม่แน่ใจว่า mssql server เวอร์ชั่นไหน เลยนิยมที่ต่อ string ด้วยคำสั่ง concat แม้จะต่าง type ก็ไม่ error
ตัวอย่างโค๊ด c# Code (C#)
string _strSql = "SELECT c.CaseKey, c.CaseCode, ct.Name, c.CaseName, c.CaseDetail, c.CaseDT, c.Requester, u.DeptName"
+ ", u.Tel, cs.Name AS Expr1, ic.StaffName, ic.DateHope, ic.Solve, ic.Dates, ic.ExpectedDT "
+ "FROM [Case] AS c "
+ "LEFT OUTER JOIN CaseType AS ct ON c.CaseTypeKey = ct.CaseTypeKey "
+ "LEFT JOIN Usr AS u ON c.UsrKey = u.UsrKey "
+ "LEFT JOIN CaseStatus AS cs ON c.CaseStatusKey = cs.CaseStatusKey "
+ "LEFT OUTER JOIN ItCase AS ic ON c.CaseKey = ic.CaseKey "
+ "Where u.UsrKey like '%" + DropDownList2.Text.Trim() + "%' "
+ "and ic.StaffName like '%" + DropDownList3.Text.Trim()+ "%' "
+ "and cs.Code like '%" + DropDownList1.Text.Trim() + "%' "
+ "and c.CaseDT BETWEEN '" + TextBox2.Text + "' AND '" + TextBox3.Text + "' ", sqlcon);
DataTable _dt;
using (SqlConnection sqlcon = new SqlConnection(connectiondb))
{
sqlcon.Open();
SqlCommand _cmd = sqlcon.CreateCommand();
_cmd.CommandText = _strSql;
SqlDataAdapter _da = new SqlDataAdapter(_cmd);
DataSet _ds = new DataSet();
_da.Fill(_ds, "Case");
sqlcon.Close();
_dt = _ds.Tables["Case"];
}
GridView1.DataSource = _dt;
//GridView1.DataBind(); // ตรงนี้ไม่เคยใช้
Load balance : Server 03
