Database Connection Strings - Sample Code

Hi,

Here is a tip for everyone who uses databases within their ASP applications regarding the database connection strings. I use this code in my applications a lot - and it comes in handy when web hosts update servers etc.

The examples here are for Access databases using the Microsoft.JET.OLEDB.4.0 connection string, but if you want it for another db and can't figure out how, just ask!

Here is what I do:

Within the global.asa file in my root website directory, I have a subprocedure Sub Application_OnStart which sets my database connection strings. Here's how:



These two strings have now set application variables. Within your application, when you connect to the database, you need to link the two connection strings together with your database name. For example:



What happens is we have a generic database connection split over two different variables. strCon and strCon2 . If you look in the testapp.asp example above, I have set the database name within the page, and put this between the two Application variables:

A simple write of the ConnectionString (from the above file) would show:

Provider=Microsoft.JET.OLEDB.4.0; Data Source=[path to your database here - do not include the actual db file name]testdb.mdb; JET OLEDB:Database Password=[your password here]

Notice that the database name has been written after the [path to your database...] part and before the .mdb part, thus making a totally generic connection string which can be used to access as many different databases as you want. (Providing they use the same connection strings)

Phew! I hope this helps someone. I spent quite a while researching this when I was a newbie to ASP, and thought I would share it with you.

If you have any other questions please feel free to ask!

great job man, keep up the good work!

why cant we use a single application variable containing the string instead of two?

Thanx
Sony

Hi Sony,

The way that the code works allows you to set the name of the database in the actual ASP Page, not the application variable. Yes, if you only had one database in your application, that would work, but dont forget to think about making the site bigger and getting more databases etc.

What the code does is sets the application variable up to the point where the database file name is required. I then stop here, and start the rest of the string with .mdb for the access database file name extension. The reason you have to do this is because you have not set the dbName variable when the global.asa file runs, have you?

For example, you cannot do this:

because the variable dbName has not yet been set. A simple Response.Write of the string would show the following:

even after setting the dbName value to, for example, "test" in the ASP Page.

If you have any more probs, post back. I hope this explains it all!

Matthew