current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote 0 down vote favorite

My SQL code that works running on the server.

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)

My asp.net code.

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] = ?)"

I'm using 3 query string parameters. The promise date and promise delivery date can be null. But when those values are null it returns no records.

How can I program this to change the SQL to 'is null' instead of = ''.

share|improve this question
up vote 0 down vote

I think this is what you want:

  • Param 1 = OrderID
  • Param 2/3 are both Promise Date
  • Param 4/5 are both Promise Delivery Date

Code:

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] = ?)"

The SQL would be:

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)
share|improve this answer
    
Thank you so much that works great. I'm new to asp.net so I didn't make the connection between the extra parameters. Technically speaking the SQL would be AND (@P2 IS NULL OR [PROMISE_DATE] = @P3) AND (@P4 IS NULL OR [PROMISE_DEL_DATE] = @P5) – redhook99 Jun 18 at 18:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.