c# - ADO.net BETWEEN 奇怪的行为

Question

我不确定这段代码有什么问题...

else if (combo_View.Text == "Orders")
{

    da.SelectCommand = new OleDbCommand("SELECT * FROM TestQuery WHERE (VendorName = @VendorName OR @VendorName = '') AND (CustomerName = @CustomerName OR @CustomerName = '') AND (PO = @PO OR @PO = '')  AND (ItemNum = @ItemNum OR @ItemNum = '') AND (orderDate BETWEEN @From AND @To) ORDER BY CustomerName", cs);

    //da.SelectCommand = new OleDbCommand("SELECT * FROM TestQuery WHERE orderDate BETWEEN @From AND @To", cs);

    da.SelectCommand.Parameters.Add("@VendorName", OleDbType.VarChar).Value = combo_VendorView.Text.ToString();
    da.SelectCommand.Parameters.Add("@CustomerName", OleDbType.VarChar).Value = combo_CustomerView.Text.ToString();

    da.SelectCommand.Parameters.Add("@From", OleDbType.Date).Value = "#" + tp_viewFrom.Value.Date.ToString("M/d/yyyy") + "#";
    da.SelectCommand.Parameters.Add("@To", OleDbType.Date).Value = "#" + tp_viewTo.Value.Date.ToString("M/d/yyyy") + "#";

    da.SelectCommand.Parameters.Add("@PO", OleDbType.VarChar).Value = txt_POLookup.Text.ToString();
    da.SelectCommand.Parameters.Add("@ItemNum", OleDbType.VarChar).Value = combo_ItemNumLookup.Text.ToString();


    dsB.Clear();
    da.Fill(dsB);
    dgv_DataLookup.DataSource = dsB.Tables[0];
}

基本上，我想用 between 语句填充数据网格。 For the uncommented SelectCommand, I appear to get no values on my datagrid when a select valid dates.但是，当我在那之后取出 select 命令的注释时，它只在它的日期有效。当我有其他参数时，谁能告诉我第一个命令有什么问题？谢谢

编辑:

更奇怪的是，当我使用这个选择命令时:

da.SelectCommand = new OleDbCommand("SELECT * FROM TestQuery WHERE
    (VendorName = @VendorName OR @VendorName = '')
    AND (CustomerName = @CustomerName OR @CustomerName = '')
    AND orderDate BETWEEN @From AND @To ORDER BY CustomerName", cs);

它可以工作，但我向它添加更多内容后它就停止了...

Answer 1

如果 between 子句中的任何数据值或参数本身为 null，则 between 将始终为 false。测试 @from 和 @to 上的空值，并将两者之间的值放在 or 中。

and (@from is null or @to is null or <between stmt> )