ConnectionState problem - managing connections to an Access database

[Nick Gilbert]

Hi,

I have an asp.net application which runs from a CD-ROM using Cassini. As
such, it is single user only.

The application connects to an Access database when it is loaded, and
keeps the same connection open all the time (as it's single user, this
shouldn't be a problem).

There is logic in the code to ensure that the connection is
automatically opened the first time it is used and the connection is
stored in an Application variable:

if ((cmd.Connectio n.State == ConnectionState .Closed) ||
(cmd.Connection .State == ConnectionState .Broken)) cmd.Connection. Open();

However occasionally while testing my application under IIS, it comes up
with this error message:

System.InvalidO perationExcepti on:
The connection is already Open (state=Connecti ng).

Or this:

ExecuteReader requires an open and available Connection. The
connection's current state is Open, Executing.

But if the state is Connecting, you can neither call .Open nor can you
execute a query. So how can I maintain an open connection without these
errors occuring?

I don't understand why it is ever in the "Exectuting "
state in a single user application. Is it because IIS is multithreaded
and perhaps it's using two+ threads to host my application?

If so, how can I stop these errors from happening? I don't want to open
and close a new connection each time I do something because this is too
slow (40ms) and some pages require 100 queries for reasons I don't have
time to explain here. Using a single connection - the application
performs very well but has these errors. Is there a way I can get the
best of both worlds?

Someone suggested that I disable connection pooling but this made no
difference.

Thanks,

Nick...
--
Please reply to the newsgroup or I won't see your message.

[William \(Bill\) Vaughn]

It's not connection pooling. It's JET. JET does not write to the disk file
until it's bored (idle). As long as there is activity (adding more rows,
selecting, changing), it does not flush its local cache to the disk. This
helps query performance. If you open a Transaction and perform the changes
it forces JET to write immediately, but then your interactive performance
goes into the gutter.

hth

--
_______________ _______________ ______
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
_______________ _______________ ____

"Nick Gilbert" <Ni***@newsgrou p.nospam> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

You could then try to see what it gives if you don't cache the
connection
and enable pooling (by adding "OLE DB Services=-1" in the connection
string).

Let us know what you find...

You're right.

The reason it was so slow was that connection pooling seems to be
disabled by default. Adding "OLE DB Services=-1" to my connection string
has speeded up the appliction by an order of magnitude (no wonder
disabling it didn't make any difference!). So I've removed all my
connection caching code and now the bug has gone as as well! Thanks
Patrice! An average page in my application now loads in 0.10s instead of
0.90s and my slow loading 4 second pages load in less than 0.5s. I can't
complain about a 10 fold performance improvement just by adding
something to a connection string.

The only thing that worries me is *WHY* is it disabled by default if it
provides so much performance improvement? It seems to be working for me,
but what if it's disabled by default because there are issues with it?

Oh well - I'll just have to see if any arise.

Follow-up: Connection pooling does not seem to work reliably in Access
and I have had to turn it off.

For example, I had some code that updated a value in a database and then
immediately requested a dataset which contained that value. Sometimes I
noticed that the new dataset still contained an OLD value, even though
the database shows the correct value. Ie Jet was sending back an out of
date value for the data.

Similarly when updating, I occasionally get the following exception:

"The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time. ".

This is in a single threaded application with only one user.

I'm coming to the conclusion that connection pooling in access doesn't
work or is horribly bugged. The odd thing is, I can only reproduce this
error easily on one machine. On my machine it seems fine - but it may
just be because it's a faster machine or perhaps my machine has newer
drivers that have fixed this problem. How can I find out which version
of the OleDb drivers each machine is using?

The big problem I now have, is that without connection pooling, my
application is really slow! GRRR.

Thanks,

Nick...

[Nick Gilbert]

William (Bill) Vaughn wrote:

It's not connection pooling. It's JET. JET does not write to the disk file
until it's bored (idle). As long as there is activity (adding more rows,
selecting, changing), it does not flush its local cache to the disk. This
helps query performance. If you open a Transaction and perform the changes
it forces JET to write immediately, but then your interactive performance
goes into the gutter.

