1 Reply Latest reply on Sep 2, 2011 4:53 PM by gdarling - oracle

    Database connection doesn't close in ASP


      file 1:

      Function ConnectSQL(strDB,strUsr,strPwd,strSQL,byref mRequest,byref mOraDynaset)
           Dim strUsrPwd
           Dim intRowCount

           strUsrPwd = strUsr & "/" & strPwd

           Set mOraSession = Server.CreateObject("OracleInProcServer.XOraSession")
           Set mOraDatabase = mOraSession.DbOpenDatabase(strDB,strUsrPwd,0)

           For Each Item In mRequest
                mOraDatabase.Parameters.add Item, Replace(mRequest(Item),"%","/%"), 1

           Set mOraDynaset = mOraDatabase.DbCreateDynaset(strSQL,0)
           intRowCount = mOraDynaset.RecordCount
           Set mOraDatabase = Nothing
           Set mOraSession = Nothing

           ConnectSQL = intRowCount
      End Function

      Sub DisconnectSQL(byref mOraDynaset)
           Set mOraDynaset = nothing
      End Sub

      file 2:

      intTotalRecords = ConnectSQL(strDB,strUsr,strPwd,strSQLQuery,Request.Form,mOraDynaset)

           If intTotalRecords > 0 Then
                While Not mOraDynaset.EOF
                     If mOraDynaset.RowPosition = 1 Then
                          Response.Write "<tr>" & vbCrLf
                          For Each mOraField In mOraDynaset.Fields
                               Response.Write "<th>" & mOraField.Name & "</th>" & vbCrLf
                          Response.Write "</tr>" & vbCrLf
                     End If

                     Response.Write "<tr>" & vbCrLf
                     For Each mOraField In mOraDynaset.Fields
                          If IsNull(mOraField.Value) Or mOraField.Value = "" Then
                               Response.Write "<td> </td>" & vbCrLf
                               Response.Write "<td>" & mOraField.Value & "</td>" & vbCrLf
                          End If
                     Response.Write "</tr>" & vbCrLf

                Response.Write "<tr>"
                Response.Write "<th>No Records</th>"
                Response.Write "</tr>"
           End If


      Problem: After fetching the data, the database connectivity doesn't close automatically.
      The database connections keep on increasing after each fetch.

      Could anyone please help