ASP Database Programming Hints


Use include files
	A web page that accesses a database and allows the user to interact with the data will generally go through many changes before it is complete and ready for the real world. When you create an ASP page that accesses the database, the database connection string should be specified in an include file. This will prevent the potentially arduous task of switching from one database to another. This is specifically the case if you have a development database that is deployed as a completed project. The database would then move to another server and this will entail a change in the ASP connection string. Being able to make this change in just one location is very advantageous.
Use of integrated security is a must
	When you create a web page that will access the database, you need to be aware that security is a very important issue. There are two options for integrated security. The first option is Anonymous access. When anonymous access is specified, what is actually occurring is that a domain account is created and specified as the account that the web page "runs" as and will be used to talk to the database. This is important if you will have people who do not have Business School NT user accounts. The second option is Trusted access. This means that the user that is browsing the web page has to have access to the database in order to retrieve data and view your pages. This is used when you have a page that you want to strictly limit to specific Business School users or a small amount of specific accounts. When using integrated security to connect to a database, the following code give an example of how to open a connection. The connection string provided here is the key to using integrated security. Set objConn = Server.CreateObject("ADODB.Connection") Dim connection_string connection_string = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YourDatabase;Data Source=YourDatabaseServer" objConn.CommandTimeout = 30 objConn.ConnectionTimeout = 30 objConn.Open connection_string
How to I connect to an Access database from ASP?
	This example will probably work for most people doing a simple ASP project. This assumes the Access db is in the same folder as the ASP scripts. fileLoc = Request.ServerVariables("PATH_TRANSLATED") fileLoc = Replace(fileLoc,"yourASPpage.asp","Test.mdb") Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileLoc Here is a much more complicated and flexible method. This one can be used across an entire website with no modifications after the initial virtual path to the Access db is set up. The database does not need to be in the same location as the ASP scripts. path_info = Request.ServerVariables("PATH_INFO") path_info = Replace(path_info,"/","\") path_translated = Request.ServerVariables("PATH_TRANSLATED") path_physical = Replace(path_translated,path_info,"") hold_file = path_physical & "\virtualPathtoDB\Test.mdb" Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & hold_file<