MS Access SQL > ASP SQL problem....

Discussion in 'ASP General' started by david@scene-double.co.uk, Jan 5, 2005.

  1. Guest

    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
    , Jan 5, 2005
    #1
    1. Advertising

  2. 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/microsoft.public.inetserver.asp.db/msg/7d6e8544e2fd6889?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.
    Bob Barrows [MVP], Jan 5, 2005
    #2
    1. Advertising

  3. 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.)




    <> wrote in message
    news:...
    > 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
    >
    Aaron [SQL Server MVP], Jan 5, 2005
    #3
  4. David Gordon Guest

    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








    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    David Gordon, Jan 5, 2005
    #4
  5. 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" <> wrote in message
    news:...
    > 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
    >
    >
    >
    >
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    Aaron [SQL Server MVP], Jan 5, 2005
    #5
  6. 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;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
    --
    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], Jan 5, 2005
    #6
  7. 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.
    Bob Barrows [MVP], Jan 5, 2005
    #7
  8. io Guest

    > 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, Jan 6, 2005
    #8
  9. io Guest


    > > 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?
    >
    >
    >
    io, Jan 6, 2005
    #9
  10. 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], Jan 6, 2005
    #10
  11. Bob Barrows [MVP] wrote:
    > David Gordon wrote:


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


    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"
    Bob Barrows [MVP], Jan 6, 2005
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Vaap
    Replies:
    2
    Views:
    1,169
  2. cooldv
    Replies:
    3
    Views:
    271
    WIlliam Morris
    Oct 6, 2003
  3. Jaunty Edward

    MS SQL to MS Access ASP problem

    Jaunty Edward, Sep 7, 2004, in forum: ASP General
    Replies:
    2
    Views:
    111
    Bob Barrows [MVP]
    Sep 7, 2004
  4. Micromanaged

    ASP and Access SQL Problem

    Micromanaged, Oct 7, 2004, in forum: ASP General
    Replies:
    3
    Views:
    162
    Stephanie Stowe
    Oct 8, 2004
  5. David
    Replies:
    4
    Views:
    151
    Bob Barrows [MVP]
    Sep 28, 2006
Loading...

Share This Page