ASP Database connection problem

C

Chris Barber

No-one really seems to have bothered trying to explain whats going on which
means that Ray is effectively coding and debugging blind?

Ray, use this code and carefully consider each step.

<%
Option Explicit

'Option explicit ensures that an error will be generated when
'attempting to access a variable that has *not* been declared

Dim pobjConnection 'The connection object - if an error occurs this will
be set to Nothing
Dim pstrConnectionString 'Connection string that defines the connection
properties.

'The URL that would correspond to the database if accessed through the web
browser.
Dim pstrDatabaseURL
'The filepathname where the DB resides *on* the webserver as defined by the
local file system.
Dim pstrDatabaseFilePathNameLocal
'The database OLEDB provider
Dim pstrDatabaseProvider

'Define and initialise the connection object (not connected to the DB yet
but exists as an object).
Set pobjConnection = Server.CreateObject("ADODB.Connection")

'Now we define the connection string from a couple of key variables.
pstrDatabaseURL = "\fpdb\books.mdb"
pstrDatabaseProvider = "Microsoft.Jet.OLEDB.4.0"

'Let IIS convert this database URL into a local filesystem path since that's
what ADO expects.
pstrDatabaseFilePathNameLocal = Server.MapPath(pstrDatabaseURL)

'Generate the connection string being careful with the syntax.
pstrConnectionString = "Provider=" & pstrDatabaseProvider
pstrConnectionString = pstrConnectionString & ";Data Source=" &
pstrDatabaseFilePathNameLocal

'You may want to debug these values here before continuing - this line will
report the connection string.
Response.Write("ConnectionString = '" & pstrConnectionString & "'<br/>")

'Try to open the connection now and catch any errors.
On Error Resume Next
pobjConnection.Open(pstrConnectionString)

'Display the error information if required.
If Err.Number <> 0 Then
Response.Write "Open connection returned an error.<br/>"
Response.Write "Description: " & Err.Description
Err.Clear
End If

'Reset the error handling again to the normal method (raise error).
On Error Goto 0

If Not(pobjConnection Is Nothing) Then
Response.Write "Connection successfully opened.<br/>"
Else
Response.Write "No error reported but the connection object returned as
[Nothing].<br/>"
End If

'Now close the connection - not really required but it's good practice.
pobjConnection.Close

Set pobjConnection = Nothing
%>

Keep in mind that this is *all* occurring on the webserver as if you were
sat at it executing the script yourself (security implications aside).

Also be very careful that the posted script is not broken due to line
wrapping at 76 characters (news posts do this).

There is a chance that if your MDB is outside the web structure that the
current context user for the ASP session may not have sufficient rights to
access it. It's more secure to have it outside the web structure but more
difficult to maintain remotely.

Hope this helps,

Chris.


......now it says
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Websites\hdo212\notjustbooks\fpdb\books.mdb

which is wrong! It seems to be looking on my local system rather than my web
server. But it's progress!
 
C

Chris Barber

Dear God text-wrapping at 76 characters gives me a really bad hair day.

The most obvious one that may catch you out is this line:

pstrConnectionString = pstrConnectionString & ";Data Source=" &
pstrDatabaseFilePathNameLocal

It should all be on one line.

Chris.

No-one really seems to have bothered trying to explain whats going on which
means that Ray is effectively coding and debugging blind?

Ray, use this code and carefully consider each step.

<%
Option Explicit

'Option explicit ensures that an error will be generated when
'attempting to access a variable that has *not* been declared

Dim pobjConnection 'The connection object - if an error occurs this will
be set to Nothing
Dim pstrConnectionString 'Connection string that defines the connection
properties.

'The URL that would correspond to the database if accessed through the web
browser.
Dim pstrDatabaseURL
'The filepathname where the DB resides *on* the webserver as defined by the
local file system.
Dim pstrDatabaseFilePathNameLocal
'The database OLEDB provider
Dim pstrDatabaseProvider

'Define and initialise the connection object (not connected to the DB yet
but exists as an object).
Set pobjConnection = Server.CreateObject("ADODB.Connection")

'Now we define the connection string from a couple of key variables.
pstrDatabaseURL = "\fpdb\books.mdb"
pstrDatabaseProvider = "Microsoft.Jet.OLEDB.4.0"

'Let IIS convert this database URL into a local filesystem path since that's
what ADO expects.
pstrDatabaseFilePathNameLocal = Server.MapPath(pstrDatabaseURL)

'Generate the connection string being careful with the syntax.
pstrConnectionString = "Provider=" & pstrDatabaseProvider
pstrConnectionString = pstrConnectionString & ";Data Source=" &
pstrDatabaseFilePathNameLocal

'You may want to debug these values here before continuing - this line will
report the connection string.
Response.Write("ConnectionString = '" & pstrConnectionString & "'<br/>")

'Try to open the connection now and catch any errors.
On Error Resume Next
pobjConnection.Open(pstrConnectionString)

