Dates in ASP SQL Select Statement....

[David]

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.OrderDate,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.OrderDate,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

 

[Bob Barrows [MVP]]

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

strQuery = strQuery & " WHERE ((pcbforecast.ShipETA < '31/12/2005')
and (products.BBProductName = '" & ProductRequest & "') and
(customers.CustomerName = 'SD UK'))"

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

 

[dlbjr]

Just Curious:

Is the field Classified as a date or string?

'dlbjr
'Pleading sagacious indoctrination!