Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access ASP Using ASP with Microsoft Access databases. For Access questions not specific to ASP, please use the Access forum. For more ASP forums, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 10th, 2004, 08:17 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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


Reply With Quote
  #2 (permalink)  
Old September 10th, 2004, 08:42 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old September 10th, 2004, 09:20 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #4 (permalink)  
Old September 10th, 2004, 09:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old September 10th, 2004, 10:04 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #6 (permalink)  
Old September 10th, 2004, 10:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #7 (permalink)  
Old September 10th, 2004, 12:40 PM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
  #8 (permalink)  
Old September 11th, 2004, 05:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error in Asp,net and MS Access DB shankha ADO.NET 1 January 4th, 2006 12:00 PM
Connection Pool on MS Access? Edward King Java Databases 1 August 3rd, 2005 12:48 AM
Database connection error. asp.net/dreamw/access peacesquid Classic ASP Databases 1 July 28th, 2005 06:56 PM
Can't get connection to MS Access pekj JSP Basics 1 June 1st, 2004 04:54 AM
ASP Error with Ms Access file sloynaz Access 1 May 15th, 2004 04:33 AM



All times are GMT -4. The time now is 04:26 AM.


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