----Original Message-----

From: Tam Nguyen [mailto:tnguyen@s...]

Sent: 20 July 2000 18:58

To: feedback@w...

Subject: Open ADO connection!



Hello,



My name is Tam. I have purchased many books from Wrox Press Ltd such as

Beginning and Professional Active Server Pages 2.0 etc. How come the books

don't talk about the disadvantages when you open many connections to the

database? I understand that you'd close the connection right after you

process the sql statements. Let me give you an example of what I mean.



Let say I need to create a two survey forms, I would have to open the

connection twice with two sql statements to insert the input to my 

database.

Of course each form has one ADO connection along with its sql. However, I

would like to know if it would make a different (better speed/time) if I

create a separate asp file calls method.asp, in this file, I will open the

connection to the database and using select case for each sql statement,

here is sample code for method.asp:



      Set adoConn = Server.CreateObject("ADODB.Connection")

	adoConn.Open "DSN=name"



	Select Case Request.Querystring

	Case "survey1"

	sql1="Insert into survey1_table;"



	Case "survey2"

	sql1="Insert into survey2_table;"

	End Select



Now in my two survey forms, I only need to call survey1 case for my

form1.asp, and survey2 case for my form2.asp. In other words, what I 

usually

do like in your books is that in my form1.asp, I would open the 

connection,

follow with sql statement, then the form. And I would do the same for

form2.asp.



I know this question is not specific to any of the chapters in your books.

However, I think it's important to explain to the readers like myself the

downside of having to open many connections with same database.



Anyway, I am hoping you could explain to me the advantages of coding

something like method.asp above over the form1.asp and form2.asp where you

open the connection for each form? Will it save time, more speed, less

trafic when you only have one open connection? Or should I open connection

each time for each form(the connection will be closed afterward though)?



This is urgent. I try to explain the best I can. If you're confused, 

please

let me know. And in the mean time, I still support your books.



Thanks,



Tam Nguyen

I found something in "Beginning ASP Databases" that I think can help you.

In page 205 we find a small discussion about connection pooling.



--------------

Setting up a connection is time consuming for the server. When a requester

colses a connection by using

Set oConn=Nothing

the connection is no longer available to that requester. However, the

connection is still held open by ADO, albeit unassigned to a requestor.

Whenever a connection request comes in, NT will automatically check to see

if there is already a connection of that type and if so NT uses the existing

connection rather than expend the time and memory to create a new one.

--------------------



So if you want better performance don't forget to "nothing" your connection.



Cheers,

		Gonzalo









-----Mensaje original-----

De: Phil Perks [mailto:philp@w...]

Enviado el: viernes, 21 de julio de 2000 9:47

Para: ASP Databases

Asunto: [asp_databases] Open ADO connection!





----Original Message-----

From: Tam Nguyen [mailto:tnguyen@s...]

Sent: 20 July 2000 18:58

To: feedback@w...

Subject: Open ADO connection!



Hello,



My name is Tam. I have purchased many books from Wrox Press Ltd such as

Beginning and Professional Active Server Pages 2.0 etc. How come the books

don't talk about the disadvantages when you open many connections to the

database? I understand that you'd close the connection right after you

process the sql statements. Let me give you an example of what I mean.



Let say I need to create a two survey forms, I would have to open the

connection twice with two sql statements to insert the input to my

database.

Of course each form has one ADO connection along with its sql. However, I

would like to know if it would make a different (better speed/time) if I

create a separate asp file calls method.asp, in this file, I will open the

connection to the database and using select case for each sql statement,

here is sample code for method.asp:



      Set adoConn = Server.CreateObject("ADODB.Connection")

	adoConn.Open "DSN=name"



	Select Case Request.Querystring

	Case "survey1"

	sql1="Insert into survey1_table;"



	Case "survey2"

	sql1="Insert into survey2_table;"

	End Select



Now in my two survey forms, I only need to call survey1 case for my

form1.asp, and survey2 case for my form2.asp. In other words, what I

usually

do like in your books is that in my form1.asp, I would open the

connection,

follow with sql statement, then the form. And I would do the same for

form2.asp.



I know this question is not specific to any of the chapters in your books.

However, I think it's important to explain to the readers like myself the

downside of having to open many connections with same database.



Anyway, I am hoping you could explain to me the advantages of coding

something like method.asp above over the form1.asp and form2.asp where you

open the connection for each form? Will it save time, more speed, less

trafic when you only have one open connection? Or should I open connection

each time for each form(the connection will be closed afterward though)?



This is urgent. I try to explain the best I can. If you're confused,

please

let me know. And in the mean time, I still support your books.



Thanks,



Tam Nguyen

I don't have all the books in question, so I can't comment on the code that

you have in front of you, but the general rule would be to open 1

connection, and execute both SQL statements against that.



<%

objConn = Server.CreateObject("ADODB.Connection")

objConn.Open strConnect



strSQL = "INSERT...etc..."



objConn.execute(strSQL)



strSQL = "DELETE FROM ...etc... "



objConn.execute(strSQL)



objConn.close

set objConn = nothing

%>



In any page, you only need 1 connection to each particular database, not

many...



HTH



Cheers

Ken





> ----Original Message-----

> From: Tam Nguyen 

> Sent: 20 July 2000 18:58

> To: feedback@w...

> Subject: Open ADO connection!

>

> Hello,

>

> My name is Tam. I have purchased many books from Wrox Press Ltd such as

> Beginning and Professional Active Server Pages 2.0 etc. How come the books

> don't talk about the disadvantages when you open many connections to the

> database? I understand that you'd close the connection right after you

> process the sql statements. Let me give you an example of what I mean.

>

> Let say I need to create a two survey forms, I would have to open the

> connection twice with two sql statements to insert the input to my

> database.

> Of course each form has one ADO connection along with its sql. However, I

> would like to know if it would make a different (better speed/time) if I

> create a separate asp file calls method.asp, in this file, I will open the

> connection to the database and using select case for each sql statement,

> here is sample code for method.asp:

>

>       Set adoConn = Server.CreateObject("ADODB.Connection")

> adoConn.Open "DSN=name"

>

> Select Case Request.Querystring

> Case "survey1"

> sql1="Insert into survey1_table;"

>

> Case "survey2"

> sql1="Insert into survey2_table;"

> End Select

>

> Now in my two survey forms, I only need to call survey1 case for my

> form1.asp, and survey2 case for my form2.asp. In other words, what I

> usually

> do like in your books is that in my form1.asp, I would open the

> connection,

> follow with sql statement, then the form. And I would do the same for

> form2.asp.

>

> I know this question is not specific to any of the chapters in your books.

> However, I think it's important to explain to the readers like myself the

> downside of having to open many connections with same database.

>

> Anyway, I am hoping you could explain to me the advantages of coding

> something like method.asp above over the form1.asp and form2.asp where you

> open the connection for each form? Will it save time, more speed, less

> trafic when you only have one open connection? Or should I open connection

> each time for each form(the connection will be closed afterward though)?

>

> This is urgent. I try to explain the best I can. If you're confused,

> please

> let me know. And in the mean time, I still support your books.

>

> Thanks,

>

> Tam Nguyen

>