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_Selector INNER JOIN ((Orders
INNER JOIN (StockMovements INNER JOIN OrderLines ON
StockMovements.JobNumber = OrderLines.JobNumber) ON Orders.OrderID =
OrderLines.OrderID) INNER JOIN Products ON OrderLines.ProductID =
Products.ProductID) ON Schedule_Selector.ScheduleID =
Products.ScheduleID"

___________________________________

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]

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_Selector INNER JOIN ((Orders
INNER JOIN (StockMovements INNER JOIN OrderLines ON
StockMovements.JobNumber = OrderLines.JobNumber) ON Orders.OrderID =
OrderLines.OrderID) INNER JOIN Products ON OrderLines.ProductID =
Products.ProductID) ON Schedule_Selector.ScheduleID =
Products.ScheduleID"

___________________________________

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.createobject("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

 

[David Gordon]

Bob,

Thanks for your post earlier,

I have narrowed down my asp SQL String to:

________________________________________________________

strquery="SELECT ScheduleSelector.BBProductName,
ScheduleSelector.Quantity, ScheduleSelector.ShipDate,
ScheduleSelector.PO, ScheduleSelector.Notes, ScheduleSelector.Job,
ScheduleSelector.EntryDate, StockMovements.FirstSerial,
StockMovements.LastSerial"

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

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

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

strquery = strquery & "ORDER BY ScheduleSelectorShipDate,
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 (ScheduleSelector.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

 

[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 ScheduleSelector.BBProductName,
ScheduleSelector.Quantity, ScheduleSelector.ShipDate,
ScheduleSelector.PO, ScheduleSelector.Notes, ScheduleSelector.Job,
ScheduleSelector.EntryDate, StockMovements.FirstSerial,
StockMovements.LastSerialFROM ScheduleSelector INNER JOIN StockMovements
ON ScheduleSelector.Job = StockMovements.JobNumberGROUP BY
ScheduleSelector.BBProductName,
ScheduleSelector.Quantity, ScheduleSelector.ShipDate,
ScheduleSelector.PO, ScheduleSelector.Notes, ScheduleSelector.Job,
ScheduleSelector.EntryDate, StockMovements.FirstSerial,
StockMovements.LastSerialHAVING ShipDate >= DATE_ADD(CURDATE(), INTERVAL
-7 DAY)ORDER BY ScheduleSelectorShipDate,
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="SELECT ScheduleSelector.BBProductName,
ScheduleSelector.Quantity, ScheduleSelector.ShipDate,
ScheduleSelector.PO, ScheduleSelector.Notes, ScheduleSelector.Job,
ScheduleSelector.EntryDate, StockMovements.FirstSerial,
StockMovements.LastSerial"

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

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

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

strquery = strquery & " ORDER BY ScheduleSelectorShipDate,
BBProductName"

 

[David Gordon]

Stewart,

Thanks for that....

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


Thanks again



David