I need advice on multiple database connections

[slyfox]

Hi friends

What I want to do is create only one connection to the database for reading, then I have if statements inside if statements etc. and need to fetch info (about 3 times) from the database with this one connection... how do i do that???

I tried it but everytime it fails and gives me an error "connection is already open", basically it doesn't matter what I do it gives me an error + another error upon error.. so i'm not too sure how to do this... any advice??

[PeOfEo]

create the connection and select specific data using sql statements. If you need to select three different types of data use 3 different sql statements and then open the connection to execute one, then close it, then open it again to execute the next, then close it. It would help if you gave us more info. I generally have one data base connection per page if all I am using is sql server, I have a few different tables and it is only neccessary to do the connection string one time.

[radpin]

Just to add this in there:

It might not be necassary to do what you're trying to do. Generally speaking connections will pool. If you run a trace on SQL Server, you'll see a login event and then only after a certain time of no activity, a log out.

The only really expensive aspect of creating multiple connections is the instantiating of the objects, but that's normally not so hard.

But, I've done what you're trying to do before, and if you're using stored procedures (classic asp, command object), your statements will look the same execpt that you'll be changing the .CommandText, and loading the different result sets (provided they have any) into different recordsets.

In short though, I think you'll notice plenty of performance if you create and drop each object, and it makes it MUCH easier to maintain your code if you do it this way as well.

But whatever method you choose, good luck, and post your results!