Incorrect SQL Syntax ??

[David]

Hi,

I have taken some SQL taken from a query in Access 2000, but I cannot
seem to get it working correctly in my .asp page.
strQuery = strquery & "FROM Schedule_Select or INNER JOIN ((Orders
INNER JOIN (StockMovements INNER JOIN OrderLines ON
StockMovements. JobNumber = OrderLines.JobN umber) ON Orders.OrderID =
OrderLines.Orde rID) INNER JOIN Products ON OrderLines.Prod uctID =
Products.Produc tID) ON Schedule_Select or.ScheduleID =
Products.Schedu leID"

_______________ _______________ _____

I have tried removing the square brackets and adding an '_' into the
table name Schedule Selector, as there is a space in the name .
Please help

Thanks
David

[Bob Barrows]

David wrote:

Hi,

I have taken some SQL taken from a query in Access 2000, but I cannot
seem to get it working correctly in my .asp page.
strQuery = strquery & "FROM Schedule_Select or INNER JOIN ((Orders
INNER JOIN (StockMovements INNER JOIN OrderLines ON
StockMovements. JobNumber = OrderLines.JobN umber) ON Orders.OrderID =
OrderLines.Orde rID) INNER JOIN Products ON OrderLines.Prod uctID =
Products.Produc tID) ON Schedule_Select or.ScheduleID =
Products.Schedu leID"

_______________ _______________ _____

I have tried removing the square brackets
That was your first mistake
and adding an '_' into the
That was your second mistake. How do you expect Jet to find a table that
does not exist?
table name Schedule Selector, as there is a space in the name .

That was you third mistake (actually, chronologically , this was your first
mistake). Just because Access allows you to get away eith using spaces in
your object names does not mean that you should. Using non-standard
characters (such as spaces) in object names forces you to use workarounds
such as putting brackets [] around the names of those objects. I suggest
renaming the table in Access to get rid of the space. If you can't do that
for some reason, then make sure you surround the name with brackets.

I do not think we have gotten to the actual cause of your problem. The
problem for us is that "...cannot
seem to get it working correctly ..." is not an error message that either
vbscript or Jet will ever generate ;-) .

You need to:
1) tell us what "not working" means - error message? wrong results?
2) show us the entire sql statement that is being sent to the database. Use

Response.Write strQuery

to cause the query to be written to the browser window. This will allow you
to verify that you've done all your concatenation correctly and that you
have a valid sql statement that can be copied and pasted from the browser
window into the SQL View of the Access Query Builder where it can be run wit
hout modification.

3) show us the code used to run the query

My ultimate suggestion is to stop trying to recreate the sql in vbscript.
Simply save your query in Access and run the saved query from asp. For
example, suppose you save the query with the name "MyQuery". I assume that
it returns records so do this in vbscript:

dim cn, rs
'create and open the connection using cn, then:
set rs=server.creat eobject("adodb. recordset")
cn.MyQuery rs
if not rs.eof then
....

If your query does not return records, just use:

cn.MyQuery

If you need to pass parameters to the query .. I'm out of time. Do a Google
search on this newsgroup (and .asp.db) for "saved parameter query" - I've
posted many examples.

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"

[David Gordon]

Bob,

Thanks for your post earlier,

I have narrowed down my asp SQL String to:

_______________ _______________ _______________ ___________

strquery="SELEC T ScheduleSelecto r.BBProductName ,
ScheduleSelecto r.Quantity, ScheduleSelecto r.ShipDate,
ScheduleSelecto r.PO, ScheduleSelecto r.Notes, ScheduleSelecto r.Job,
ScheduleSelecto r.EntryDate, StockMovements. FirstSerial,
StockMovements. LastSerial"

strquery = strquery & "FROM ScheduleSelecto r INNER JOIN StockMovements
ON ScheduleSelecto r.Job = StockMovements. JobNumber"

strquery = strquery & "GROUP BY ScheduleSelecto r.BBProductName ,
ScheduleSelecto r.Quantity, ScheduleSelecto r.ShipDate,
ScheduleSelecto r.PO, ScheduleSelecto r.Notes, ScheduleSelecto r.Job,
ScheduleSelecto r.EntryDate, StockMovements. FirstSerial,
StockMovements. LastSerial"

strquery = strquery & "HAVING ShipDate >= DATE_ADD(CURDAT E(), INTERVAL
-7 DAY)"

strquery = strquery & "ORDER BY ScheduleSelecto rShipDate,
BBProductName;"

_______________ _______________ _______________ ___
The error the browser throws back is:
ADODB.Recordset .1 error '80004005'

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'INNER JOIN StockMovements ON (ScheduleSelect or.Job = StockMovem

_______________ _______________ _______________ _____

This query works in MS Access, but my .asp pages run from a MySQL DB
running on our Cobalt Qube server, so I cannot call the query in access
directly. It has to be written in SQL as you know.

Any ideas what I am doing wrong ?
Thanks
David


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[stewert gallington]

Now i could be wrong, but i've had this problem before when using
concatenated strings to build my query string.

You have to be aware of spaces.

For example.

If you where to response.write strquery you would find it reads thus

SELECT ScheduleSelecto r.BBProductName ,
ScheduleSelecto r.Quantity, ScheduleSelecto r.ShipDate,
ScheduleSelecto r.PO, ScheduleSelecto r.Notes, ScheduleSelecto r.Job,
ScheduleSelecto r.EntryDate, StockMovements. FirstSerial,
StockMovements. LastSerialFROM ScheduleSelecto r INNER JOIN StockMovements
ON ScheduleSelecto r.Job = StockMovements. JobNumberGROUP BY
ScheduleSelecto r.BBProductName ,
ScheduleSelecto r.Quantity, ScheduleSelecto r.ShipDate,
ScheduleSelecto r.PO, ScheduleSelecto r.Notes, ScheduleSelecto r.Job,
ScheduleSelecto r.EntryDate, StockMovements. FirstSerial,
StockMovements. LastSerialHAVIN G ShipDate >= DATE_ADD(CURDAT E(), INTERVAL
-7 DAY)ORDER BY ScheduleSelecto rShipDate,
BBProductName;

Now, looking closely you can see that the 'FROM' statement is now part of
the last field LastSerial.

Change your code to the following and it should work.

strquery="SELEC T ScheduleSelecto r.BBProductName ,
ScheduleSelecto r.Quantity, ScheduleSelecto r.ShipDate,
ScheduleSelecto r.PO, ScheduleSelecto r.Notes, ScheduleSelecto r.Job,
ScheduleSelecto r.EntryDate, StockMovements. FirstSerial,
StockMovements. LastSerial"

strquery = strquery & " FROM ScheduleSelecto r INNER JOIN StockMovements
ON ScheduleSelecto r.Job = StockMovements. JobNumber"

strquery = strquery & " GROUP BY ScheduleSelecto r.BBProductName ,
ScheduleSelecto r.Quantity, ScheduleSelecto r.ShipDate,
ScheduleSelecto r.PO, ScheduleSelecto r.Notes, ScheduleSelecto r.Job,
ScheduleSelecto r.EntryDate, StockMovements. FirstSerial,
StockMovements. LastSerial"

strquery = strquery & " HAVING ShipDate >= DATE_ADD(CURDAT E(), INTERVAL
-7 DAY)"

strquery = strquery & " ORDER BY ScheduleSelecto rShipDate,
BBProductName"

[David Gordon]

Stewart,

Thanks for that....

This now takes a long time to run and throws up some record lines as
duplicates...ba ck to Access to see what is up.
Thanks again

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!