How easy is it to do a transaction from .NET? Or is it something you
have to do inside Access using a module?

Are there any other workarounds to this problem? Eg somehow disabling
the write cache or forcing it to write immediately for certain queries?

It's odd that the bug seems to go away when I disable connection
pooling, but perhaps the delay of opening a new connection is enough to
prevent the error from occuring (or maybe it always flushes the cache
when closing a connection).

Thanks,

Nick...

[Nick Gilbert]

William (Bill) Vaughn wrote:

It's not connection pooling. It's JET. JET does not write to the disk file
until it's bored (idle). As long as there is activity (adding more rows,
selecting, changing), it does not flush its local cache to the disk. This
helps query performance. If you open a Transaction and perform the changes
it forces JET to write immediately, but then your interactive performance
goes into the gutter.

I've tried wrapping the code in a transaction but the problem still
occurs. It seems that transactions don't affect this problem at all.

It just seems that occasionally access (or perhaps IIS) gives me back
data that is now obsolete.

Nick..

[Nick Gilbert]

Nick Gilbert wrote:

William (Bill) Vaughn wrote:

It's not connection pooling. It's JET. JET does not write to the disk file
until it's bored (idle). As long as there is activity (adding more rows,
selecting, changing), it does not flush its local cache to the disk. This
helps query performance. If you open a Transaction and perform the changes
it forces JET to write immediately, but then your interactive performance
goes into the gutter.

I've tried wrapping the code in a transaction but the problem still
occurs. It seems that transactions don't affect this problem at all.

It just seems that occasionally access (or perhaps IIS) gives me back
data that is now obsolete.

I've done some more testing and I've narrowed down the problem a bit
more. It seems that if you immediately read back the data on the same
connection, it appears to be correct. But if, after updating the data,
you redirect to a webpage which should contain the updated data, then it
somehow can get OLD data. This happens even when the code is wrapped in
a transaction as recommended by William above.

I presume what's happening is that if you redirect to a different page
after updating the database, you don't necessarily get the same thread
in IIS or the same connection from the connection pool, and somehow it's
managing to retrieve the old value from the database or perhaps some
caching is happening somewhere at IIS level.

I've proved that IIS isn't serving me an old *page* but IS serving me
old *data* by adding a sequential counter to the page (if it was serving
me an old page the counter would go backwards). So the problem is that
Access or it's drivers is returning the old values for some of the rows.

I really need to fix this problem ASAP. Any help would be greatly
appreciated.

Nick...

[William \(Bill\) Vaughn]

Well, I missed the fact that you're trying to use JET in an ASP environment.
While this is possible, as you have discovered there are a litany of issues
that pop up. JET was never designed to be used as a database engine to
support a web site. It's designed to handle a single "human" user and
perhaps share a database file over the intranet. When you read-back from a
JET database, it tries to use its local cache. This might be why you read a
record just written but others cannot. I think you're beating a dead horse.

--
_______________ _______________ ______
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
_______________ _______________ ____

"Nick Gilbert" <Ni***@newsgrou p.nospam> wrote in message
news:Or******** ********@TK2MSF TNGP09.phx.gbl. ..

William (Bill) Vaughn wrote:

It's not connection pooling. It's JET. JET does not write to the disk
file
until it's bored (idle). As long as there is activity (adding more rows,
selecting, changing), it does not flush its local cache to the disk. This
helps query performance. If you open a Transaction and perform the
changes
it forces JET to write immediately, but then your interactive performance
goes into the gutter.

How easy is it to do a transaction from .NET? Or is it something you
have to do inside Access using a module?

Are there any other workarounds to this problem? Eg somehow disabling
the write cache or forcing it to write immediately for certain queries?

It's odd that the bug seems to go away when I disable connection
pooling, but perhaps the delay of opening a new connection is enough to
prevent the error from occuring (or maybe it always flushes the cache
when closing a connection).

Thanks,

Nick...