'Display the error information if required.
If Err.Number <> 0 Then
Response.Write "Open connection returned an error.<br/>"
Response.Write "Description: " & Err.Description
Err.Clear
End If

'Reset the error handling again to the normal method (raise error).
On Error Goto 0

If Not(pobjConnection Is Nothing) Then
Response.Write "Connection successfully opened.<br/>"
Else
Response.Write "No error reported but the connection object returned as
[Nothing].<br/>"
End If

'Now close the connection - not really required but it's good practice.
pobjConnection.Close

Set pobjConnection = Nothing
%>

Keep in mind that this is *all* occurring on the webserver as if you were
sat at it executing the script yourself (security implications aside).

Also be very careful that the posted script is not broken due to line
wrapping at 76 characters (news posts do this).

There is a chance that if your MDB is outside the web structure that the
current context user for the ASP session may not have sufficient rights to
access it. It's more secure to have it outside the web structure but more
difficult to maintain remotely.

Hope this helps,

Chris.


......now it says
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Websites\hdo212\notjustbooks\fpdb\books.mdb

which is wrong! It seems to be looking on my local system rather than my web
server. But it's progress!
 
B

Bob Barrows

Chris said:
pstrDatabaseURL = "\fpdb\books.mdb"

LOL
You've made the same mistake we did! :)
URLs have front slashes, not backslashes!

pstrDatabaseURL = "/fpdb/books.mdb"

<snip of the rest of the correct explanation>
Bob
 
C

Chris Barber

Dear gawd - I'd better go off and put the Dunces cap on - I even read all the posts and tried to not
do that!

Although - doesn't really matter in ASP VBScript does it - only Javascript cares about it since
VBScript has no '\' escape character?

Thanks Bob.

Chris.

Chris said:
pstrDatabaseURL = "\fpdb\books.mdb"

LOL
You've made the same mistake we did! :)
URLs have front slashes, not backslashes!

pstrDatabaseURL = "/fpdb/books.mdb"

<snip of the rest of the correct explanation>
Bob
 
R

Roland Hall

in message : .....now it says
: Provider=Microsoft.Jet.OLEDB.4.0;Data
: Source=C:\Websites\hdo212\notjustbooks\fpdb\books.mdb
:
: which is wrong! It seems to be looking on my local system rather than my
web
: server. But it's progress!

Hey Ray...

Run this on a new page, on the webserver and tell us what you get.

<%@ Language=VBScript %>
<%
Response.Write(Request.ServerVariables("PATH_TRANSLATED"))
%>

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
 
B

Bob Barrows [MVP]

Chris said:
Dear gawd - I'd better go off and put the Dunces cap on - I even read
all the posts and tried to not do that!

Although - doesn't really matter in ASP VBScript does it - only
Javascript cares about it since VBScript has no '\' escape character?
Hmm, I guess you're right. I could have sworn I received an error when using
backslashes in the past, but I've just tested it with this:

Response.Write server.MapPath("w2kmigration.mdb") & "<BR>"
Response.Write server.MapPath("..\test_Local\w2kmigration.mdb") & "<BR>"
Response.Write server.MapPath("../test_Local/w2kmigration.mdb") & "<BR>"

and got this result:
C:\Visual Studio Projects\test\test_Local\w2kmigration.mdb
C:\Visual Studio Projects\test\test_Local\w2kmigration.mdb
C:\Visual Studio Projects\test\test_Local\w2kmigration.mdb

