The right way to connect....

[JediBMC]

After view a number of people's ASP code here and in other forums, I have noticed that some people make connections differently than I do. So I am curios as to the right way to make a connection.

Here is what I do when connecting to something like a MS Access database:

Dim DBConnection
Dim DBConnectionString
Dim DBRecordSet
Dim DBRecordSetSQL

Set DBConnection = Server.CreateObject("ADODB.Connection")
Set DBRecordSet = Server.CreateObject("ADODB.RecordSet")
DBConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};uid=; DBQ=" & Server.MapPath("northwind.mdb")
DBRecordSetSQL = "SELECT * FROM Orders WHERE Orders.OrderID = 1;"

DBConnection.Open DBConnection String
DBRecordset.Open DBRecordsetSQL, DBConnection

What I have seen others do is:

Dim Recordset
Dim DBConnectionString

Set Recordset = Server.CreateObject("ADODB.Recordset")

DBConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};uid=; DBQ=" & Server.MapPath("northwind.mdb")
Recordset.ActiveConnection = DBConnectionString
Recordset.Source = "SELECT * FROM Orders WHERE Orders.OrderID = 1;"
Recordset1.Open()

Any help here?


-Brian-
I'm not an actor, but I play one on TV.

 

[Sheco]

The connection will be created implicitly in the second example so you don't really save anything as far as memory usage is concerned.

There was a rumor that explicitly creating the connect as in the first example would help with the connection pooling but I'm not sure if that ever really played out.

 

[Dashley]

Brian,
theres also a third way and more to open a connection. Most of it depends on how it was learned.

Set DBConnection = Server.CreateObject("ADODB.Connection")
creates an actual connection object that can be called / resused throught out a sub function, or site wide if created globally.

The second examle above works but only within the specific recordset its opend with.

Check this link out

http://www.w3schools.com/ado/ado_ref_connection.asp

Hope this helps.

Dan

The code below shows how one connection can serve 2 Recordsets.

DIM Rs1
DIM rs2
Dim Conn
Dim Sql1
Dim Sql2


Set conn=server.CreateObject("ADODB.Connection") 
Set RS2=Server.CreateObject("ADODB.Recordset") 	
Set RS3 = Server.CreateObject("ADODB.Recordset")
	
conn.Open = "Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\database\MyDB"
Sql1 = "SELECT Bla Bla bla from"
Sql2 = "SELECT Bla Bla bla from"

	With RS1
	IF RS1.State=1 THEN RS1.Close
		.ActiveConnection = conn
		.Source =Sql1
		.Open
	end with

	With RS2
	IF RS2.State=1 THEN RS2.Close
		.ActiveConnection = conn
		.Source =Sql2
		.Open
	end with

 

[DotNetGnat]

nice explanation here...

http://www.devguru.com/Technologies/ado/quickref/recordset_activeconnection.html

-DNG