sqldatasource selectcommand包含零值

Question

我在服务器上运行的SQL代码。

SELECT 
    [LINE_NO], [CUST_ORDER_ID], [PART_ID], 
    [CUSTOMER_PART_ID], [MISC_REFERENCE], [PROMISE_DATE], 
    [PROMISE_DEL_DATE] 
FROM 
    [CUST_ORDER_LINE] 
WHERE 
    ([CUST_ORDER_ID] = '33742-1' 
     AND [PROMISE_DATE] IS NULL
     AND [PROMISE_DEL_DATE] IS NULL)

我的asp.net代码。

SelectCommand = "SELECT [LINE_NO], [CUST_ORDER_ID], [PART_ID], [CUSTOMER_PART_ID], [MISC_REFERENCE], [PROMISE_DATE], [PROMISE_DEL_DATE] FROM [CUST_ORDER_LINE] WHERE ([CUST_ORDER_ID] = ? AND [PROMISE_DATE] = ? AND [PROMISE_DEL_DATE] = ?)"

我正在使用3个查询字符串参数。承诺日期和承诺交付日期可能为null。但是，当这些值为null时，它不会返回记录。

如何对此进行编程以将SQL更改为'is null'而不是= ''。

Answer 1

我认为这就是您想要的：

• param 1 = Orderid
• 参数2/3都是承诺日期
• 参数4/5都是承诺交付日期

代码：

SelectCommand = "SELECT [LINE_NO], [CUST_ORDER_ID], [PART_ID], [CUSTOMER_PART_ID], [MISC_REFERENCE], [PROMISE_DATE], [PROMISE_DEL_DATE] FROM [CUST_ORDER_LINE] WHERE [CUST_ORDER_ID] = ? AND (? IS NULL OR [PROMISE_DATE] = ?) AND (? IS NULL OR [PROMISE_DEL_DATE] = ?)"

SQL将是：

DECLARE @P1 INT, @P2 DATETIME, @P3 DATETIME
SELECT [LINE_NO], [CUST_ORDER_ID], [PART_ID], [CUSTOMER_PART_ID], [MISC_REFERENCE], [PROMISE_DATE], [PROMISE_DEL_DATE] 
FROM [CUST_ORDER_LINE] 
WHERE [CUST_ORDER_ID] = @P1 
AND (@P2 IS NULL OR [PROMISE_DATE] = @P2)
AND (@P3 IS NULL OR [PROMISE_DEL_DATE] = @P3)