Global.asa and database connections..

[Graham Mattingley]

Hello group,

I have a question hope it does not turn out to be a silly on....

On everyone of my pages I have an a include page at the top and at the
bottom of the page

one is the database connection and one is for the database disconnection.

is there anyway I can automaticly connected to the database using a string
without adding it to every page ?

I checked in IIS and I can add a footer but not a header, or anyway using
global.asa file

Kind Regards

Graham Mattingley

 

[Aaron Bertrand - MVP]

is there anyway I can automaticly connected to the database using a string

without adding it to every page ?

Isn't that what the include file is for? When your database moves or you
otherwise have to change your connection string, you change one file.

If you're hoping to have a single connection object throughout your session,
or worse yet, a single connection object shared by all sessions, it is not a
good idea. It doesn't sound intuitive at first, but believe me, it is more
efficient to open the database, get in, get out and close it on every single
page than it is to have a connection remain open (and unusable by other
sessions) throughout the life of a session.

Perhaps useful reading:
http://www.aspfaq.com/2053

 

[Brynn]

I agree with Aaron ... you need to get in and get out of your database
as fast as possible. You don't need your database connection the
entire time the page is loading. Try out my
/coolpier_scripts/DBConn.asp script at ...

http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp

This will allow you to get in and out pretty quick ... then loop
through the 2-dimensional array of your recordset.


<!-- #include virtual="/coolpier_scripts/DBConn.asp" -->
<%
Dim yourSQL, yourArray

cp_ConnectionString = "DSN=yourDSN;"
yourSQL = "select * from yourTable where this = 'that';"



'// ****** INTO DATABASE ******
cp_DBConn.asp("open")
yourArray = cp_SqlArray(yourSQL)
cp_DBConn("close")
'// ****** OUT OF DATABASE ******



If Not IsArray(yourArray) Then
'// No Records Found Code
Else
'// Display Records Code

'//SAMPLE LOOP
Dim columnCount, rowCount
columnCount = ubound(yourArray,1)
rowCount = ubound(yourArray,2)

With Response
.Write "<table border=""1"">"
For zRow = 0 to rowCount
.Write "<tr>"
For zColumn = 0 to columnCount
.Write "<td>" & yourArray(zColumn, zRow) & "</td>"
Next
.Write "</tr>"
Next
.Write "</table>"
End With

End If
%>

Isn't that what the include file is for? When your database moves or you
otherwise have to change your connection string, you change one file.

If you're hoping to have a single connection object throughout your session,
or worse yet, a single connection object shared by all sessions, it is not a
good idea. It doesn't sound intuitive at first, but believe me, it is more
efficient to open the database, get in, get out and close it on every single
page than it is to have a connection remain open (and unusable by other
sessions) throughout the life of a session.

Perhaps useful reading:
http://www.aspfaq.com/2053

I participate in the group to help give examples of code. I do not guarantee the effects of any code posted. Test all code before use!

Brynn
www.coolpier.com

 

[Ralph Hodenius [MS]]

Hi Graham,

If you are always showing the same data (for example to fill dropdownboxes)
from the database, you can use an application variable to store the
recodset in.
Storing recordsets in session variables is absolute not something you
should do.


Maybe reading article
http://support.microsoft.com/default.aspx?scid=kb;en-us;258939



Regards,

Ralph
Microsoft

 

[Chris Hohmann]

Hi Graham,

If you are always showing the same data (for example to fill dropdownboxes)
from the database, you can use an application variable to store the
recodset in.
Storing recordsets in session variables is absolute not something you
should do.


Maybe reading article
http://support.microsoft.com/default.aspx?scid=kb;en-us;258939



Regards,

Ralph
Microsoft

Recordsets (and other non-threadsafe) objects should be stored in
neither session nor application variables.
http://aspfaq.com/show.asp?id=2053

HTH
-Chris Hohmann

 

[Aaron Bertrand - MVP]

Agreed. Throw it into GetRows(), and store the array in such a scope, at
least. Or build a string and store the string there. But not a recordset
object, blecch. WHat's the point of storing the object? The data is
already stale, so you're not really getting the benefit of "dynamic"...

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

 

[Brynn]

I think this question is leading more towards that of "ease of
development" ... keeping the DB open all the time. This obviously
should not be done, and hence my post.

Everyone elses points here are also very valid ...

If I am showing the same data over and over, and that data doesn't
change very often, I build a backend where I can use the database to
store the data, and where I can update it ... but I also have an
"UPDATE WEBSITE" button to recreate the 'static' file on the website
that has the info ... many times, I will have it take my recordset ...
for example ...

values, labels

1234, Option1Name
2345, Option2Name
3456, Option3Name

my backend will take this data, and write a n ASP file with the html
written out for the dropdown box. Then include it in the write spot.

Another example, a products database that only a small handful of
people have access to update, delete, add. I will create each product
it's own formatted static html info in a folder with it's item number
as it's file name, etc...

I want my database open only when completely necessary. But then
again, I am a crazy, performance hungry fiend ... hehe.

Brynn
www.coolpier.com

Hi Graham,

If you are always showing the same data (for example to fill dropdownboxes)
from the database, you can use an application variable to store the
recodset in.
Storing recordsets in session variables is absolute not something you
should do.


Maybe reading article
http://support.microsoft.com/default.aspx?scid=kb;en-us;258939



Regards,

Ralph
Microsoft

I participate in the group to help give examples of code. I do not guarantee the effects of any code posted. Test all code before use!

Brynn
www.coolpier.com