In fact, this even worked (watch for line break - should be single line):
Response.Write server.MapPath(".\Visual Studio
Projects\test\test_Local\w2kmigration.mdb")

It isn't until I try to use the C: that I get the message I remember
getting:
Error Type:
Server.MapPath(), ASP 0172 (0x80004005)
The Path parameter for the MapPath method must be a virtual path. A physical
path was used.

I was thinking it used the slash-type to determine when a physical path was
being supplied. Learn something new every day ...

Bob
 
C

Chris Barber

I believe that you can even break out of the web root using ../../ but this can be disabled in the
IIS setting for the website since it represent a security hole where relative paths are submitted.

Chris.

Chris said:
Dear gawd - I'd better go off and put the Dunces cap on - I even read
all the posts and tried to not do that!

Although - doesn't really matter in ASP VBScript does it - only
Javascript cares about it since VBScript has no '\' escape character?
Hmm, I guess you're right. I could have sworn I received an error when using
backslashes in the past, but I've just tested it with this:

Response.Write server.MapPath("w2kmigration.mdb") & "<BR>"
Response.Write server.MapPath("..\test_Local\w2kmigration.mdb") & "<BR>"
Response.Write server.MapPath("../test_Local/w2kmigration.mdb") & "<BR>"

and got this result:
C:\Visual Studio Projects\test\test_Local\w2kmigration.mdb
C:\Visual Studio Projects\test\test_Local\w2kmigration.mdb
C:\Visual Studio Projects\test\test_Local\w2kmigration.mdb

In fact, this even worked (watch for line break - should be single line):
Response.Write server.MapPath(".\Visual Studio
Projects\test\test_Local\w2kmigration.mdb")

It isn't until I try to use the C: that I get the message I remember
getting:
Error Type:
Server.MapPath(), ASP 0172 (0x80004005)
The Path parameter for the MapPath method must be a virtual path. A physical
path was used.

I was thinking it used the slash-type to determine when a physical path was
being supplied. Learn something new every day ...

Bob
 
R

Ray

Thanks Chris
I've done all of that, and I get absolutely nothing back. Not one word or
anything at all.

Ray
Chris Barber said:
Dear God text-wrapping at 76 characters gives me a really bad hair day.

The most obvious one that may catch you out is this line:

pstrConnectionString = pstrConnectionString & ";Data Source=" &
pstrDatabaseFilePathNameLocal

It should all be on one line.

Chris.

No-one really seems to have bothered trying to explain whats going on which
means that Ray is effectively coding and debugging blind?

Ray, use this code and carefully consider each step.

<%
Option Explicit

'Option explicit ensures that an error will be generated when
'attempting to access a variable that has *not* been declared

Dim pobjConnection 'The connection object - if an error occurs this will
be set to Nothing
Dim pstrConnectionString 'Connection string that defines the connection
properties.

'The URL that would correspond to the database if accessed through the web
browser.
Dim pstrDatabaseURL
'The filepathname where the DB resides *on* the webserver as defined by the
local file system.
Dim pstrDatabaseFilePathNameLocal
'The database OLEDB provider
Dim pstrDatabaseProvider

'Define and initialise the connection object (not connected to the DB yet
but exists as an object).
Set pobjConnection = Server.CreateObject("ADODB.Connection")

'Now we define the connection string from a couple of key variables.
pstrDatabaseURL = "\fpdb\books.mdb"
pstrDatabaseProvider = "Microsoft.Jet.OLEDB.4.0"

'Let IIS convert this database URL into a local filesystem path since that's
what ADO expects.
pstrDatabaseFilePathNameLocal = Server.MapPath(pstrDatabaseURL)

'Generate the connection string being careful with the syntax.
pstrConnectionString = "Provider=" & pstrDatabaseProvider
pstrConnectionString = pstrConnectionString & ";Data Source=" &
pstrDatabaseFilePathNameLocal

'You may want to debug these values here before continuing - this line will
report the connection string.
Response.Write("ConnectionString = '" & pstrConnectionString & "'<br/>")

'Try to open the connection now and catch any errors.
On Error Resume Next
pobjConnection.Open(pstrConnectionString)

'Display the error information if required.
If Err.Number <> 0 Then
Response.Write "Open connection returned an error.<br/>"
Response.Write "Description: " & Err.Description
Err.Clear
End If

'Reset the error handling again to the normal method (raise error).
On Error Goto 0

If Not(pobjConnection Is Nothing) Then
Response.Write "Connection successfully opened.<br/>"
Else
Response.Write "No error reported but the connection object returned as
[Nothing].<br/>"
End If

'Now close the connection - not really required but it's good practice.
pobjConnection.Close

Set pobjConnection = Nothing
%>

Keep in mind that this is *all* occurring on the webserver as if you were
sat at it executing the script yourself (security implications aside).

Also be very careful that the posted script is not broken due to line
wrapping at 76 characters (news posts do this).

There is a chance that if your MDB is outside the web structure that the
current context user for the ASP session may not have sufficient rights to
access it. It's more secure to have it outside the web structure but more
difficult to maintain remotely.

Hope this helps,

Chris.


.....now it says
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Websites\hdo212\notjustbooks\fpdb\books.mdb

which is wrong! It seems to be looking on my local system rather than my web
server. But it's progress!



Bob Barrows said:
Oops - I forgot to correct your slashes. Server.MapPath requires a url as
the argument.

Not this:

This:
sConnect ="Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/fpdb/books.mdb")



This line needs to go:
**************************************

That error is covered is in this article:
http://www.aspfaq.com/show.asp?id=2009 - 80004005 errors

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
 
A

Alont

what asp editor do you use? I'm using EDITPLUS, it really worse than
VB on write and test code, so I'm searching for a good editor, which
have features as you said could recognize "Option Explicit" so that I
could find my error easier, I'm sorry my english is so poor, hope you
could undersand what I said, thank you :)


at Mon, 26 Apr 2004 14:39:03 -0400
Bob Barrows said:
Don't forget to declare your variables. Use Option Explicit (it needs to be
the first line in your code) to avoid errors.

Option Explicit
dim conn


I'm sorry my english is so poor,
so forgive me if my word had make you uncomfortable.
I'll try my best to express my question correctly
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,479
Members
44,900
Latest member
Nell636132

Latest Threads

Top