SQL Query inside a loop. Connect inside??

Results 1 to 3 of 3

Thread: SQL Query inside a loop. Connect inside??

  1. #1
    Thierry Guest

    Default SQL Query inside a loop. Connect inside??

    Hi,<BR><BR>I need to connect to an ACCESS database and make variable-dependant SQLqueries within a loop.<BR><BR>my (incomplete) codes go like this<BR>-----------------------------------------------<BR>dim count<BR>For count=LBound(MATERIAUX_POSSIBLES) to UBound(MATERIAUX_POSSIBLES)<BR>******* &#039I need to SELECT * FROM Produits WHERE Modele=strModele AND Materiel=MATERIAUX_POSSIBLES (count)<BR>******* &#039if not EOF...<BR>*********** &#039some more codes...<BR>******* &#039Else<BR>*********** &#039some more codes...<BR>Next<BR>-----------------------------------------------<BR><BR><BR>MY QUESTION:<BR>Should I include this THE WHOLE connection inside the loop??? wouldn&#039t that<BR>be performance degrading?:<BR>Dim strSQL<BR>strSQL = "SELECT * FROM Produits WHERE Modele=strModele AND Materiel=MATERIAUX_POSSIBLES (count)"<BR>*** <BR>Dim objRS<BR>Set objRS= Server.CreateObject("ADODB.Recordset")<BR>objRS.Op en strSQL, objConn, , adLockOptimistic<BR><BR>or should I work otherwise?<BR>I&#039m a bit new to ASP and even more to SQL queries of that order.<BR>Awaiting for your (clearly commented if possible) advice! : )<BR>Thanks!

  2. #2
    peterjl@austec.net.au Guest

    Default RE: SQL Query inside a loop. Connect inside??

    Your question does not make muh sense. I think that you mean that you want to execute a recordset several times based on the values in an array. Where does the array come from? Is it hard-coded into your program or is it generated from the database?<BR><BR>From your code, it looks like you you have made the connection to the DB once and then loop through the array elements. Openning the recordset for each Query you build. That is okay as long as you close the recordset after each loop. So you have something like this:<BR><BR>&#060;%<BR>Set Conn=Server.CreateObject("ADODB.Connection")<BR>Co nn.Open ConnectionString<BR>Set Rs=Server.CreateObject("ADODB.Recordset")<BR>For count=LBound(MATERIAUX_POSSIBLES) to UBound(MATERIAUX_POSSIBLES)<BR>strSQL = "SELECT * FROM Produits WHERE Modele=&#039" & strModele & "&#039 AND Materiel=&#039" & MATERIAUX_POSSIBLES (count) & "&#039"<BR>rs.open strSQL, Conn<BR>&#039Do something with the data.<BR>rs.close<BR>Next <BR>set rs=nothing<BR>conn.close<BR>set conn=nothing<BR>%&#062;<BR><BR>Note that I have changed your "strSQL =" to what would be required to build the string dynamically. Also if you are only displaying the results of the recordset the rs.Open method only requires the first 2 params

  3. #3
    Thierry Guest

    Default Thanks! : )

    Thanks. that&#039s what I needed.<BR>(so the connection object need to be outside the loop, but the recordset (w/query) is inside...as long as I close it before reusing it.)<BR>Thanks for cleaning my SQL Query too!

Posting Permissions

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