Virtual Domain Hosting
Database Access
- Preliminary info about referencing databases READ THIS FIRST!
- ASP.NET 2.0 - 4.0 (settings & examples)
- "Classic" ASP (Active Server Pages) (settings & examples)
- ColdFusion (settings & examples)
- Virtual Domain Hosting main menu
Preliminary info about referencing databases
If you host a domain with us, you can use one or more databases with your site (e.g.: Microsoft Access, Microsoft SQL Server, Microsoft Visual FoxPro, etc.) Take a look at the information below for information, tips, etc. regarding incorporating a database into your website.- Keep your database file(s) in a separate directory from other files in your site. This makes managing directory/file permissions much easier. Microsoft SQL Server databases are not located within your website's directory structure.
- Read about SQL Injection to prevent someone from seeing sensitive data and/or tampering with data in your database:
- Review the reserved words when writing SQL statements:
ASP.NET 2.0 - 4.0
ASP.NET users do not need and should not use DSNs (data source names) to access databases. ASP.NET can directly access databases which is more efficient than using ODBC DSNs.
Sample /Web.config file:
<?xml version="1.0" ?>
<connectionStrings>
<!-- The following two connectionString values are identical.
http://msdn.microsoft.com/en-us/library/ms998292.aspx -->
<add name="MyDbConn1"
connectionString="Server=SQLServerName;Database=MyDatabaseName;Trusted_Connection=Yes;"/>
<add name="MyDbConn2"
connectionString="Data Source=SQLServerName;Initial Catalog=MyDatabaseName;Integrated Security=SSPI;"/>
</connectionStrings>
ASP (Active Server Pages)
ASP users do not need and should not use DSNs (data source names) to access databases. ASP can directly access databases which is more efficient than using ODBC DSNs.
Sample /global.asa file:
<script language="vbscript" runat="server">
' Use the following METADATA line instead of referencing
' C:\Program Files\Common Files\System\ado\adovbs.inc
<!--METADATA TYPE="typelib"
UUID="2A75196C-D9EB-4129-B803-931327F72D5C"
NAME="Microsoft ActiveX Data Objects 2.8 Library"-->
Sub Application_OnStart
' http://msdn.microsoft.com/en-us/library/ms675810.aspx
' http://msdn.microsoft.com/en-us/library/ms681020.aspx
' Use the appropriate connection string (see the above references for details):
' Microsoft SQL Server database:
' http://msdn.microsoft.com/en-us/library/ms677227.aspx
Application("ConnectionString") = "Provider=SQLOLEDB;" _
& "Data Source=SQLServerName;" _
& "Database=MyDatabaseName;" _
& "Integrated Security=SSPI;"
' Microsoft Access 2007, 2010 database:
' http://www.microsoft.com/en-us/download/details.aspx?id=13255#instructions
Application("ConnectionString2") = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & Server.MapPath("/db/MyDatabaseFile.accdb") & ";" _
& "User ID=MyUserID;" _
& "Password=MyPassword;"
' Microsoft Access 2000, 2002, 2003 database:
' http://msdn.microsoft.com/en-us/library/ms681754.aspx
Application("ConnectionString3") = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("/db/MyDatabaseFile.mdb") & ";" _
& "User ID=MyUserID;" _
& "Password=MyPassword;"
End Sub
</script>
Sample ASP file that references the database: example.asp
<%@ Language="VBScript" %>
<% Option Explicit %>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Sample ASP page referencing a database</title>
</head>
<body>
<div id="memberlist">
<%
Call DisplayMemberList
%>
</div>
</body>
</html>
<%
Sub DisplayMemberList
Dim dbConn, cmd, MemberList, RecordCount
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.ConnectionString = Application("ConnectionString") ' obtained from /global.asa file
dbConn.Mode = adModeRead ' http://msdn.microsoft.com/en-us/library/ms675792.aspx
dbConn.CursorLocation = adUseClient ' used to obtain RecordCount
dbConn.Open
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = dbConn ' http://msdn.microsoft.com/en-us/library/ms676107.aspx
cmd.CommandText = "sp_MyStoredProcedure" ' http://msdn.microsoft.com/en-us/library/ms675958.aspx
cmd.CommandType = adCmdStoredProc ' http://msdn.microsoft.com/en-us/library/ms675946.aspx
cmd.Prepared = True ' http://msdn.microsoft.com/en-us/library/ms675106.aspx
Set MemberList = cmd.Execute ' http://msdn.microsoft.com/en-us/library/ms681559.aspx
RecordCount = MemberList.RecordCount
Response.Write "Member count: " & RecordCount & "<br>" & vbcrlf
If RecordCount > 0 Then
Do While Not MemberList.EOF
Response.Write MemberList("FirstName") & " " & MemberList("LastName") _
& " - " & MemberList("Age") & "<br>" & vbcrlf
MemberList.MoveNext
Loop
Else
Response.Write "No records were found." & vbcrlf
End If
MemberList.Close
Set MemberList = Nothing
Set cmd = Nothing
dbConn.Close
Set dbConn = Nothing
End Sub
%>
ColdFusion
ColdFusion 5 users should use OLEDB DSNs (data source names) instead of ODBC DSNs to access their databases. Network Tallahassee can create DSNs on the web server for you.
Sample /Application.cfc file (ColdFusion MX):
<cfcomponent>
<cfset This.name="MyWebSite">
<cffunction name="onApplicationStart">
<cfset application.dsn="MyDSNname">
</cffunction>
</cfcomponent>
Sample /Application.cfm file (ColdFusion 5):
<cfapplication name="MyWebSite">
<cfset application.dsn="MyDSNname">
Sample ColdFusion file that references the database: example.cfm
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Sample ColdFusion page referencing a database</title>
</head>
<body>
<cfquery name="MemberList" datasource="#application.dsn#">
EXECUTE sp_MyStoredProcedure
<!--- or some other SQL command such as
SELECT FirstName, LastName, Age
FROM Members
WHERE Age >= 25
ORDER BY LastName, FirstName
--->
</cfquery>
<div id="memberlist">
<cfoutput query="MemberList">
#FirstName# #LastName# - #Age#<br>
<cfoutput>
</div>
</body>
</html>