Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: open/close connection

    I am looping through a form that has me updating at least 14 records.

    This is the script that I am using.
    myDSN= theconnectionstrting
    mySQL="UPDATE thaTable SET thaTable.ProdType = '" & DefaultType(m) & "', thaTable." & columnHdr & " = '" & DefaultEmp(M) & "' WHERE LineNum = " & Cint(m)& ""
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open(myDSN)
    Conn.Execute(mySQL)
    Conn.Close

    My question is, can/should I open and close the connection outside the for loop or does it matter?

    Thanks,
    Lee

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    Where is your FOR loop? If you execute the sql statement inside the loop, you need to open the connection. If the FOR loop is used to build the sql statement, you do not need to open the connection inside the loop.

  3. #3
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    What I did was create a sub routine that build the SQL. I loop through the form and get the values for each row of data. I then call the SQL statement. I run both and insert and update for each loop. I just wandered if it was better to leave the connection open as it loops through each row or should I open and close it within the loop?

    Example

    Open the connection

    For i = 1 to 14
    a = request.form("a")
    Call SQLInsert
    Call SQLUpdate
    Next
    Close Connection
    Set to nothing

  4. #4
    Join Date
    Jan 2004
    Location
    Bermuda
    Posts
    40
    Lee:

    To minimize overhead on your SQL box, open the connection BEFORE the Loop.

    Lots of Open's + Closes tend to over-work SQL!!!

    RobbieD
    Sunny Times...

  5. #5
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Thumbs up

    That's what I thought but was not sure! Thanks for the help

Posting Permissions

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