p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access ASP (http://p2p.wrox.com/forumdisplay.php?f=104)
-   -   Help with ASP and MS ACCESS. Connection error. (http://p2p.wrox.com/showthread.php?t=18196)

patzblue September 10th, 2004 08:17 AM

Help with ASP and MS ACCESS. Connection error.
 
Hi. I have a project using ASP to connect to an Access Database.

Randomly, I get the error "The connection cannot be used to perform this operation. It is either closed or invalid in this context.".

It's not constant though. Works nice for a week, then I get that error. Does anybody know a way to validate if the connection is on ? I am not an advanced ASP programmer so any help would be more than appreciated. I assume there might be something wrong in my connection string, either that or the settings of the Access Driver on the server. I changed the timeout on the driver and the buffer size, but without any success. Here's my ASP script :

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% Session.CodePage = 65001%>
<%
Dim strSystemDSNName
Dim strDatabaseName
Dim oConn
Dim rs
Dim strSQL
Dim strKey

on error resume next

'************************************************* ****************
strSystemDSNName = "xxx-c5a"
strDatabaseName = "D:\Inetpub\xxx_Database\xxx-c5a.mdb"
'************************************************* ****************

'Request object variables
strKey = Request.Form("Key")

'use request object to build SQL statement
strSQL = "select * from " & "DSCs" & " where dsc_name='" & strKey & "'"


'Create the connection object
Set oConn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

'open the connection to database
oConn.open "Provider=MSDASQL.1;Persist Security Info=False;Data Source="& strSystemDSNName & ";Initial Catalog=" & chr(34) & strDatabaseName &chr(34)

rs.Open strSQL,oConn,adOpenForwardOnly,,adCmdText

thanks a lot.

          Pat



happygv September 10th, 2004 08:42 AM

Looks like you are chosen the wrong connection string for your application. That seems to be an MSSQL server connection string.

Check for connection string samples here.

Connections sample

Connection Strings

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

patzblue September 10th, 2004 09:20 AM

It should be an access connection string, and it works most of the time. That is odd. I'll have a look at the links though.

Something is weird though. I am starting to think the Access Driver might be the problem. Here's why.

1) When I ran the application from my staging server, it was working fine 99% of the time. If the connection error was appearing, I just had to reboot the server. Definitely not the greatest fix, but still.

2) Once moved on the client's server, it worked for about a week before I started getting that error again. But in an odd fashion, as half my files were working while the other ones were not, and they are ALL using the same string, thanks to cut & paste.

3) On the client server, I created a new ODBC link, and it works again fine. But if I recreate the original link, niet.

that's about it. It's really driving me nuts. And thanks a lot for your feedback :)

pat



happygv September 10th, 2004 09:34 AM

Where do you have your .mdb file? Is that staying within the same folder where your asp files reside? And is there any reason for using DSN connection?

_________________________
- Vijay G
Strive for Perfection

patzblue September 10th, 2004 10:04 AM

My MDB file is on the same server. I took it out the "root" of the web server so it wouldn't be accessible directly via the web. So the structure is :

IIS ROOT :
d:\inetpub\mywebsite

my ASP are in a virtual directory at :
d:\inetpub\mywebsite\asp
and my database is at :
d:\inetpub\mydatabase

And no, I have no reason to use the DSN. But to explain, I'm mainly a project manager and a multimedia programmer, with only basic knowledge of SQL and until that project, no knowledge of ASP. So I had a freelancer coding my asp pages for me. I did modify them after but the bulk of the code was coming from someone else, who's used to work with MSQL.

So you are saying I don't need a DSN ? I can simply connect on the DB without it ?

thanks again for the help, you have no idea how much it's appreciated.

Pat



happygv September 10th, 2004 10:21 AM

Yes, you can simply do that without DSN.

Try using this.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\inetpub\mydatabase\yourdb.mdb;User Id=admin;Password=;"

Make sure you give the correct path of your mdb file there.

Cheers!

_________________________
- Vijay G
Strive for Perfection

patzblue September 10th, 2004 12:40 PM

Ok, it finally worked. There's one thing that still mesmerize me.

My database was originally in a :
d:\inetpub\clientname-database\clientname-xxx.mdb

I was getting the error. Someone told me Access might be touchy about hyphens or other chars like that. So I renammed the folder to :
d:\inetpub\clientnamedatabase\xxx.mdb

still getting the error.

then I shortened the name of the folder, just in case :
d:\inetpub\cdb\xxx.mdb

still an error.

So just for the sake of testing, I put my database into
D:\temp\xxx.mdb

and IT WORKED.

so my question is , what the heck was wrong with the other paths ?? it doesn't make sense to me.

and thanks again for the connection string !

Pat




happygv September 11th, 2004 05:43 AM

Hi Pat,

I don't think that was a problem with path. I have used that with being under much lengthy foldernames successfully. It should be with permission is what I suspect. As access creates .ldb file to handle locking under the same folder where the mdb exists, the folder should have enough permissions(READ & WRITE) for the IUSR_Computername. Still the error doesn't seem related to permissions. This connection error you faced usually happens when you err with conn.open or connection string or recordset properties not set to update records in it.

These should be the recordset properties when it need to be updated, which I don't see in your code.
    rs.CursorType=3 'Static cursor
    rs.LockType=3 ' Optimistic lock

Was that resulting in same error or a different error when you tested out with lengthy foldername before moving into TEMP folder? If different what was that showing up.

What I feel is TEMP by default has enough permission for all users, so once you moved it there, it should have worked with no problems.

Try that by having the db within your old path with read & write permission for IIS user, and see if that sorts out the problem.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection


All times are GMT -4. The time now is 10:52 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.