Access SQL to ASP SQL not working ... need help please

[David]

Hi,

I have taken some SQL from MS Access and got it working.
I have since updated the SQL in Access but cannot get the additional
part working in ASP.

The working SQL I have in asp is:-

strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped)>=0) AND
(OrderLines.SelectAll=-1) AND (PCBForecast.HeldMarker='Scheduled'))"

I need to add the following but keep getting errors saying my SQL is
incorrect.

This is the additional part which should also be on this line:-

AND ((PCBForecast.ShipETA) Between Now() And #12/30/3000#))"

or another variant I require

AND ((PCBForecast.ShipETA)=#12/31/3000#))"

What is the correct way to write both of these, as I cannot get either
to work in asp, yet they work in Access.

Apprecaite your advise

Thanks

David

 

[Anthony Jones]

Hi,

I have taken some SQL from MS Access and got it working.
I have since updated the SQL in Access but cannot get the additional
part working in ASP.

The working SQL I have in asp is:-

strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped)>=0) AND
(OrderLines.SelectAll=-1) AND (PCBForecast.HeldMarker='Scheduled'))"

I need to add the following but keep getting errors saying my SQL is
incorrect.

This is the additional part which should also be on this line:-

AND ((PCBForecast.ShipETA) Between Now() And #12/30/3000#))"

or another variant I require

AND ((PCBForecast.ShipETA)=#12/31/3000#))"

What is the correct way to write both of these, as I cannot get either
to work in asp, yet they work in Access.

Apprecaite your advise

The first thing you should do when ever you have a problem like this is to
check the SQL that is actually being executed.

Response.Write strQuery
Response.End

Just before you would normally exectute it. Check that the SQL is what you
expect it to be.

Another useful piece of advise would be specific! "I cannot get either to
work" tells us nothing.

Did it ignore your changes? Produce the wrong results?
Generate an error? If so what did it say? I dunno but it might be a clue

There is a space before the AND right?

 

[Bob Barrows [MVP]]

Hi,

I have taken some SQL from MS Access and got it working.
I have since updated the SQL in Access but cannot get the additional
part working in ASP.

The working SQL I have in asp is:-

strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped)>=0) AND
(OrderLines.SelectAll=-1) AND (PCBForecast.HeldMarker='Scheduled'))"

I need to add the following but keep getting errors saying my SQL is
incorrect.

This is the additional part which should also be on this line:-

AND ((PCBForecast.ShipETA) Between Now() And #12/30/3000#))"

or another variant I require

AND ((PCBForecast.ShipETA)=#12/31/3000#))"

What is the correct way to write both of these, as I cannot get either
to work in asp, yet they work in Access.

Two things:
1. Datetime literals in SQL Server are delimited by quotes ('), not
hashmarks (#).
2. There are no VBA functions such as Now() available in SQL Server. The
variant of sql used by SQL Server is called Transact-SQL (sometimes
called T-SQL for short). Transact-SQL has builtin functions for doing
many of the things VBA functions are used for in Jet. GETDATE() is the
T-SQL equivalent to Now().

You may want to have a look at this article:
http://www.aspfaq.com/show.asp?id=2214

 

[Anthony Jones]

Two things:
1. Datetime literals in SQL Server are delimited by quotes ('), not
hashmarks (#).
2. There are no VBA functions such as Now() available in SQL Server. The
variant of sql used by SQL Server is called Transact-SQL (sometimes
called T-SQL for short). Transact-SQL has builtin functions for doing
many of the things VBA functions are used for in Jet. GETDATE() is the
T-SQL equivalent to Now().

You may want to have a look at this article:
http://www.aspfaq.com/show.asp?id=2214

Did I miss something that points to SQL Server?

 

[Bob Barrows [MVP]]

Did I miss something that points to SQL Server?

Nope. I misread it.

To the OP:
Show us the vbscript code that is failing. Better yet, read these:

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]