To DSN, Or Not To DSN
- That Is The Question!

Shakespeare never wrote it that way, but then again there was no such thing as the Internet in his days! If he was alive today and involved with creating interactive, database-driven websites then maybe he would have. Whilst we could debate the literary merits of such a statement, it serves us well to introduce the thorny subject of Data Source Names (or DSNs as they are more commonly referenced).

"What is a DSN?" I hear you cry. Let's look at it this way ... to create a database-driven website then the web page must be able to connect to the database. Whilst creating a connection is like building a roadway between the page and the database, the DSN is effectively the route MAP that defines the pathway that the road should follow - going from A to B is not always easy so you need to know where you are going! A typical example of creating a connection between a web page and a database in ASP could be ...

	Dim oConn
	Set oConn = Server.CreateObject ("ADODB.Connection")
	oConn.Open ("DSN=SiteGuru")

... whereby the DSN called SiteGuru will have been created as an ODBC Data Source using Window's Control Panel, and this will have specified the Software Driver to be used and the path to the database file or server. Variable oConn represents the connection object, which is effectively the roadway itself between your ASP file and the database. So ... that all looks pretty easy stuff, eh? And it is - all you need to remember to connect to your database is one word and all the hard work in getting to the database is taken care of for you.

Since it is so easy to use, why would we want to mess about with that? Well, to set up a DSN you need access to Window's Control Panel on the computer which is running your website, i.e. the server. Since it is possible to wreak all sorts of havoc via the Control Panel then usually only a select few people (the web administrators) have the level of access required to create a DSN. Thus it can be difficult to get a DSN established, and if you need another database or change the name of your existing database then your DSN also needs to be updated or a new one created. This can lead to major headaches trying to adminster your website. Another drawback is that a DSN is set up to use a specific driver (e.g. Microsoft Access Driver) and so will always use that driver unless the DSN is altered. If a better alternative is available, or the driver becomes faulty, it can be difficult to make any necessary changes for the reasons already mentioned.

So, if a DSN sounds good, but can have some major drawbacks, what else can we do? Well, that's where a DSN-less connection comes into play. This performs all the same functions of a DSN, but is generated within your ASP code rather than via the Control Panel. Hence it would appear that this method provides much greater flexibility than a normal DSN, and indeed it does. However it too has its own drawbacks, which we will come to soon. Firstly let's show an example of how we create a DSN-less connection ...

	Dim oConn, sDriver, sPath
	sDriver = "provider=Microsoft.jet.oledb.4.0;Persist Security Info=False;"
	sPath = "Data Source=" & Server.MapPath ("/username/db/DBname.mdb") & ";"
	Set oConn = Server.CreateObject ("ADODB.Connection")
	oConn.Open (sDriver & sPath)

... I know what you're thinking - "Whoa! Now you're speaking fluent gobble-de-gook!", and I agree that it can look a little daunting at first but let's take a closer look at what's going on. Again variable oConn represents the connection object, but this time we are specifying in our code rather than a DSN the details of how to get to the database (sPath), and which software driver to use (sDriver). Put them both together and that's your DSN for you in a DSN-less manner - all pretty easy when you think about it. (To close out this example, I should also tell you that the Server.MapPath ( ) function is used to convert a virtual path - /username/db/DBname.mdb - to a physical path which the server recognises, since the connection must follow a route which is mapped to the server. You can discover more on this subject in the tutorials at CodeFixer and in the DB and SQL tutorial at Bod's World 2).

I said that DSN-less connections are easy and flexible, but I also said that they have their own drawbacks. The two major ones are that you need to know and remember the path to, and the name of, your database; you also need to be wary of making any typographical mistakes when defining the software driver - make a mistake with either of these and you can end up with some weird and wonderful error messages!

To summarise

• DSNs are easy to use;
• And they are easy to create ...
• ... IF you have the right access permissions to the server!
• DSN-less connections are not too complicated;
• And they are very flexible;
• But you need to be careful when creating them.

It's not for me to pass judgement on which approach is better - I don't think the argument could be categorically won by either camp as there are times when one approach is more practical than the other. However I hope this article gives you a flavour of what these two approaches are, and helps you to improve your understanding of how ASP and databases can work together.

Ian Anderson
Founder and CEO
SiteGuru.co.uk