>I have been programming for a number of years and use all the major backends. >This is a question about connections. I was taught to make a connection and >then end it as quickly as possible. Now our network/SQL Server administrator >has suggested keeping open one connection and not closing it until a person >closes the ASP application.
I think your intuition is right, his is wrong. How long has your network administrator been developing distributed Internet applications? :)
The internet is stateless. Someone clicks your page. Your ASP gets a connection, returns the results and drops the connection. If one user keeps the connection open how do you ever hope to scale? You *should* use connection pooling so that when you disconnect, the operating system keeps the connection for the next request, but this is invisible to the programmer.
If you are worried about performance, you can store lookup tables as XML in your application or session variables.
With .NET, the tables are reversed, so to speak. ADO.NET Datasets are safe to be stored in session variables (because they are XML) and ASP.NET keeps viewstate for your Web forms so you don't have to create hidden form inputs. But the connection to the database still gets closed when it is not needed.
-Dave