Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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


  #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
  #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


  #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
  #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


  #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
  #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



  #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


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





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