How to display records from top 4 database systems using plain ASP?

Sometimes, we would have chance to convert our existing database or create a new database. Some decisions IT professionals need to make are scary, and choosing database software is one of them. This overview aims to describe how to set database connections with some common database software. Hopefully, you would find it useful.

i. MS SQL Server

With DSN

<%
	Set conn = Server.CreateObject("ADODB.Connection")
	conn.Open "DSN=DSNname;UID=username;PWD=password;DATABASE=databasename"
%>

Without DSN

<%
	DSN1 = "DRIVER={SQL Server};SERVER=servername;UID=username;" & _
		"PWD=password;"
	DSN1 = DSN1 & "DATABASE=databasename"

	Set conn = Server.CreateObject("ADODB.Connection")
		conn.Open DSN1
%>

ii. Oracle

ODBC with DSN

<%
	Set conn = server.createobject("adodb.connection")
		conn.CursorLocation = adUseClient   
		conn.Open "DSN=DSNname;UID=username;PWD=password"
%>

iii. MS Access

i.) With DSN and no username/password

<%
	Set conn = Server.CreateObject("ADODB.Connection")
		conn.Open "DSNname"
%>

ii.) With DSN and User ID/Password

<%
	Set conn = Server.CreateObject("ADODB.Connection")
		conn.Open "DSNname","username","password"
%>

iii.) Without DSN
Using physical path as a reference:

<%
	DSN1="DRIVER={Microsoft Access Driver (*.mdb)}; "
	DSN1=dsntemp & "DBQ=c:\mywork\accounting.mdb" 

	Set conn = Server.CreateObject("ADODB.Connection")
		conn.Open DSN1
%>

Using Server.MapPath, which is the path from the webserver root (by default, c:\inetpub\wwwroot).

<%
	DSN1="DRIVER={Microsoft Access Driver (*.mdb)}; "
	DSN1=dsn1 & "DBQ=" & Server.MapPath("/mywork/accounting.mdb")

	Set conn = Server.CreateObject("ADODB.Connection")
		conn.Open DSN1
%>

iv. MySQL Databases :
You need to supply a database name because in MySQL you can have mulitple databases on the same SQL server.

<% 
	strConnection = "driver={MySQL};server=localhost;uid=username;"
	strConnection = strConnection & "pwd=password;database=databasename" 

	Set conn = Server.CreateObject("ADODB.Connection") 
		conn.Open strConnection 
%>

Now, the database connections would be established. Next, we would need to retrieve data from the database.

Below is an example that assumes we've got a list of email addresses.

Table Name: emailadds 

name              emailadd 

------------------------------------------------------ 

Ken 		  ken@prect.com
...		  ...........

We write a query to list and output all the names and email address in a table.

<% 
	strQuery = "SELECT * FROM emailadds"

	Set rsEmailData = adoDataConn.Execute(strQuery)

	If Not rsEmailData.BOF Then 
%>

<TABLE> 
</span><TR>
<TD<b>Name</b></TD>
<TD><b>Email Address</b></TD>
</span></TR> 

<% 
	Do While Not rsEmailData.EOF 
%> 

</span><TR>
<TD><%=rsEmailData("name").Value %></TD>
<TD><%=rsEmailData("emailadd").Value %></TD>
</span></TR> 

<% 
	rsEmailData.MoveNext 
	Loop 
%> 

</TABLE> 
<%
	Else
		Response.Write("Sorry, no email addresses found.") 
	End If
%>

After using databases, you should close the database and the record set so that occupied memory can be released.

<% 
	rsEmailData.Close
	adoDataConn.Close

	Set adoDataConn = Nothing 
	Set rsEmailData = Nothing 
%>