SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Am I doing something wrong?

    Hi there,

    Below are the sample of my codes. The problem I have is, eventhough I set the recordset to nothing and close the db connection, I still have an active database connection showing up on my remote db server. I tried disabling the connection pooling and I tried setting the CPTimeout value to 5 secs. But the db connection still hang around for 1 minute before it goes away!

    Thanks../Ken

    On Error Resume Next
    Set DataConn = Server.CreateObject("ADODB.Connection")
    DataConn.ConnectionTimeout = 3
    DataConn.CommandTimeout = 3

    DataConn.Open DATABASE,DB_USER,DB_PASSWD

    Set cmdDC = Server.CreateObject("ADODB.Command")
    cmdDC.ActiveConnection = DataConn
    cmdDC.CommandText = "SELECT * from users"
    cmdDC.CommandType = 1

    Set rsDC = Server.CreateObject("ADODB.Recordset")
    rsDC.Open cmdDC, , 3, 1
    iRecordCount = rsdc.recordcount

    If DataConn.Errors.Count > 0 Then
    For intLoop = 0 To DataConn.Errors.Count
    ErrorString = ErrorString & "<b>" & DataConn.Errors.Count & ": </b>" & DataConn.Errors(intLoop).Description & "<p>"
    Next
    RW ErrorString
    End If

    If iRecordCount = 1 Then
    response.write "Ok"
    else
    response.write "Not Ok"
    End If

    rsDC.Close
    set rsDC = nothing
    set cmdDC.ActiveConnection = nothing
    Dataconn.Close
    set Dataconn = nothing

  2. #2
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maybe try adding near the bottom:

    set cmdDC = nothing


    another reason may be connection pooling. connection pooling's job is to keep a connection open after your done with it and give it to the next page that wants that connection.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I'll try adding that line in to see if it makes any difference. As far as the connection pooling, I think it works and it's a nice feature, but I don't need it to hang around for 60 secs. And none of the settings makes any different, e.g. cptimeout.

    Ken

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    FYI, adding that line didn't help! Thanks for the effort.

  5. #5
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Other than setting the command (cmdDC) to Nothing at the correct place (which you've tried and say it doesn't help), I can't see anything obviously wrong.

    Try removing the "On Error Resume Next", and see if you get any errors displayed - if so, you can work out from there what is going wrong and where. I'd have said the most likely reason would be that something still has a reference to the data connection, which means it can't be set to nothing. If that is the case, you'd get an error when you tried to set it to nothing.

    Sorry, that's all I can suggest trying.
    Nick Wilson [ - email - ]

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've experienced problems with trying to reopen a connection after having just set it to nothing. Using the following fixed it so I could reopen the connection (don't ask "why?" ;-)

    Hope it helps in some way, but to tell you the truth, I think it's more likely a feature of the DBMS

    [VBS]
    Sub CloseConn
    If ( IsObject( oConn ) ) Then
    If oConn.State = 1 Then oConn.Close
    Set oConn = Nothing
    oConn = Null
    End If
    End Sub
    [/VBS]


Bookmarks

Posting Permissions

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