find order by date range or order id

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @.Date_ordered OR @.Date_ordered IS NULL) AND ([Date_ordered] <= @.Date_ordered2 OR @.Date_ordered2 IS NULL OR (Order_ID=ISNULL(@.OrderID_ID,Order_ID) OR @.Order_ID IS NULL))">

but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!

Thanks

Jez

If you use SqlDataSource, you can add this to the SqlDataSource:CancelSelectOnNullParameter="false"

And change your SelectCommand to:

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order] WHERE [Date_ordered] >= ISNULL(@.Date_ordered, Date_ordered) AND ([Date_ordered] <= ISNULL(@.Date_ordered2, Date_ordered) AND Order_ID=ISNULL(@.OrderID_ID,Order_ID)">

|||

hi thanks for the code, right near the end you put OrderID_ID instead of Order_ID, and missed one ) at the end, but thank you very much for your help!

Jez

|||

Thank you for catching the typos. Glad that you got the idea.

|||

hey i just wondered if i could pick your brains again quickly, i wanted to have the same statement, but also see if it would also take input from a querystring, so could i just do the same thing, put all the WHERE clause in brackets, put a "OR" then just put where order_ID = ? and in the select perameters just have where ? = querystringorderID ?

Jez