how to open a recordset OUTSIDE a loop...

Results 1 to 3 of 3

Thread: how to open a recordset OUTSIDE a loop...

  1. #1
    Roxanne Guest

    Default how to open a recordset OUTSIDE a loop...

    I&#039ve got the following problem:<BR>I&#039ve got one table with products, together with a productID.<BR>In another table are prices of those products, and (of course) a productID.<BR><BR>Now, I make an SQL-query:<BR>SQL = "Select * from products_tbl where name = book"<BR><BR>I make a recordset:<BR>Set product = Server.CreateObject("ADODB.Recordset")<BR><BR>And open everything:<BR>artikelgegevens.Open SQL, DSN<BR><BR>In a loop, I get all the productID&#039s from all the stuff, that&#039s called book<BR><BR>do until products.eof<BR>.<BR>.<BR>.<BR>.<BR>.<BR>products. movenext<BR>loop<BR><BR><BR>This is going perfect. But how can I get the prices with the correct products without puting something like:<BR><BR>SQL = "SELECT * FROM price_tbl where productID = &#039" & productID & "&#039 "<BR>Set price = Server.CreateObject("ADODB.Recordset")<BR>price.Op en SQL, DSN<BR><BR>in the loop???<BR>If I put this in the loop, Everything will be REALLY slow (because it has to open a new recordset everytime it&#039s loops again...).<BR>Is there a way I can ask the right price without opening a recordset in the loop?<BR><BR>Please, please, PLEASE, help me out<BR><BR><BR><BR>(sorry for so much text...)

  2. #2
    Bygus Guest

    Default RE: how to open a recordset OUTSIDE a loop...

    Instead of <BR>SQL = "Select * from products_tbl where name = book"<BR>do <BR>SQL = "Select * from products_tbl AS pd Inner Join price_table AS pc On pc.ProductID=pd.ProductID where name = book"<BR>This should give you what you want.

  3. #3
    Roxanne Guest

    Default I'm so grateful!

    Bygus,<BR><BR>Thank you so much for your quick help! Everything is working just the way I hoped!!! No problems, no errors, and a (reasonable) fast result on my screen.<BR><BR><BR>ThanX!!!<BR><BR>-Roxanne-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts