Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 28th, 2005, 08:06 AM
Authorized User
 
Join Date: Jul 2005
Location: New York, New York, .
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default database connection convention

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.

  #2 (permalink)  
Old July 28th, 2005, 06:05 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
  #3 (permalink)  
Old July 29th, 2005, 03:33 PM
Authorized User
 
Join Date: Jul 2005
Location: New York, New York, .
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #4 (permalink)  
Old July 29th, 2005, 08:50 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server Naming convention for Replication darrenb SQL Server 2005 3 May 3rd, 2008 02:50 PM
Table path using 4 part naming convention snufse SQL Server 2000 1 February 1st, 2008 07:45 PM
host has preset database naming convention crmpicco PHP Databases 0 October 23rd, 2007 01:14 PM
Which coding convention do you use? cyberjoe C# 2 January 9th, 2007 10:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.