469,573 Members | 1,655 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,573 developers. It's quick & easy.

Dates in ASP SQL Select Statement....

Hi,

I have part of my SQL statement in my asp page as follows:

WHERE ((pcbforecast.ShipETA < '31/12/2005') and
(products.BBProductName = .......

The problem I am having is that I want the selected records only with
a ShipETA less than 31/12/2005.

My asp pages are running via MySQL on our Windows Server. I have tried
using # and various other ways, but the records listed either ignore
the date or throw an error or show no records. There are records.

__________________________________________

The full SQL Statement is:

strQuery = "SELECT customers.CustomerName,orders.PONumber,orders.Orde rDate,orderlines.OrderQuantity,
orderlines.Selectall, orderlines.OrderfromSD, orderlines.JobNumber,
orderlines.EntryDate , Sum(pcbforecast.ShipQty) as Total,
pcbforecast.ShipQty, pcbforecast.ShipETA,products.BBProductName,
products.ProductName FROM customers"
strQuery = strQuery & " INNER JOIN orders ON (customers.CustomerID =
orders.CustomerID) INNER JOIN orderlines ON (orders.OrderID =
orderlines.OrderID) INNER JOIN products ON (orderlines.ProductID =
products.ProductID) INNER JOIN pcbforecast ON (orderlines.JobNumber =
pcbforecast.JobNumber)"
strQuery = strQuery & " WHERE ((pcbforecast.ShipETA < '31/12/2005')
and (products.BBProductName = '" & ProductRequest & "') and
(customers.CustomerName = 'SD UK'))"
strQuery = strQuery & " GROUP BY
customers.CustomerName,orders.PONumber,orders.Orde rDate,orderlines.OrderQuantity,
orderlines.Selectall, orderlines.OrderfromSD, orderlines.JobNumber,
orderlines.EntryDate ,pcbforecast.ShipQty,
pcbforecast.ShipETA,products.BBProductName, products.ProductName;"

__________________________________________________ ________

Where am I going wrong ?

Appreciate your help

Thanks
David
Jul 22 '05 #1
2 2017
David wrote:
Hi,

I have part of my SQL statement in my asp page as follows:

WHERE ((pcbforecast.ShipETA < '31/12/2005') and
(products.BBProductName = .......
Doesn't MySQL have a native query execution tool which you can use to design
and debug your queries? If not, that's a glaring omission.

I really do not know how dates are supposed to be delimited in MySQL. I
suspect it's quotes, but I may be wrong.. The other issue is the ambiguous
date format you are using to specify the date. You should go look at the
MySQLL documentation and verify whether it will accept dates in a standard
format, such as ISO: yyyymmdd or yyyy-mm-dd.

The problem I am having is that I want the selected records only with
a ShipETA less than 31/12/2005.

My asp pages are running via MySQL on our Windows Server. I have tried
using # and various other ways, but the records listed either ignore
the date or throw an error or show no records. There are records.

__________________________________________

The full SQL Statement is:

strQuery = "SELECT <snip> strQuery = strQuery & " WHERE ((pcbforecast.ShipETA < '31/12/2005')
and (products.BBProductName = '" & ProductRequest & "') and
(customers.CustomerName = 'SD UK'))"
<snip>
Where am I going wrong ?


Using dynamic SQL is your first mistake. Correct that, and the other
mistakes will disappear. Try this:

strQuery="SELECT ... "
strQuery = strQuery & " WHERE ((pcbforecast.ShipETA < ?) " & _
"and (products.BBProductName = ?) " & _
"and (customers.CustomerName = 'SD UK'))"
strQuery = strQuery & " GROUP BY ... "

dim cmd,arParms
arParms=array(#2005-12-31#, ProductRequest)
set cmd=createobject("adodb.command")
cmd.CommandText=strQuery
set cmd.ActiveConnection = conn
'or whatever your connection variable is named
set rs=cmd.execute(,arParms,1)
if not rs.eof then
'you have records, continue
else
'handle the empty recordset situation
end if

The question marks are called "parameter placeholders" and will be replaced
with the values in arParms when the command is executed.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #2
Just Curious:

Is the field Classified as a date or string?

'dlbjr
'Pleading sagacious indoctrination!
Jul 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by brett | last post: by
1 post views Thread by I am Sam | last post: by
2 posts views Thread by patang | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.