my big question...

newbietoo · #1 (**permalink**) 06-26-03, 18:21

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.

Bullschmidt · #2 (**permalink**) 06-30-03, 01:40

Quote:

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.

Bullschmidt · #3 (**permalink**) 06-30-03, 21:52

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.

rhs98 · #4 (**permalink**) 07-02-03, 12:49

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)...

Seppuku · #5 (**permalink**) 07-09-03, 18:47

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

rhs98 · #6 (**permalink**) 07-10-03, 20:49

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...

Seppuku · #7 (**permalink**) 07-10-03, 21:05

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech