MS Access SQL > ASP SQL problem....

[david]

Hi,

I have the following code which returns an error when run as part of my
ASP SQL....

strquery = strquery & "FROM (Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER
JOIN (OrderLines INNER JOIN StockMovements ON OrderLines.JobNumber =
StockMovements.JobNumber) ON (Products.ProductID =
OrderLines.ProductID) AND (Products.ProductID = OrderLines.ProductID))
ON Orders.OrderID = OrderLines.OrderID"

I have copied this direct from MS Access Query SQL. How would I adapt
this code to work in ASP ?


Appreciate your help


David

 

[Bob Barrows [MVP]]

Hi,

I have the following code which returns an error

What error?

when run as part of
my ASP SQL....

strquery = strquery & "FROM (Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER
JOIN (OrderLines INNER JOIN StockMovements ON OrderLines.JobNumber =
StockMovements.JobNumber) ON (Products.ProductID =
OrderLines.ProductID) AND (Products.ProductID = OrderLines.ProductID))
ON Orders.OrderID = OrderLines.OrderID"

I have copied this direct from MS Access Query SQL. How would I adapt
this code to work in ASP ?

The only way to debug a sql statement is to know what it is. Do

Response.Write strquery

to see what your vbscript code has generated. If it is correct you should be
able to copy and paste it from the browser window into the SQL View of an
Access Query Builder and run it without modification (unless wildcards are
involved).

You would be better off executing the saved query directly instead of
dealing with all the dynamic sql nonsense:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/7d6e8544e2fd6889?oe=UTF-8

Bob Barrows

 

[Aaron [SQL Server MVP]]

All those parentheses are causing the SQL interpreter problems. You also
need an ON after each INNER JOIN... you can't say INNER JOIN INNER JOIN ON
ON. Gawd, Access teaches some bad habits. Try this:

FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
INNER JOIN OrderLines oL
ON oL.OrderID = oL.OrderID
INNER JOIN Products p
ON oL.ProductID = p.ProductID
INNER JOIN StockMovements s
ON oL.JobNumber = s.JobNumber

 

[David Gordon]

Bob,

Great to hear from you again. At least I know I can count on you for
great help.

I tried response.write...not much help really.
It would be much easier for me to run a query which is already saved in
th MS Access Back End and output the results direct into ASP.

My Query is called RemainderStatusBBUK

I already have an include file for the connection to the database:

_______________________________________
strConnection = "driver=
{MySQL};server=xx.xxx.xx.x;uid=xxxxxx;pwd=xxxxx;database=xxxxx"

Set adoDataConn = Server.CreateObject("ADODB.Connection")

adoDataConn.Open strConnection
________________________________________

I then tried the following in my page:

conn.RemainderStatusBBUK JobNumber, PONumber

'''JobNumber & PONumber are two fields in my Query

set rs = server.createobject("adodb.recordset")
conn.qGetRecords parm1,parm2, rs


How do I define Conn or what do I replace it with ?


Thanks


David

 

[Aaron [SQL Server MVP]]

conn is the de facto standard name for a connection object. You called your
connection object "adoDataConn" so you either need to change the definition
or change the references.

 

[Bob Barrows [MVP]]

Bob,

Great to hear from you again. At least I know I can count on you for
great help.

I tried response.write...not much help really.

It should have been, but you seem to be taking the proper course below, so I
won't pursue it.

It would be much easier for me to run a query which is already saved
in th MS Access Back End and output the results direct into ASP.

My Query is called RemainderStatusBBUK

I already have an include file for the connection to the database:

_______________________________________
strConnection = "driver=
{MySQL};server=xx.xxx.xx.x;uid=xxxxxx;pwd=xxxxx;database=xxxxx"

You need to use the native OLE DB Provider for Jet in order to treat saved
queries as stored procedures. Unless your database is protected by workgroup
security (if you don't know what workgroup security is then it isn't) you do
NOT supply a username and password in the connection string or in the open
statement. The string should look like:

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=p:\ath\to\database.mdb"

The path to the database must be supplied as a physical file path. You can
use Server.MapPath to get the correct path if needed:
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("database.mdb")

I then tried the following in my page:

conn.RemainderStatusBBUK JobNumber, PONumber

'''JobNumber & PONumber are two fields in my Query

Fields? They need to be parameters. Go back and reread the link I showed you
in the last message.

Here's another one to look at, as well:
http://groups-beta.google.com/group.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8

It got truncated, but at least the part where I explained about parameters
is still there.

Bob Barrows

 

[Bob Barrows [MVP]]

All those parentheses are causing the SQL interpreter problems.

I hate it myself, but unfortunately, Jet requires multiple joins to be
nested with parentheses. It makes for very messy, hard-to-read sql, but
without them, Jet barfs. That's why i always recommend the use of the query
builder to build queries involving 3 or more tables.

Bob Barrows

 

[io]

It would be much easier for me to run a query which is already saved in

th MS Access Back End and output the results direct into ASP.

My Query is called RemainderStatusBBUK

I already have an include file for the connection to the database:

_______________________________________
strConnection = "driver=
{MySQL};server=xx.xxx.xx.x;uid=xxxxxx;pwd=xxxxx;database=xxxxx"

Set adoDataConn = Server.CreateObject("ADODB.Connection")

adoDataConn.Open strConnection

Judging by the connection string you are attempting to connect to MySQL
database, not MS Access.

I then tried the following in my page:

conn.RemainderStatusBBUK JobNumber, PONumber

I doubt ADO connection object has ever had method *RemainderStatusBBUK*

'''JobNumber & PONumber are two fields in my Query

set rs = server.createobject("adodb.recordset")
conn.qGetRecords parm1,parm2, rs

Again, never seen *qGetRecords* as a ADO connection method

How do I define Conn or what do I replace it with ?

You either failed to explain yourself clearly or (the worst case scenario)
do not understand what you do. In either case no one will be able to help
you unless they have a clear picture of what needs to be done. Try not to
get into technicalities straight away, but rather explain the bigger
picture. Are you migrating from MS Access to MySQL with ASP?

 

[io]

I doubt ADO connection object has ever had method *RemainderStatusBBUK*

Oh, dear... That's an equivalent to a DAO Database object where all
tables/queries become exposed upon a connection! How could I forget!

Again, never seen *qGetRecords* as a ADO connection method

See comment above

 

[Bob Barrows [MVP]]

I hate it myself, but unfortunately, Jet requires multiple joins to be
nested with parentheses. It makes for very messy, hard-to-read sql,
but without them, Jet barfs. That's why i always recommend the use of
the query builder to build queries involving 3 or more tables.

Oh Damn. Ignore this. I just realized that to OP was using MySQL, not
Access!

 

[Bob Barrows [MVP]]

David Gordon wrote:

You need to use the native OLE DB Provider for Jet in order to treat
saved
queries as stored procedures.

Ignore this. I failed to realize that you were using MySQL. i think you
would be better off finding a NySQL group or forum to figure out your
problem. Remember: JetSQL syntax is unlikely to translate well into MySQL
sql syntax, especially if your query utilizes VBA functions.

Bob BArrorw