Perplexing connection problem - Win2K ASP/Jet DB

[Peter O'Reilly]

I am experiencing a problem with connecting to a MS Jet database (Access 97
mdb file format) via a classic ASP page running under Win2K/IIS server in a
shared hosting environment.

Here is the error information:
Err.Number: 3709
Err.Description: The connection cannot be used to perform this operation. It
is either closed or invalid in this context.

This error information is reported by the MS ADO connection error object.

Here is the relevant snippet of code:
Set con = CreateObject("ADODB.Connection")
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strDBfile ' strDBfile - variable name for the full file path of the mdb
file
con.Open

I've also tried specifying: Provider=Microsoft.Jet.OLEDB.3.5 without any
luck

Note, the odd thing is this problem is ***intermittent***. In other words
the asp page page will do its data processing successfully, without incident
for days on end. On a particular day, the asp page will fail and report the
above message. Repeated requests will subsequently fail and report the same
error.

I'm really perplexed. I have no idea what is triggering this problem.
After "some time" (i.e. hours, couple of days) this connection problem will
seemingly go away. For days on end, I can then run the page successfully
only for this problem to reoccur again some time in the future.

Searching the web, I see others reporting this problem. Their problem is
with syntax like not having a correct connection string or specifying enough
parameters for the recordset open method. Being very experienced with ADO
and ASP, I don't think such is the case for me. I believe my problem has
something to do with the operating environment. I have found no such
comments from others or documentation to support this.

I still think it is a problem with the operating environement
(ASP/Jet/OLEDB), here's why:
I have no such problem ever using the same connection string for an
ASP.NET/ADO.NET application. For instance, I have two ASP.NET applications
with each accessing their own respective mdb file. ADO.NET works flawlessly
24-7, even when my two administrator ASP pages incurr the connection error
above.

I queried my shared hosting web provider and they have no clue. Anyone else
experience such behavoir?

Thanks!
--
Peter O'Reilly

[Jeff Cochran]

On Wed, 14 Jul 2004 13:13:00 -0400, "Peter O'Reilly"
<Peter_OReilly@timeinc.com!N!O!.S!P!AM!> wrote:

>I am experiencing a problem with connecting to a MS Jet database (Access 97
>mdb file format) via a classic ASP page running under Win2K/IIS server in a
>shared hosting environment.
>
>Here is the error information:
>Err.Number: 3709
>Err.Description: The connection cannot be used to perform this operation. It
>is either closed or invalid in this context.
>
>This error information is reported by the MS ADO connection error object.

How about full error messages, including event log entries? And if
you think the code below is relevant, give us line numbers for the
errors and a corresponding snippet.

>Here is the relevant snippet of code:
> Set con = CreateObject("ADODB.Connection")
> con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
>strDBfile ' strDBfile - variable name for the full file path of the mdb
>file
> con.Open
>
>I've also tried specifying: Provider=Microsoft.Jet.OLEDB.3.5 without any
>luck
>
>Note, the odd thing is this problem is ***intermittent***. In other words
>the asp page page will do its data processing successfully, without incident
>for days on end. On a particular day, the asp page will fail and report the
>above message. Repeated requests will subsequently fail and report the same
>error.

Then it's likely not the connection string, since that's always the
same. It's more likely a code error with a variable, or a system
problem. Both of which you need to post full errors for help in
tracking down.

Jeff

>
>I'm really perplexed. I have no idea what is triggering this problem.
>After "some time" (i.e. hours, couple of days) this connection problem will
>seemingly go away. For days on end, I can then run the page successfully
>only for this problem to reoccur again some time in the future.
>
>Searching the web, I see others reporting this problem. Their problem is
>with syntax like not having a correct connection string or specifying enough
>parameters for the recordset open method. Being very experienced with ADO
>and ASP, I don't think such is the case for me. I believe my problem has
>something to do with the operating environment. I have found no such
>comments from others or documentation to support this.
>
>I still think it is a problem with the operating environement
>(ASP/Jet/OLEDB), here's why:
>I have no such problem ever using the same connection string for an
>ASP.NET/ADO.NET application. For instance, I have two ASP.NET applications
>with each accessing their own respective mdb file. ADO.NET works flawlessly
>24-7, even when my two administrator ASP pages incurr the connection error
>above.
>
>I queried my shared hosting web provider and they have no clue. Anyone else
>experience such behavoir?
>
>Thanks!

[Peter O'Reilly]

Thanks for the reply.

I never implied it was a connection string problem; I listed such just
because if I didn't, someone would ask to see it as that is the most common
culprit. It's a shared hosting environment so I do not have access to the
event log. The error message I posted is the "full" error message.
Unfortunately there is no more information to grab other than that morsel of
information the Jet OLEDB provider has furnished.

--
Peter O'Reilly

[Jeff Cochran]

On Wed, 14 Jul 2004 16:30:25 -0400, "Peter O'Reilly"
<Peter_OReilly@timeinc.com!N!O!.S!P!AM!> wrote:

>I never implied it was a connection string problem; I listed such just
>because if I didn't, someone would ask to see it as that is the most common
>culprit. It's a shared hosting environment so I do not have access to the
>event log. The error message I posted is the "full" error message.
>Unfortunately there is no more information to grab other than that morsel of
>information the Jet OLEDB provider has furnished.

You may want to ask your host then, or possibly find another host. If
you create a simple page that reads data from a database, does it have
similar issues?

Jeff

[Bob Barrows [MVP]]

Peter O'Reilly wrote:

> I am experiencing a problem with connecting to a MS Jet database
> (Access 97 mdb file format) via a classic ASP page running under
> Win2K/IIS server in a shared hosting environment.
>
> Here is the error information:
> Err.Number: 3709
> Err.Description: The connection cannot be used to perform this
> operation. It is either closed or invalid in this context.
>

Believe it or not, this error is usually not the result of a failed
connection. The "It is closed" part, while it is a possible cause, is
usually not the cause. It's the "invalid in this context" part that should
be concentrated on.

You first need to determine the line of code that raises this error. Then,
prove to yourself that the connection is still open by checking the
connection's state (conn.State) immediately before this line of code is
executed. If it is an intermittent problem (which usually means that the
problem is data-related), then log the date and time to a file if the
connection ever turns out to be really closed when it should be open.

Once you have convinced yourself that a closed connection is not the
problem, check this article for other causes of this error.

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Peter O'Reilly]

> Once you have convinced yourself that a closed connection is not the

> problem, check this article for other causes of this error.
>

It is failing on the Open method with 100% certainty.
Which article? (It's missing from your post.)

Thanks.
--
Peter O'Reilly

[Peter O'Reilly]

> You may want to ask your host then, or possibly find another host. If

> you create a simple page that reads data from a database, does it have
> similar issues?

This was covered and answered in the original post. Thanks.
--
Peter O'Reilly

[Bob Barrows [MVP]]

Peter O'Reilly wrote:

>> Once you have convinced yourself that a closed connection is not the
>> problem, check this article for other causes of this error.
>>

> It is failing on the Open method with 100% certainty.

??
You mean the connection is failing to open in the first place? With that
error message? I've never seen that error generated by a conn.Open
statement. Are you sure?

You said you tried "Provider=Microsoft.Jet.OLEDB.3.5 ". That should be
"Provider=Microsoft.Jet.OLEDB.3.51"

> Which article? (It's missing from your post.)

Oops
[url]http://www.aspfaq.com/show.asp?id=2285[/url]
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"

[Aaron [SQL Server MVP]]

> It is failing on the Open method with 100% certainty.

That doesn't sound right at all. That error message makes absolutely no
sense in relation to an Open call.

[Peter O'Reilly]

>That doesn't sound right at all. That error message makes absolutely no

>sense in relation to an Open call.

I fully agree and am equally perplexed. I think it has something to do with
the operating environment rather than the code. (Please see my last reply
to Bob Barrows as to why).

Thanks.
--
Peter O'Reilly

[Bob Barrows [MVP]]

Peter O'Reilly wrote:

> Thanks for the article. Yes, it is failing on the open method, if
> not of the connection object, then the recordset object.

I'm not sure how to interpret this statement. Are you saying the error
occurs sometimes on con.Open and other times on rs.Open statements? Or are
you saying you aren't sure which statement is generating the error? If so,
you need to be sure.

> I tried
> specifying 3.5.1,
> 3.5, 4.0 and it makes no difference.

Not "3.5.1": "3.51" But I doubt this will make any difference. And if you
don't have the Jet 3.51 components installed on your server, you will find
out when you get the "Provider not found" error.

> Again, this error does not
> happen consistently or very often, but when it does fail, repeated
> requests fail with the same error. That's why my original contention
> is it having something to do with the operating environment, i.e a
> threading issue with the ASP dll or some OLEDB library file.
>
> I've revisited, inspected my code quite a bit and on several
> occasions. Attach is a copy of my web page. It's operation is quite
> simple. In short, it's just a command window for executing SQL
> commands against an MDB database file. (A utility page quite useful
> if one has an application running under a shared hosting site.) It's
> heavily commented and well/tightly structured - being thorough with
> closing and destroying objects and trapping errors. I can't imagine
> giving this code any more of a white glove treatment than it already
> has.
>

OK, I'm reviewing the code and I will comment as I see issues. The order of
the comments has nothing to do with the importance of the issues, unless I
come across something I consider to be the source of your problem:

1. There is no need to repeat "On Error Resume Next" as you do in your
Main() sub (and now that I've moved on, I see that you do this in other
procedures as well). This statement remains in effect until either an "On
Error GoTo 0" statement is executed, or the code goes out of scope.
2. You go to a lot of trouble to get the database folder's path when you
could simply do
Server.MapPath("/database/rideSub.mdb")

3. I would add Err.Clear in your error-handling code. Like this:

If Err.Number <> 0 Then
strErrMsg = strErrorReport("conGetConnection(): Opening connection", _
"MDB Filename: " & strDBname) & strConnErrors(con)
Err.Clear
Call KillConnection(con)
End If

4. There are several issues here (none that are fatal):

Set rs = CreateObject("ADODB.Recordset")
' Options: 0 = adOpenForwardOnly, 1 = adLockReadOnly
rs.CursorLocation = 3 ' adUseClient
rs.MaxRecords = intMAX_ROWS

rs.Open strSELECT_SQL, con, 0, 1

a. You've used incompatible settings. The only cursortype available with a
client-side cursor is adOpenStatic. No matter what cursortype you ask for,
if the cursorlocation is adUseClient, the only cursortype you will get is
adOpenStatic. May I ask why you want a client-side cursor here? A
server-side cursor would seem to suit your purposes. Instead of the above
lines

Set rs = con.Execute(strSELECT_SQL,,1)

b. You should specify the CommandType argument in your Open statement (if
you decide to keep using the client-side cursor):
'1=adCmdText
rs.Open strSELECT_SQL, con, 0, 1,1

5. Similarly:
Call con.Execute(strSQL, lngRecordsAffectedCount, 1) ' 1 = adCmdText

You should specify the Execution argument
'129 = 1 (adCmdText) + 128 (adExecuteNoRecords)
Call con.Execute(strSQL, lngRecordsAffectedCount, 129)

I do not see anything that could cause this error, especially if the error
is occurring on an Open statement.

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"

[Aaron [SQL Server MVP]]

> > Thanks for the article. Yes, it is failing on the open method, if

> > not of the connection object, then the recordset object.

>
> I'm not sure how to interpret this statement. Are you saying the error
> occurs sometimes on con.Open and other times on rs.Open statements? Or are
> you saying you aren't sure which statement is generating the error? If so,
> you need to be sure.

I've been confused for the better part of today too, since previously, Peter
said:

> It is failing on the Open method with 100% certainty.

[Peter O'Reilly]

Since I am unable to reproduce the error at the moment - that last chance I
had to study it, it was failing on the recordset open method.
That is not to say it does not happen on the connection open method. Sorry
I was not clear on this before.

> I've been confused for the better part of today too, since previously,

Peter

> said:
>

> > It is failing on the Open method with 100% certainty.

>

I know it is alot of details here, but I wrote on several occasions that the
script works flawlessly for some time, then suddenly it
will go into a tailspin. When it does incur such error, it occurs with 10%
certainty each time you request the ASP page and happens
for some unspecified time.

Eventually the problem goes away (for the time being) and the web page
operates correctly.

The reason for this error is still a mystery. My contention is that it's an
environmental issue with ASP or ADO or both.
Again, I'm using the same data provider and database with ADO.NET/ASP.NET
and I never experience this problem.

> > I'm not sure how to interpret this statement. Are you saying the error
> > occurs sometimes on con.Open and other times on rs.Open statements? Or

are

> > you saying you aren't sure which statement is generating the error? If

so,

> > you need to be sure.

>

--
Peter O'Reilly

[Peter O'Reilly]

Thanks for looking at my code. I agree the client side cursor is not
necessary and an oversight on my part.
That said, I don't think it is material to the problem I am experiencing -
with, my guess is stability issues.

I'm almost at the point of rewriting the page in ASP.NET. Finding the time
to do such is another matter.
--
Peter O'Reilly

[Rob Collyer]

"Peter O'Reilly" <Peter_OReilly@timeinc.com!N!O!.S!P!AM!> wrote in
news:ueN77VabEHA.3508@TK2MSFTNGP09.phx.gbl:

> Thanks for looking at my code. I agree the client side cursor is not
> necessary and an oversight on my part.
> That said, I don't think it is material to the problem I am
> experiencing - with, my guess is stability issues.
>
> I'm almost at the point of rewriting the page in ASP.NET. Finding the
> time to do such is another matter.

Just o say, thay an error on the 'open' method 90% of the time means an
error in the SQL statement.... just for your future reference!

--
Robert Collyer
[url]www.webforumz.com[/url]
Free Web Design and Development Help, Discussions, tips and Critique!
ASP, VB, .NET, SQL, CSS, HTML, Javascript, Flash, XML, SEO !

[Peter O'Reilly]

> Just o say, thay an error on the 'open' method 90% of the time means an

> error in the SQL statement.... just for your future reference!

I trap for bad SQL statements and it is reported as such. The same SQL
statement will work fine on the first request,
then subsequent requests I will receive the error originally stated.
--
Peter O'Reilly