Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    1

    Unanswered: my big question...

    In the best of all worlds... is it better to :

    A) open a connection , do the SQL, close the connection.

    B) open a connection object in the global.asa file and use it as needed.

    C) something else

    I would appreciate any responses.


    Right now, I'm using a dbopen function that opens the conn, opens the rs
    and a dbclose fucntion that shuts it down.

  2. #2
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032

    Re: my big question...

    Originally posted by newbietoo
    In the best of all worlds... is it better to :

    A) open a connection , do the SQL, close the connection.

    B) open a connection object in the global.asa file and use it as needed.

    C) something else

    I would appreciate any responses.


    Right now, I'm using a dbopen function that opens the conn, opens the rs
    and a dbclose fucntion that shuts it down.
    I often open up several recordsets on one page usually closing one before opening the next. This is used to fill up combo boxes, etc. Thus for convenience sake and so I'm not opening and closing connections more than once on a page, I like to open the connection toward the top of the page and then close it toward the bottom and before a Response.Redirect.

    And if the connection were opened via global.asa that would be fine with me although that's not actually how I do it.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    I don't believe you'd want to open a connection and leave it open for the whole session as it does use up resources (i.e. server memory, etc.)

    Think about opening the connection at the top of the page and closing it at the bottom at the most. The other accepted way would be to open and close it each time you use it on the page.

    That's my $0.02 anyways.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    I would say;

    Don't put the connection in a session object; if you get a lot of hits in a short time (especailly people who leave instantly or quickly) your server may go down due to lack of memory.

    I tend to do as Bullschmidt does; open a connection, use it and close it at some point in the page (and then set to nothing it last)...

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Then a side question to that might be:

    If you open the connection once, then use Command.Execute to populate your recycled recordset, do you need to close the recordset before doing another Command.Execute to repopulate the recordset with new (and potentially different) data? Ex:

    Code:
    ''' Assume objDataCmd and Connection objects are instantiated and opened elsewhere '''
    
    Dim objRS
    set objRS = Server.CreateObject("ADODB.RecordSet")
    
    objDataCmd.CommandText = "SELECT * myTable"
    set objRS = objDataCmd.Execute(,,adCmdText)
    
    ''' Read results, do some work '''
    
    objRS.Close ''' <-- Is this line necessary?
    
    objDataCmd.CommandText = "SELECT * mySecondTable"
    set objRS = objDataCmd.Execute(,,adCmdText)
    
    ''' Do more work with this new recordset '''
    
    objRS.Close
    set objRS = Nothing
    
    ''' Close and Null objDataCmd and Connection objects

  6. #6
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    I belive so, I allways do. Try it with out, I think however it will fail...though I would be interested to know the result of your test...

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Originally posted by rhs98
    I belive so, I allways do. Try it with out, I think however it will fail...though I would be interested to know the result of your test...
    I've always done it that way. Without closing the recordset between it's repopulation, without an error. I open the recordset, do several queries with the same recordset variable, then close it at the very end. This doesn't cause an error, but I'm curious if I'm leaving myself open for a memory leak. If those are infact distinctly different recordsets simply referenced by the same variable, then I probably have an issue.

Posting Permissions

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