This discussion is archived
1 Reply Latest reply: Sep 2, 2011 9:53 AM by gdarling - oracle RSS

Database connection doesn't close in ASP

Veera Newbie
Currently Being Moderated
Hi,

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
     Next

     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
                    Next
                    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
                    Else
                         Response.Write "<td>" & mOraField.Value & "</td>" & vbCrLf
                    End If
               Next
               Response.Write "</tr>" & vbCrLf

               mOraDynaset.MoveNext
          Wend
     Else
          Response.Write "<tr>"
          Response.Write "<th>No Records</th>"
          Response.Write "</tr>"
     End If

     DisconnectSQL(mOraDynaset)

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points