How to link 2 SQL statements..........

Discussion in 'ASP General' started by davidgordon@scene-double.co.uk, Sep 19, 2005.

  1. Guest

    Hi,

    Quick question: How can I link 2 SQL statements in my asp page.
    Originally I had 1 statement, but because it was fairly complex, it
    took too long to parse.

    SQL statement 1

    strQuery = "Select ..............

    Set RS = adoDataConn.Execute(strQuery)

    then

    Create SQL statement 2

    strQuery2 = "SELECT stockmovements.JobNumber,
    stockmovements.InStockMarker, orderlines.JobNumber"
    strQuery2 = strQuery2 & " FROM stockmovements INNER JOIN orderlines ON
    stockmovements.JobNumber = orderlines.JobNumber"
    strQuery2 = strQuery2 & " WHERE (((stockmovements.JobNumber)=" &
    RS("JobNumber") & "));"

    (''''' RS("JobNumber") is pulled from SQL statement 1)


    Set RS_Marker = adoDataConn.Execute(strQuery2)

    _______________________________

    When running the asp page, I want to pull out the field
    stockmovements.InStockMarker (SQL statement 2) for each record in the
    loop provide by SQL statement 1. I have tried, but all I get is
    stockmovements.InStockMarker for the first record of SQL statement 1
    listed for all the records.

    Do I need to place a Do Loop within a Do Loop ???

    Appreciate your help

    Thanks

    David.
     
    , Sep 19, 2005
    #1
    1. Advertising

  2. CJM Guest

    Look up the FETCH instruction in Books Online. It will allow you to loop
    through the first set of rows, and run a separate query on each. I imagine
    you could collate the results in a temp table...

    I would be careful though. This is not necessarily the most efficient
    approach. If you published your data structure and what you are trying to
    acheive, we might be able to think of a better all-in-one query...

    Chris

    <> wrote in message
    news:...
    > Hi,
    >
    > Quick question: How can I link 2 SQL statements in my asp page.
    > Originally I had 1 statement, but because it was fairly complex, it
    > took too long to parse.
    >
    > SQL statement 1
    >
    > strQuery = "Select ..............
    >
    > Set RS = adoDataConn.Execute(strQuery)
    >
    > then
    >
    > Create SQL statement 2
    >
    > strQuery2 = "SELECT stockmovements.JobNumber,
    > stockmovements.InStockMarker, orderlines.JobNumber"
    > strQuery2 = strQuery2 & " FROM stockmovements INNER JOIN orderlines ON
    > stockmovements.JobNumber = orderlines.JobNumber"
    > strQuery2 = strQuery2 & " WHERE (((stockmovements.JobNumber)=" &
    > RS("JobNumber") & "));"
    >
    > (''''' RS("JobNumber") is pulled from SQL statement 1)
    >
    >
    > Set RS_Marker = adoDataConn.Execute(strQuery2)
    >
    > _______________________________
    >
    > When running the asp page, I want to pull out the field
    > stockmovements.InStockMarker (SQL statement 2) for each record in the
    > loop provide by SQL statement 1. I have tried, but all I get is
    > stockmovements.InStockMarker for the first record of SQL statement 1
    > listed for all the records.
    >
    > Do I need to place a Do Loop within a Do Loop ???
    >
    > Appreciate your help
    >
    > Thanks
    >
    > David.
    >
     
    CJM, Sep 19, 2005
    #2
    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. Neil Zanella
    Replies:
    8
    Views:
    1,196
    mfmehdi
    Oct 20, 2006
  2. Harry George
    Replies:
    6
    Views:
    385
    Bart Nessux
    Feb 23, 2004
  3. Vince
    Replies:
    12
    Views:
    751
    Martin Gregorie
    Jan 21, 2008
  4. JT
    Replies:
    1
    Views:
    120
    Bob Barrows [MVP]
    Sep 27, 2005
  5. John Crichton
    Replies:
    6
    Views:
    267
    John Crichton
    Jul 12, 2010
Loading...

Share This Page