Results 1 to 4 of 4

Thread: SQLConnection

  1. #1
    Christine Guest

    Default SQLConnection

    My boss wants me to think of a way to update the existing system to ensure better efficiency. The SQL Connection is establised when the session is on and only be closed when the session is end. This had caused memory leakage problem.<BR><BR>Pls help to think any other better way to solve it.<BR>Appreciate any advice<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: SQLConnection

    The general rule for database connections in ASP is open at the last possible moment and close at the first possible moment. This allows connection pooling to work properly.<BR><BR>The worst possible thing to do is store a connection in the session - this is a sure-fire way of giving yourself scalablility problems because it locks the session to a single ASP worker thread. Also, there is a limit to the number of database connections ASP can manage at any one time, so doing this will effectively set a maximum limit on the number of sessions your server can handle.<BR><BR>The rule I use is always EXPLICITLY close and set to nothing ALL database connections and recordsets on every page. I know it seems less efficient but it actually makes much better use of resources.<BR><BR>Dunc

  3. #3
    christine Guest

    Default RE: SQLConnection

    Thanks A lot..

  4. #4
    Christine Guest

    Default RE: SQLConnection

    Dunc,<BR><BR>I encountered another problem when changing the existing system to your recommended way. My idea is to create 2 functions. One is to establish SQL Connection and another is to Close the connection. The purpose i do so is to call these 2 functions when necessary. Anyhow,The following error had occur. <BR><BR>ADODB.Command error &#039 800a0bb9&#039 <BR><BR>The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another. <BR><BR>BELOW IS MY PROGRAM<BR><BR>THE ERROR refer to the sentence with a slash in front<BR><BR>Sub SelectSQL(Records,Statement)<BR> Set ObjCmd = Server.CreateObject("ADODB.Command")<BR>/ ObjCmd.ActiveConnection = ObjConn<BR> ObjCmd.CommandText =Statement<BR> Set Records = objCmd.Execute<BR>End Sub <BR><BR>THE BELOW IS TO ESTABLISH CONNECTION and CLOSE CONNECTION<BR><BR>Sub fnCreateConnecion<BR> Set ObjConn = Server.CreateObject("ADODB.Connection")<BR> ObjConn.ConnectionString = Application("ES_ConnectionString")<BR> objConn.CursorLocation = adUseClient<BR> ObjConn.Open<BR>End Sub<BR><BR>Sub fnCloseConnection<BR> ObjConn.Close<BR> Set ObjConn = nothing<BR>End Sub<BR><BR><BR>Appreciate your help..thanks in advance<BR>

Posting Permissions

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