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]]

da***@scene-double.co.uk wrote:

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/...d6889?oe=UTF-8

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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

--
http://www.aspfaq.com/
(Reverse address to reply.)


<da***@scene-double.co.uk> wrote in message
news:11********************@z14g2000cwz.googlegrou ps.com...

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

[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;da tabase=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


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

[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.

--
http://www.aspfaq.com/
(Reverse address to reply.)


"David Gordon" <da***@scene-double.co.uk> wrote in message
news:O8**************@TK2MSFTNGP15.phx.gbl...

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;da tabase=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


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

[Bob Barrows [MVP]]

David Gordon wrote:

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;da tabase=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/...UTF-8&oe=UTF-8

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

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Aaron [SQL Server MVP] wrote:

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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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;da tabase=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 then tried the following in my page:

conn.RemainderStatusBBUK JobNumber, PONumber

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!

'''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

See comment above

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?

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

Aaron [SQL Server MVP] wrote:

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.

Oh Damn. Ignore this. I just realized that to OP was using MySQL, not
Access!
--
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"

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

David Gordon wrote:

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

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

--
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"