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

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.

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

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

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