Hi, ASP Guru. I'm an ASP newbie. What's the convention of database connection?

Most pages require connection to database. Should I put an include file for database connection? If that's the case, what if some pages require variable amount of connection depending on the user option such as customer update? I can't put one include file on the top to open a database and another include file at the footer for closing the connection.

NOTE: Following this type of method you will only ever need to change your connection string ot the details within in one file for all your pages - changing from Access to SQL Server etc the same.

What I do (yes, use .asp files as includes not .inc):
1..Create a global.asp file and place all your constants (amoung other things) EG:
   Dim conn,sql,EoF,Rs
   const IUSER = "someUN"
   const PASS = "somePW"
   const DBNAME = "myDB"
   const DBSERVER = "pcName"

2..Create a function.asp file and place you connectiopn string there along with all functions you would like to be accessable from any page:

  Function GetConnectionString()
    GetConnectionString = "Provider=SQLOLEDB; Password=" & PASS & "; Persist Security Info=True; User ID=" & IUSER & ";Initial Catalog=" & DBNAME & "; Data Source=" & DBSERVER
  End Function

3..Now at the VERY TOP of every page place:

<% OPTION EXPLICIT %>


<% Set Conn = server.createobject("ADODB.Connection")
     conn.open(GetConnectionString)
%>



NOTE:
a..Conn.open (opens a connection)
b..GetConnectionString (call the function holding your connection string)
c..To view your connection string <%= GetConnectionString %>
d..Any page you do not need a DB connection leave out the following:
   Set Conn = server.createobject("ADODB.Connection")
   conn.open(GetConnectionString)
e..To close a connection place the following at the foot of any page you have opened one up:
<%
   conn.close
   set conn= nothing
%>

;;;what if some pages require variable amount of connection depending on the user option such as customer update
Im not clear whay you are getting at here

;;I can't put one include file on the top to open a database and another include file at the footer for closing the connection.
Yes you can however I would do it the way I have illustrated above.





Wind is your friend
Matt

Thanks. What I meant before is ... If I have one page that requires 2 database connections,
one retrieves record from order table, another one from orderline table. it's like 2 select statements.

Sorry if I misunderstood you but why wouldnt you uae the same connection? If you use the method above you would:
<% OPTION EXPLICIT %>


<% Set Conn = server.createobject("ADODB.Connection")
     conn.open(GetConnectionString)
     dim getOrders,getOrderLines
     sql = "SELECT ...,...,... FROM ORDERS Where ...;"
     set getOrders = conn.execute(sql)
     sql = "SELECT ...,...,... FROM orderLine Where ...;"
     set getOrderLines = conn.execute(sql)
     'Now you have two record sets ready to use
     'do stuff with them, then when you ahve finished with them tidy up like so
     IF isObject(getOrders) then set getOrders = nothing END IF
     IF isObject(getOrderLines) then set getOrderLines = nothing END IF
     conn.close
     set conn= nothing

%>


Wind is your friend
Matt