Please check the SQL statement...

[laxatcg1]

Please check the SQL statement given below... am not getting any rows.


SELECT * FROM tblORDERlist
WHERE (Ordercategory = 'C001') AND (Orderdate <= @cdate) AND(Orderdelete <> 1) AND ((Ordercompleteddate = @cdate OR Ordercompleteddate = NULL) AND (Ordercomplete = 1 OR Ordercomplete = 0)) ORDER BY Orderpriority
cmd.Parameters.Add(New SqlClient.SqlParameter("@cdate", Date.Today))

[Shuja Ali]

If it is not giving any error, then I would say that there are no matching rows present in the tblORDERlist for the condition that you have provided.

Did you try executing the same query directly in the database?

[laxatcg1]

Thank you for your reply.

No, it definitely has matching records. I tried directly on the DB too and it is not showing any records. Am sure there is a problem in the SQL statement, may be the syntax is correct but the logic is wrong. Thats where I need your experts help. I tried with too many variations but I couldn't able to succeed.

[Shuja Ali]

Thank you for your reply.

No, it definitely has matching records. I tried directly on the DB too and it is not showing any records. Am sure there is a problem in the SQL statement, may be the syntax is correct but the logic is wrong. Thats where I need your experts help. I tried with too many variations but I couldn't able to succeed.
If it is logic then just showing us the query won't work. You will have to tell us what exactly are you trying to search for.

[laxatcg1]

I think am not clear. The logic what I meant was... is the query itself. Let me explain you briefly.

Earlier I was having my SELECT query like this... and it was working perfect. This displays all the records of which the order is not deleted and not completed and of today's date or lesser and Category 'C001'.

SELECT * FROM tblORDERlist
WHERE (Ordercategory = 'C001') AND (Orderdate <= @cdate) AND(Orderdelete <> 1) AND (Ordercomplete <> 1) ORDER BY Orderpriority
cmd.Parameters.Add(New SqlClient.SqlParameter("@cdate", Date.Today))


Then later on I want to display those orders thats been completed today (Date.Today). So I want to have the same records plus those orders thats been completed today. So I added this part "... ((Ordercompleteddate = @cdate OR Ordercompleteddate = NULL) AND (Ordercomplete = 1 OR Ordercomplete = 0)) ..." And hence I changed the query as given below (the one I sent it earlier)

SELECT * FROM tblORDERlist
WHERE (Ordercategory = 'C001') AND (Orderdate <= @cdate) AND(Orderdelete <> 1) AND ((Ordercompleteddate = @cdate OR Ordercompleteddate = NULL) AND (Ordercomplete = 1 OR Ordercomplete = 0)) ORDER BY Orderpriority
cmd.Parameters.Add(New SqlClient.SqlParameter("@cdate", Date.Today))


Hope I have explained it better.

[Shuja Ali]

When you are checking a column against NULL you should use Is Null. Try this SELECT
*
FROM
tblORDERlist
WHERE
(Ordercategory = 'C001')
AND (Orderdate <= @cdate)
AND (Orderdelete <> 1)
AND (Ordercompleteddate = @cdate OR Ordercompleteddate Is NULL)
AND (Ordercomplete = 1 OR Ordercomplete = 0)
ORDER BY
Orderpriority

Edit--
This thread should be moved to Database Forum.

[HanneSThEGreaT]

[ Moved ]

[jp140768]

Is OrderComplete a boolean / logical etc type field? If so, you shouldn't need the last line of your Where statement


AND (Ordercomplete = 1 OR Ordercomplete = 0)


The other thing, is what is the format of cdate? I'm wondering because if it is a date time field, does it contain the time portion of the date?

i.e. are you looking for all orders completed on 02/04/2007 04:25:58?

What value is stored in the OrderDate field, again does it contain the time?