請問一下各位大神
為什麼一定要輸入滿所有條件才會有資料顯示
我想要做1~5筆資料搜尋
SQL語法單獨測試是沒問題的
我想問題應該在ASP.NET上
一開始以為TextBox沒輸入時,值不等於null
後來我把空值全部改為null
在"更新查詢資料"按鈕上給語法
protected void Button5_Click1(object sender, GridViewRowEventArgs e)
{
if (String.IsNullOrEmpty(TextBox1.Text))
{
TextBox1.Text = null;
}
if (String.IsNullOrEmpty(TextBox3.Text))
{
TextBox3.Text = null;
}
if (String.IsNullOrEmpty(TextBox5.Text))
{
TextBox5.Text = null;
}
if (String.IsNullOrEmpty(TextBox7.Text))
{
TextBox7.Text = null;
}
}
或直接全部改成空值SQL也不認得
protected void Button5_Click1(object sender, GridViewRowEventArgs e)
{
TextBox1.Text = null;
TextBox3.Text = null;
TextBox5.Text = null;
TextBox7.Text = null;
TextBox9.Text = null;
TextBox10.Text = null;
我畫面上有
TextBox1,TextBox3,TextBox5,TextBox7,TextBox9,TextBox10
(其他TextBox沒用到直接略過)
有GridView1,GridView2,GridView3,GridView4,GridView5
各別顯示(當GridView1顯示時,其他GridView隱藏)
(GridView2~5不是本次問題請直接略過)
SQL已經有設定,語法如下
declare @AssetNum Varchar(12) = @TextBox1
, @sDate date = @TextBox9
, @eDate date = @TextBox10
, @ClassNum Varchar(12) = @TextBox3
, @VendorNum varchar(12) = @TextBox5
, @EmpNum varchar(6) = @TextBox7
select * from vwRTRdetails
where (@AssetNum is null or Asset_Num = @AssetNum)
and (@sDate is null or RTR_Date >= @sDate)
and (@eDate is null or RTR_Date <= @eDate)
and (@ClassNum is null or Class_Num = @ClassNum)
and (@VendorNum is null or Vendor_Num = @VendorNum)
and (@EmpNum is null or Emp_Num = @EmpNum)
--這邊將is null 改成 ='' 放到asp.net結果是一樣的
上述語法直接把@TextBox1,@TextBox9,@TextBox3,@TextBox5,@TextBox7
改成null或是欄位上對應的值,是有資料的
我也同意不要讓DB允許NULL,
這樣會比較難處理.
DB都是有值
只是讓使用者可以用多種方式搜尋
比如:
只找資產,其他TextBox都是null
只找廠商,其他TextBox都是null
只找日期範圍,其他TextBox都是null
或是多筆搜尋
資產 + 日期範圍,其他TextBox都是null
資產 + 人員,其他TextBox都是null
資產 + 人員 + 日期範圍,其他TextBox都是null
可以收尋得更精準到他想要的
DB都有值你的SQL就有問題了吧, 你的問題應該是要判斷哪些要搜尋吧...
SqlConnection conn = new SqlConnection(連線字串);
string sql = @"declare @AssetNum Varchar(12) = @TextBox1
, @sDate date = @TextBox9
, @eDate date = @TextBox10
, @ClassNum Varchar(12) = @TextBox3
, @VendorNum varchar(12) = @TextBox5
, @EmpNum varchar(6) = @TextBox7
select * from vwRTRdetails
where 1 = 1"
if(!string.IsNullOrEmpty(TextBox1.Text))
sql += " AND Asset_Num = @AssetNum"
if(!string.IsNullOrEmpty(TextBox3.Text))
sql += " AND RTR_Date >= @sDate"
if(!string.IsNullOrEmpty(TextBox5.Text))
sql += " AND RTR_Date <= @eDate"
if(!string.IsNullOrEmpty(TextBox7.Text))
sql += " AND Class_Num = @ClassNum"
if(!string.IsNullOrEmpty(TextBox9.Text))
sql += " AND Vendor_Num = @VendorNum"
if(!string.IsNullOrEmpty(TextBox10.Text))
sql += " AND Emp_Num = @EmpNum"
SqlCommand cmd = new SqlCommand(strSQL, conn);
if(!string.IsNullOrEmpty(TextBox1.Text))
cmd.Parameters.Add("@AssetNum", SqlDbType.NVarChar).Value = TextBox1.Text;
if(!string.IsNullOrEmpty(TextBox3.Text))
cmd.Parameters.Add("@sDate", SqlDbType.NVarChar).Value = TextBox3.Text;
if(!string.IsNullOrEmpty(TextBox5.Text))
cmd.Parameters.Add("@eDate", SqlDbType.NVarChar).Value = TextBox5.Text;
if(!string.IsNullOrEmpty(TextBox7.Text))
cmd.Parameters.Add("@ClassNum", SqlDbType.NVarChar).Value = TextBox7.Text;
if(!string.IsNullOrEmpty(TextBox9.Text))
cmd.Parameters.Add("@VendorNum", SqlDbType.NVarChar).Value = TextBox9.Text;
if(!string.IsNullOrEmpty(TextBox10.Text))
cmd.Parameters.Add("@EmpNum", SqlDbType.NVarChar).Value = TextBox10.Text;
最後用類似的方法完成,雖然有不小的差異,不過還是感謝提供參考!
string selectStr=@" select * from vwRTRdetails
where";
if (String.IsNullOrEmpty(TextBox1.Text))
{
selectStr+="(@AssetNum is null or Asset_Num = @AssetNum)" ;
}
if (String.IsNullOrEmpty(TextBox3.Text))
{
selectStr+="and (@sDate is null or RTR_Date >= @sDate) ";
}
...
你沒辦法滿足 同時間內 這麼多欄位的值都是 null
改用is not null
declare @AssetNum Varchar(12) = @TextBox1
, @sDate date = @TextBox9
, @eDate date = @TextBox10
, @ClassNum Varchar(12) = @TextBox3
, @VendorNum varchar(12) = @TextBox5
, @EmpNum varchar(6) = @TextBox7
select * from vwRTRdetails
where ((@AssetNum is not null) and (Asset_Num = @AssetNum))
or ((@sDate is not null) and (RTR_Date >= @sDate))
or ((@eDate is not null) and (RTR_Date <= @eDate))
or ((@ClassNum is not null) and (Class_Num = @ClassNum))
or ((@VendorNum is not null) and (Vendor_Num = @VendorNum))
or ((@EmpNum is not null) and (Emp_Num = @EmpNum))