Solved

Create An ASP Database Connection in FrontPage

Posted on 2004-04-23
19
1,312 Views
Last Modified: 2013-12-24
I need to be able to create database connections to two databases on my website, one has a DSN and the other does not. I've had problems trying to do this and I'm wondering how I should accomplish this. Can you give me an example of the basic coding for creating these two types of connections? The databases ARE stored in the fpdb folder that FrontPage recommends.
0
Comment
Question by:wcameron
  • 7
  • 6
  • 3
  • +2
19 Comments
 
LVL 12

Expert Comment

by:rcmb
ID: 10906655
Did you create a database connection to each when you imported the files (this can be done with or without a dsn)?

Click on Tools, Web Settings, Database to create a connection to your databases.

Once that is done --- FrontPage has a database results wizard that will build your connections for you.

Open a blank page and click on Insert, Database, Results and perform the five step wizard.

Step 1 - Select a connection (we created at the top)
Step 2 - Select a table within the database connection (or create a custom query)
Step 3 - Select the fields to display
Step 4 - Select the format to display
Step 5 - Select the number of records to display at one time (or all records)

RCMB
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10906669
Or if you just desire the straight code to build a database connection check out this link - it even has a tutorial

http://www.aspwebpro.com/aspscripts/database/dbconnection.asp

RCMB
0
 
LVL 14

Expert Comment

by:hhammash
ID: 10907152
Hi wcameron,

If your website is hosted with a company,  call them and get the full path of the database.  It should have the name of the physical drive:

<%
set conn= server.createobject("ADODB.Connection")
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=c:\web\YourFolder\AnotherFolder\DatabaseName.mdb"
%>

But If you can't reach anyone and you don't have access to the database or you don't know the full path,  use the Server.MapPath as in:  

<%
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("/mydatabase.mdb") & ";"
objConn.Open
%>

You can find the above code in the link that has been given to you by RCMB.

I always use the Server.MapPath and it works fine.  Try it.  It is in the link of RCMB's last reply to you.

hhammash


0
 
LVL 3

Author Comment

by:wcameron
ID: 10909282
Thanks. I'm getting closer. I have a working connection and have tried placing it into an include file in the directory _DBConn.

Everything works but depending upon the directory into which the asp page is placed it may not be able to find the database file. I get the following error:

Microsoft JET Database Engine error '80004005'

'd:\64.225.57.5\birds\test\fpdb\Birding.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

D:\64.225.57.5\BIRDS\TEST\TEST\../../../_DBConn/Birding.asp, line 6

It seems to defeat the purpose of using an include file if you can only place your asp pages in a single directory. What am I doing wrong? Here is the code from my include file:

<%
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("../fpdb/Birding.mdb") & ";"
objConn.Open
%>
0
 
LVL 3

Author Comment

by:wcameron
ID: 10909326
You can see a working version at:
http://www.mountainnature.com/birds/test/dbconn.asp
0
 
LVL 31

Expert Comment

by:seanpowell
ID: 10910001
Ward, your page seems to be working now. Are you up and running?
0
 
LVL 3

Author Comment

by:wcameron
ID: 10912347
Yes. I'm up and running, but my last comment illustrates the problem. I use an include file for my connection but it does not allow me to have my files in various folder depths on the site. If I move an asp page from root/test to root/test/test suddenly the include file does not find the database connection.

How do I get past this problem?
0
 
LVL 12

Assisted Solution

by:rcmb
rcmb earned 125 total points
ID: 10912910
Server.MapPath returns the physical path to the script, e.g. 'c:\website\', as long as the database is in the same folder as the script it now has the physical path to the database and the database name.

Try using an actual path like:

<%
set cnn = server.createobject("ADODB.Connection")
cnn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\directory\subdirectory\anydatabase.mdb"
%>
RCMB
0
 
LVL 14

Expert Comment

by:hhammash
ID: 10912940
Hi Wcameron,

in you path use ../ for each up level

If your inlcluded file is in webfolder/MyFiles/IncludeFile/filename

you can use ../../IncludeFile/filename


0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 3

Author Comment

by:wcameron
ID: 10913157
I did that (see my code in previous post). In my include file, the path is correctly placed and works. The challenge is in where I place the asp files. With FrontPage includes, I can move the files anywhere and as long as the references in the include are correct everything works. With the <!--#include file="../../_DBConn/Birding.asp"--> statement relative references don't seem to be updated. I've placed two exact copies of the same file, one is placed one level further down. I've corrected the include file reference by adding an additional ../ so that it can find the include file. Unfortunately I get an error because the include file can't seem to find the database. Here are the two links:

http://www.mountainnature.com/birds/test/dbconn2.asp
http://www.mountainnature.com/birds/test/test/dbconn2.asp

