|
Virtual Domain Hosting
Database Access
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.
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/library/en-us/ado270/htm/mdproconnectionstring.asp
' http://msdn.microsoft.com/library/en-us/ado270/htm/mdrefadoprovinfo.asp
' Use the appropriate connection string (see the above references for details):
' Microsoft SQL Server database:
' http://msdn.microsoft.com/library/en-us/ado270/htm/mdrefsqlprovspec.asp
Application("ConnectionString") = "Provider=SQLOLEDB;" _
& "Data Source=SQLServerAddress;" _
& "Database=MyDatabaseName;" _
& "User ID=WebUser;" _
& "Password=;"
' Microsoft Access database:
' http://msdn.microsoft.com/library/en-us/ado270/htm/mdrefjetprovspec.asp
Application("ConnectionString") = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("/db/MyDatabaseFile.mdb") & ";" _
& "User ID=MyUserID;" _
& "Password=MyPassword;"
End Sub
</SCRIPT> |
| Sample include file: /ssi/dbconn.inc.asp |
<%
Dim dbconn
Set dbconn = Server.CreateObject("ADODB.Connection")
dbconn.ConnectionString = Application("ConnectionString") ' obtained from the /global.asa file
' Set the connection permissions--use adModeRead (read-only) when possible:
dbconn.Mode = adModeRead ' http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstconnectmodeenum.asp
%> |
| Sample ASP file that references the database: example.asp |
<%@ Language=VBScript %>
<% Option Explicit %>
<!--#INCLUDE VIRTUAL="/ssi/dbconn.inc.asp"-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<link href="/ssi/default.css" rel="stylesheet" type="text/css">
<title>Sample ASP page referencing a database</title>
</head>
<body>
<!-- Various other HTML code... -->
<%
' If necessary, override your default database connection permissions
' obtained from the include file referenced above:
' dbconn.Mode = adModeWrite ' http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstconnectmodeenum.asp
dbconn.Open
Dim SQL, MemberList
SQL = "EXECUTE sp_MyStoredProcedure"
' or some other SQL command such as
' "SELECT FirstName, LastName, Age FROM Members
' WHERE Age > 25 ORDER BY LastName, FirstName"
Set MemberList = dbconn.Execute(SQL,,adCmdText)
Do While Not MemberList.EOF
Response.Write MemberList("FirstName") & " " & MemberList("LastName") _
& " - " & MemberList("Age") & "<br>" & vbcrlf
MemberList.MoveNext
Loop
' Clean up as soon as you're finished using the database!
MemberList.Close
Set MemberList = Nothing
dbconn.Close
%>
<!-- Various other HTML code... -->
</body>
</html> |
ColdFusion 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. See the following information at Macromedia's website:
Using Microsoft Access Databases in a Production Environment (also includes information about using OLEDB and ODBC)
| Sample /application.cfm file: |
<CFAPPLICATION NAME="MyWebSite">
<CFSET application.dsn="MyDSNname"> |
| Sample ColdFusion file that references the database: example.cfm |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<link href="/ssi/default.css" rel="stylesheet" type="text/css">
<title>Sample ColdFusion page referencing a database</title>
</head>
<body>
<!--- Various other code... --->
<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>
<CFOUTPUT QUERY="MemberList">
#FirstName# #LastName# - #Age#<br>
<CFOUTPUT>
<!--- Various other code... --->
</body>
</html> |
|