Using ASP to connect to an Access Database

by

The following ASP script was used to pull information out of an Access database and publish it to a web page. IIS 7.0 was used as the web server application.

The script does the following-

  • Create an HTML body
  • Create a connection to an Access Database (in this example I have connected to the PaperCut database file)
  • Read the authenticated user account and strip the domain prefix (please note, for this to work users need to authenticate with their use account for this information to be accessed)
  • Find the PaperCut record for the user
  • Echo the pages credit and pages printed details for the user from the database file
  • If the ‘credit’ amount is less than 30 cents (<.3) it will be displayed in red font.

IIS Configuration

IIS needs to be configured to use the “integrated Windows Authentication”, for the user name look up part to work.

<html>
<head>
<title>Print Blance</title>
</head>

<body marginwidth=”0″ marginheight=”0 leftmargin=”0″>
<img src=”papercut-logo.png”><br>
<%

Response.Buffer = True
Response.CacheControl = “No-cache”
‘Dimension variables
Dim adoCon ‘Holds the Database Connection Object
Dim rsDataBase ‘Holds the recordset for the records in the database
Dim strSQL ‘Holds the SQL query to query the database ‘Create an ADO connection object
Dim gstrLoggedInUser
Dim dbname
Set adoCon = Server.CreateObject(“ADODB.Connection”)

dbname = “C:\Program Files\PaperCut\Database\PCUserDB.mdb”

adoCon.Open (“Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & dbname)

‘Create an ADO recordset object
Set rsDataBase = Server.CreateObject(“ADODB.Recordset”)
‘Initialise the strSQL variable with an SQL statement to query the database
strSQL = “SELECT UserData.UserName, UserData.Credit, UserData.PagesPrinted FROM UserData;”
‘Open the recordset with the SQL query
rsDataBase.Open strSQL, adoCon

Function StripDomainFromUser(strUser)
Dim s
s = Replace(strUser, “/”, “\”)
Dim pos
pos = InStr(1, s, “\”, vbBinaryCompare)
if pos > 0 Then
s = Trim(Mid(s, pos + 1, 255))
End If

StripDomainFromUser = s
End Function

gstrLoggedInUser = StripDomainFromUser(Request.ServerVariables(“LOGON_USER”))

Response.Write (“<b>User: </b>” + gstrLoggedInUser)

Do While not rsDataBase.EOF
if (rsDataBase(“UserName”))=(gstrLoggedInUser) then

if (rsDataBase(“Credit”))=<.3 then
Response.Write (“<br><font color=red><B>Balance: </b>”)
Response.Write FormatCurrency((rsDataBase(“Credit”)),-1)
Response.Write (“</font>”)
Else
Response.Write (“<br><b>Balance: </b>”)
Response.Write FormatCurrency((rsDataBase(“Credit”)),-1)
end if

Response.Write (“<br><b>Pages Printed: </b>”)
Response.Write (rsDataBase(“PagesPrinted”))

rsDataBase.Close
Response.End
else
‘Move to the next record in the recordset
‘ Response.Write (gstrLoggedInUser + ” is not a match with ” + rsDataBase(“UserName”) + “<br><br>”)
rsDataBase.MoveNext

end if
loop

‘ If user doesnt exist in database, give error message
Response.Write (“<br>Information not found”)

‘Reset server objects
rsDataBase.Close
Set rsDataBase = Nothing
Set adoCon = Nothing
%>
<BR>
</body>
</html>

 

Output

output