<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Option Explicit
‘ ADO Connection Constants: Cursor location
Const adUseServer = 2 ‘ Uses a server-side cursor, DEFAULT
Const adUseClient = 3 ‘ Uses a client-side cursor
‘ ADO Connection Constants: Mode
Const adModeUnknown = 0 ‘ Permissions cannot be set or determined
Const adModeRead = 1 ‘ Read-only
Const adModeWrite = 2 ‘ Write-only
Const adModeReadWrite = 3 ‘ Read and write permissions
Const strConnectString = “Provider=sqloledb;User ID=pubsLogin; pwd=pubsPassword; Data Source= YourServerName; Initial Catalog=pubs”
Const strSQL = “SELECT * FROM Authors WHERE au_lname=’This is SQL Statement 1′”
Dim cnPubs ‘ As New ADODB.Connection
Dim i
For i = 1 To 1000
Response.Write “<br>Iteration ” & i
Set cnPubs = Server.CreateObject(“ADODB.Connection”)
Response.Write “<br>–cnPubs.CursorLocation=” & cnPubs.CursorLocation
Response.Write “<br>–cnPubs.Mode=” & cnPubs.Mode
cnPubs.ConnectionString = strConnectString
cnPubs.CursorLocation = adUseClient
cnPubs.Mode = adModeReadWrite
cnPubs.Open
cnPubs.Execute strSQL
Response.Write “ ” ‘ our simple delay tactic
cnPubs.Close
Set cnPubs = Nothing
Response.Flush
Next ‘
Response.Write “<p>Done”
</script>
Question 4: True or False: Modifying the ConnectionTimeout will cause the pooled connection to remember this new value and could introduce problems when it goes back into the connection pool.
False.
The ConnectionTimeout property cannot be reset on a pooled connection. So what happens if the credentials are the same as a pooled connection, but only the timeout is different? Modifying the ConnectionTimeout on a connection — with all other parameters being identical — will create a new connection. If you have a pooled connection with the same ConnectionTimeout value, and the same credentials, that connection will be preferred. In the code below each connection and its SQL statement are identical. The iteration value of i provides the unique Connection.Timeout value. Performance monitor shows all the proof we need that, all things being equal with the exception of the Timeout value, a new connection is created each and every time.
Try it for yourself.
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Option Explicit