Multiple Queries on same asp page..

Discussion in 'ASP General' started by David, Oct 25, 2004.

  1. David

    David Guest

    Hi,

    I have an ASP page which runs a select statement such as

    QueryA = "SELECT............ ;"

    Set RS_A = adoDataConn.Execute(QueryA)

    (adoDataConn is set in a hidden include file).

    If I run RS_A and pull out the records, I then want to run another
    query on a seperate table but where one field is common.

    QueryB = "SELECT.........;"

    Set RS_B = adoDataConn.Execute(QueryB)


    I am always getting the following error:

    ADODB.Recordset.1 error '80004005'

    SQLState: 42000
    Native Error Code: 1064
    [TCX][MyODBC]You have an error in your SQL syntax near
    'ProductPackREC.JobNumber = 5578' at line 1

    I cannot see any error in my SQL apart from the fact that I am calling
    adoDataConn twice without doing anything in between it ?
    What do I need to do to run both queries happily on the same page ?

    Appreciate your help


    David.
     
    David, Oct 25, 2004
    #1
    1. Advertising

  2. David

    Turkbear Guest

    On 25 Oct 2004 08:27:58 -0700, (David) wrote:

    >Hi,
    >
    >I have an ASP page which runs a select statement such as
    >
    >QueryA = "SELECT............ ;"
    >
    >Set RS_A = adoDataConn.Execute(QueryA)
    >
    >(adoDataConn is set in a hidden include file).
    >
    >If I run RS_A and pull out the records, I then want to run another
    >query on a seperate table but where one field is common.
    >
    >QueryB = "SELECT.........;"
    >
    >Set RS_B = adoDataConn.Execute(QueryB)
    >
    >
    >I am always getting the following error:
    >
    >ADODB.Recordset.1 error '80004005'
    >
    >SQLState: 42000
    >Native Error Code: 1064
    >[TCX][MyODBC]You have an error in your SQL syntax near
    >'ProductPackREC.JobNumber = 5578' at line 1
    >
    >I cannot see any error in my SQL apart from the fact that I am calling
    >adoDataConn twice without doing anything in between it ?
    >What do I need to do to run both queries happily on the same page ?
    >
    >Appreciate your help
    >
    >
    >David.


    Please post the Sql for each statement...Cannot tell what is wrong without seeing it..

    Are you attempting to use a value in RS_A in the where clause of the sql to create RS_B?
     
    Turkbear, Oct 25, 2004
    #2
    1. Advertising

  3. David

    David Gordon Guest

    strQuery works 100%

    ___________________________________

    strQuery = "SELECT OrderLines.PSLrepnotes, OrderLines.OrderNotes,
    Customers.CustomerName, Orders.PONumber, OrderLines.JobNumber,
    OrderLines.OrderQuantity, Products.ProductName, Products.BBProductName,
    OrderLines.HoldonSD, OrderLines.ShipfromPSL "
    strQuery = strQuery & "FROM StockMovements INNER JOIN OrderLines ON
    (StockMovements.JobNumber = OrderLines.JobNumber) INNER JOIN Products ON
    (OrderLines.ProductID = Products.ProductID) INNER JOIN Orders ON
    (OrderLines.OrderID = Orders.OrderID) INNER JOIN Customers ON
    (Orders.CustomerID = Customers.CustomerID) "
    strQuery = strQuery & "WHERE OrderLines.JobNumber = " &
    Session("SerialSearchJob") & ";"

    Set RS = adoDataConn.Execute(strQuery)

    if RS.EOF then ...........

    ____________________________________


    PackQuery = "SELECT ProductPackREC.JobNumber,
    ProductPackREC.packitemNAME, ProductPackREC.packitemDESC,
    ProductPackREC.packitemQTY, ProductPackREC.packitemCODE"
    PackQuery = PackQuery & "FROM ProductPackREC"
    PackQuery = PackQuery & "WHERE ProductPackREC.JobNumber = " &
    Session("SerialSearchJob") & ";"


    Set PackRS = adoDataConn.Execute(PackQuery)

    If PackRS.EOF then .................


    __________________________________

    Do I need to close the connection, then re-open ?

    David


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    David Gordon, Oct 25, 2004
    #3
  4. David

    David Gordon Guest

    Ooops....sorry.
    After trailing my code for half a day, I discovered the error..

    In the SQL statement, if you do not leave a space before the end quotes
    if you are working such as

    QRY = "SELECT......."
    QRY = QRY & "FROM......"

    then it does not recognize the SQL text....DOH...a bloody space for
    hours of debugging....thats programming for you

    Appreciate you looking.

    Thank

    David.




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    David Gordon, Oct 25, 2004
    #4
  5. David

    Mark Schupp Guest

    any time you have problems with a SQL statement

    Response.Write QRY
    Response.End

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com


    "David Gordon" <> wrote in message
    news:%...
    >
    > Ooops....sorry.
    > After trailing my code for half a day, I discovered the error..
    >
    > In the SQL statement, if you do not leave a space before the end quotes
    > if you are working such as
    >
    > QRY = "SELECT......."
    > QRY = QRY & "FROM......"
    >
    > then it does not recognize the SQL text....DOH...a bloody space for
    > hours of debugging....thats programming for you
    >
    > Appreciate you looking.
    >
    > Thank
    >
    > David.
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Mark Schupp, Oct 25, 2004
    #5
  6. David

    caulker Guest

    and if these sql statements were stored procedures, you'd never had this
    problem to begin with ;-)


    "David Gordon" <> wrote in message
    news:%...
    >
    > Ooops....sorry.
    > After trailing my code for half a day, I discovered the error..
    >
    > In the SQL statement, if you do not leave a space before the end quotes
    > if you are working such as
    >
    > QRY = "SELECT......."
    > QRY = QRY & "FROM......"
    >
    > then it does not recognize the SQL text....DOH...a bloody space for
    > hours of debugging....thats programming for you
    >
    > Appreciate you looking.
    >
    > Thank
    >
    > David.
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    caulker, Oct 25, 2004
    #6
  7. Hey! That's my line! :)

    Bob
    caulker wrote:
    > and if these sql statements were stored procedures, you'd never had
    > this problem to begin with ;-)
    >
    >



    --
    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], Oct 25, 2004
    #7
    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. Daves
    Replies:
    1
    Views:
    648
    S. Justin Gengo
    Jan 6, 2006
  2. =?Utf-8?B?UHVuaXNoZXI=?=

    Need same viewstate for same page across multiple servers

    =?Utf-8?B?UHVuaXNoZXI=?=, Mar 23, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    431
    John Timney \( MVP \)
    Mar 23, 2006
  3. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,549
    Crutcher
    Feb 26, 2006
  4. Biranchi Narayan Panda
    Replies:
    0
    Views:
    573
    Biranchi Narayan Panda
    Feb 21, 2010
  5. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    372
    Aaron [SQL Server MVP]
    Aug 6, 2004
Loading...

Share This Page