It seems that I can only place my asp files at the same depth beneath the root or I get the error.
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10913230
Instead of include file try include virtual

<!-- #include virtual = "path/filename.asp" -->

RCMB
0
 
LVL 3

Author Comment

by:wcameron
ID: 10914426
What is the difference?
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10914528
"#include virtual=" means the page's path is specified relative to the web root. "#include file=" means the path is specified as relative to the current page's directory.

Look at this thread:

http://www.codeproject.com/asp/fakeframe.asp?df=100&forumid=906&exp=0&select=3684

RCMB
0
 
LVL 3

Author Comment

by:wcameron
ID: 10915744
I've tried it, but now I get the message that my include file cannot be found, regardless of whether I put the file reference releative to the current file locations OR the root. Is there some syntax that I'm missing?
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10917353
I use a logon system that accesses an Access database. At the top of each page where I desire to verify rights to the page I place the following:

<!--#include virtual="/logon/_private/logon.inc"-->

This path starts at the root of the web and goes down a directory to the _private and then to the logon.inc file. This code works on any page as it always starts at the root.

Below is the contents of the logon.inc. As you can see from the code I included a path to the database that also begins at the root (Const MDB_URL). This works very well and I am always able to access the database no matter what level I am at in the web.

Hope this helps,
RCMB

<%
  ' Do not cache this page.
  Response.CacheControl = "no-cache"

  ' Define the name of the users table.
  Const USERS_TABLE  = "tblUsers"
  ' Define the path to the logon page.
  Const LOGON_PAGE   = "/logon/logon.asp"
  ' Define the path to the logon database.
  Const MDB_URL      = "/logon/_private/logon.mdb"

  ' Check to see whether you have a current user name.
  If Len(Session("UID")) = 0 Then
    ' Are you currently on the logon page?
    If LCase(LOGON_PAGE) <> LCase(Request.ServerVariables("URL")) Then
      ' If not, set a session variable for the page that made the request...
      Session("REFERRER") = Request.ServerVariables("URL")
      ' ...and redirect to the logon page.
      Response.Redirect LOGON_PAGE
    End If
  End If

  ' This function checks for a username/password combination.
  Function ComparePassword(UID,PWD)
    ' Define your variables.
    Dim strSQL, objCN, objRS
    ' Set up your SQL string.
    strSQL = "SELECT * FROM " & USERS_TABLE & " WHERE (UID='" & UID & "' AND PWD='" & PWD & "');"
    ' Create a database connection object.
    Set objCN = Server.CreateObject("ADODB.Connection")
    ' Open the database connection object.
    objCN.Open "driver={Microsoft Access Driver (*.mdb)}; dbq=" & Server.MapPath(MDB_URL) & "; uid=admin; pwd="
    ' Run the database query.
    Set objRS = objCN.Execute(strSQL)
    ' Set the status to true/false for the database lookup.
    ComparePassword = Not(objRS.EOF)
    ' Close your database objects.
    Set objRS = Nothing
    Set objCN = Nothing
  End Function
%>
0
 
LVL 19

Accepted Solution

by:
webwoman earned 125 total points
ID: 10918803
Create a NEW page, put it where you know the path to the database.

Write out the path to the database so you know EXACTLY what it is.

You can do it this way...

<%=server.mappath("mydatabase.mdb")%>

You're not making a connection, you just want to see the actual physical path.

Then when you have that, use IT in your connection string. No server.mappath, use the actual, physical path to the database.

It should look something like this...
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myfiles\fpdb\Birding.mdb;"

Then you can use it from ANY folder, from ANY page, anywhere on your site, and it will work.
0
 
LVL 19

Expert Comment

by:webwoman
ID: 10918908
Sorry rcmb, didn't see that you'd posted that before.

As for includes, I'll again second using virtual instead of file.

Remember, you need to use BOTH -- the physical path to the database, NOT server.mappath, AND use virtual instead of file for including the connection include file.

So your statement would be
<!--#include virtual="first_level/second_level/from_root.asp" -->
(no opening slash, all paths are from the web root regardless of where your files are)
and it would be the same REGARDLESS of where the file was.
0
 
LVL 3

Author Comment

by:wcameron
ID: 10921500
Thanks webwoman, that gets me closer. I think I might drop the whole include file thing. I like your way of getting the physical path but I also use my laptop as a server to test the pages and if I use the physical path for the files location on my ISP's server than I won't be able to run the asp pages on my test server when I don't have a live Internet connection - or will I?
0
 
LVL 19

Expert Comment

by:webwoman
ID: 10922308
You will if you can duplicate the file structure on your ISP. Otherwise, no -- but you CAN have a second include file that has the ISP structure. Upload that one to your ISP and use the other one on your test server.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now