In this article we will learn the two ways of connecting to database :
via DSN ( Data Source Name ) without DSN DSN ConnectionsIn my earlier article on Accessing the database from ASP I explained connecting to database via DSN in a step by step mannner. So there is no need to repeat that again, you can see it from there.
DSN stands for 'Data Source Name'. It is an easy way to assign useful and easily rememberable names to data sources which may not be limited to databases alone e.g Excel spread sheet etc.
I will now skip the steps of creating and assigning DSN to a database, you can see them by clicking here. Once you are done creating a DSN for your data source ( database lets say ), you can connect to it using following code :
Dim con Set con = Server.CreateObject("ADODB.Connection")
con.Open "DSN=mydsn" ' Now database is open and we are connected ' Do some thing
here 'We are done so lets close the connection con.Close Set con = Nothing
ExplanationIf you have been following my tutorials then above code will be nothing but a piece of cake for you. The only significant point to see is that we have used "DSN=mydsn" to connect to our database using our DSN which in this case is mydsn.
DSN less ConnectionDSN less connections don't require creation of system level DSNs for connecting to databases and provide an alternative to DSNs. We will now see how to connect to a database via ASP using Connection String in place of DSN name.
Dim con Set con =
Server.CreateObject("ADODB.Connection")
con.Open
"Provider=Microsoft.Jet.OLEDB.4.0; Data" &
_ "Source=c:\path\to\database.mdb"
' Now database is open
and we are connected ' Do some thing here 'We are done so lets close
the connection
con.Close Set con = Nothing
ExplanationThe only change is use of a Connection String in place of a rather easy to remember DSN. Above code connects to an imaginary Access database. Connection Strings for other databases are different.
How to construct a Connection String for Access and SQL Server Databases ?
For Access database :-With native OLE DB Provider ( preferred ):Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\path\to\database.mdb
Using ODBC connection without specifying a DSN :Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\path\to\database.mdb
Note, always use the first Connection String that uses native OLE DB provider because it is faster than the second one. 'Data Source' or 'DBQ' are absolute path to the database. If you have relative path then you can obtain absolute path by using Server.MapPath("/relative/path/to/database.mdb") e.g.
Dim conStr Set conStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Server.MapPath("/dbo/database.mdb")
For SQL Server :With native OLE DB Provider ( preferred ):-
Provider=SQLOLEDB; Data Source=server_name; Initial Catalog=database_name; User Id=user_name; Password=user_password
Using ODBC Provider :Driver={SQL Server}; Server=server_name; Database=database_name; UID=user_name; PWD=user_passwordWhy to use DSN Connections ?
Provides easy to remember data source names. When there are lots of data sources to think of and you want a central repository to hold the collection of data sources without having to worry about the actual site and configuration of the data sources. Why to use DSN less Connections ?
When you can't register DSNs yourself e.g. when you are running a virtual hosting account on other's server. Stop emailing system administerator, connect to your databases directly. Provides faster database access because it uses native OLE DB providers, while DSN connections make use of ODBC drivers. My ExperienceI always use DSN less connections on my site and examples :).
