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.Connection.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.InvalidOperationException:
The connection is already Open (state=Connecting).

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.

[Kevin Spencer]

Hi Nick,

I understand that you may think it is better to use a single Connection, but
you're really trying to make an end-run around the .Net database Connection
model. The .Net platform is designed to make efficient use of database
Connections, and to reuse them all by itself. it does this by the process of
Connection Pooling.

You may be confusing the Connection Class and a database Connection. In
fact, they are not the same. A Connection class is a class that provides a
programming interface for working with database Connections. A database
Connection is a software entity that contains data about the daabase
Connection (that is, the client app and the database), and process for
communicating between the client app and the database. This is an important
distinction to make. Why? Because in the .Net platform, closing a Connection
doesn't actuall kill a database Connection. It disconnects the Connection
class from the database Connection itself, which is held in a Connection
Pool for re-use by any Connection class that has the same Connection String.

In other words, by persisting your Connection (class), you're not
accomplishing anything that the .Net platform doesn't do all by itself
already, which is to persist actual database Connections. And, in fact, you
are, as I said earlier, making an expensive end-run around the .Net database
Connection model, which is more likely to cause problems than it is to solve
any (as you have seen).

Therefore, I would recommend that you take Microsoft's word for how the .Net
platform works, and use it as prescribed by Microsoft. That is, create your
Connection classes when and where you need them, and close and/or dispose
them as quickly as possible, in order to avoid the sorts of problems you're
experiencing right now.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.

"Nick Gilbert" <Ni***@newsgroup.nospam> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

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.Connection.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.InvalidOperationException:
The connection is already Open (state=Connecting).

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.

[Nick Gilbert]

> In other words, by persisting your Connection (class), you're not

accomplishing anything that the .Net platform doesn't do all by itself
already, which is to persist actual database Connections.

If this is the case, why do I get a 5000% performance improvement if I
cache my connection class? Why also does calling .Open take about 30ms
to execute each time you call it if it's not really having to open the
database connection again?

If you don't belive me - try it yourself. Calling Connection.Open about
2000 times on SQL Server takes almost no time. On Access it takes
several seconds. Perhaps you're assuming .NET handles Access connections
in the same way it handles SQL Server connections?

Calling .Open every time I call a query makes some of my pages unusably
slow - whereas currently they render in a fraction of a second. I could
either rewrite the page so it uses fewer queries (not very feasable for
my project) or try and fix this bug another way.

Nick...

[Patrice]

Not using Access myself but according to
http://msdn.microsoft.com/library/de...taprovider.asp
and other sources, my understanding is that pooling should work as this is
handled by the provider.

What is the format of the Jet DB ? (Access 97, 2000, 2003 level ?) Do you
see the same slowness on a read/write device ?

I'll likely give this a try. Just curious to know if pooling works or not
with Jet... For example an interesting test would be to try to disable
pooling. If it's the same this is that pooling doesn't work. If it's slower,
this is because the original slow result comes from something else than
pooling (permissions checking or whatever else)...

--
Patrice

"Nick Gilbert" <Ni***@newsgroup.nospam> a écrit dans le message de
news:eq**************@TK2MSFTNGP09.phx.gbl...

In other words, by persisting your Connection (class), you're not
accomplishing anything that the .Net platform doesn't do all by itself
already, which is to persist actual database Connections.

If this is the case, why do I get a 5000% performance improvement if I
cache my connection class? Why also does calling .Open take about 30ms
to execute each time you call it if it's not really having to open the
database connection again?

If you don't belive me - try it yourself. Calling Connection.Open about
2000 times on SQL Server takes almost no time. On Access it takes
several seconds. Perhaps you're assuming .NET handles Access connections
in the same way it handles SQL Server connections?

Calling .Open every time I call a query makes some of my pages unusably
slow - whereas currently they render in a fraction of a second. I could
either rewrite the page so it uses fewer queries (not very feasable for
my project) or try and fix this bug another way.

Nick...

[Patrice]

Ok it looks actually that the problem is that pooling is not enabled by
default for Jet (or perhaps depending on the OS or whatever ???)

What I saw here :
- with a basic connection string, creating/closing 100 connections took
around 4 s.
- surprisingly it made no difference when disabling pooling.
- I tested then to see what happens when explictely enabling pooling and it
gave a much better 0,1 s result

For now, it looks like to me that pooling is actually disabled by default
(at least on a workstation OS ???).

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

--
Patrice
"Patrice" <no****@nowhere.com> a écrit dans le message de
news:Oa**************@TK2MSFTNGP10.phx.gbl...

Not using Access myself but according to
http://msdn.microsoft.com/library/de...taprovider.asp and other sources, my understanding is that pooling should work as this is
handled by the provider.

What is the format of the Jet DB ? (Access 97, 2000, 2003 level ?) Do you
see the same slowness on a read/write device ?

I'll likely give this a try. Just curious to know if pooling works or not
with Jet... For example an interesting test would be to try to disable
pooling. If it's the same this is that pooling doesn't work. If it's slower, this is because the original slow result comes from something else than
pooling (permissions checking or whatever else)...

--
Patrice

"Nick Gilbert" <Ni***@newsgroup.nospam> a écrit dans le message de
news:eq**************@TK2MSFTNGP09.phx.gbl...

In other words, by persisting your Connection (class), you're not
accomplishing anything that the .Net platform doesn't do all by itself
already, which is to persist actual database Connections.

If this is the case, why do I get a 5000% performance improvement if I
cache my connection class? Why also does calling .Open take about 30ms
to execute each time you call it if it's not really having to open the
database connection again?

If you don't belive me - try it yourself. Calling Connection.Open about
2000 times on SQL Server takes almost no time. On Access it takes
several seconds. Perhaps you're assuming .NET handles Access connections
in the same way it handles SQL Server connections?

Calling .Open every time I call a query makes some of my pages unusably
slow - whereas currently they render in a fraction of a second. I could
either rewrite the page so it uses fewer queries (not very feasable for
my project) or try and fix this bug another way.

Nick...

[Nick Gilbert]

Patrice wrote:

Ok it looks actually that the problem is that pooling is not enabled by
default for Jet (or perhaps depending on the OS or whatever ???)

What I saw here :
- with a basic connection string, creating/closing 100 connections took
around 4 s.
- surprisingly it made no difference when disabling pooling.
- I tested then to see what happens when explictely enabling pooling and it
gave a much better 0,1 s result

For now, it looks like to me that pooling is actually disabled by default
(at least on a workstation OS ???).

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.

Nick...

[Patrice]

My first though would be it depends on the OS (connection pooling would make
more sense on an server OS rather than a workstation OS).

From
http://msdn.microsoft.com/library/de...l/pooling2.asp
and though this is not directly related (ODBC) :
Enabling Connection Pooling
For Internet Information Services (IIS) version 3.0, pooling is set to off
by default, so you need to manually turn on connection pooling. For IIS
version 4.0 and later, pooling has been set to on by default. (IIS 3.0 uses
ODBC 3.0, and IIS 4.0 uses ODBC 3.5.) If you are coding by using the ActiveX
Data Objects (ADO) object model to the OLE DB Provider for ODBC (MSDASQL),
connection pooling will be turned on for you. If you are not using ADO and
are coding directly to the ODBC API, you will need to turn on pooling
yourself.

Though not directly related, it would me make think from a general point of
view that it is better to explicitely ask for the behavior you depend on
rather than to rely on some kind of default (that could vary in time)...

--

Patrice
"Nick Gilbert" <Ni***@newsgroup.nospam> a écrit dans le message de
news:Os****************@TK2MSFTNGP14.phx.gbl...

Patrice wrote:

Ok it looks actually that the problem is that pooling is not enabled by
default for Jet (or perhaps depending on the OS or whatever ???)

What I saw here :
- with a basic connection string, creating/closing 100 connections took
around 4 s.
- surprisingly it made no difference when disabling pooling.
- I tested then to see what happens when explictely enabling pooling and it gave a much better 0,1 s result

For now, it looks like to me that pooling is actually disabled by default (at least on a workstation OS ???).

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.

Nick...

[Adrian Moore]

Patrice,

For anyone around here who survived the Window NT days, you would have
experienced the joy of debugging connection pooling problems (which of
course, was enabled by default, back then). On multiple CPU's, under
moderate stress, it worked horribly. Thanks to our brave efforts,
Microsoft, in their wisdom, now disables it by default and it is a far more
stable and predictable sub-system. MDAC 2.61 on Windows 2000 was the first
release where it actually worked predictably and correctly.

That's a battle I won't soon forget ... as I write this from my padded room
;-)

Ad.

"Patrice" <no****@nowhere.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

My first though would be it depends on the OS (connection pooling would
make
more sense on an server OS rather than a workstation OS).

From
http://msdn.microsoft.com/library/de...l/pooling2.asp
and though this is not directly related (ODBC) :
Enabling Connection Pooling
For Internet Information Services (IIS) version 3.0, pooling is set to off
by default, so you need to manually turn on connection pooling. For IIS
version 4.0 and later, pooling has been set to on by default. (IIS 3.0
uses
ODBC 3.0, and IIS 4.0 uses ODBC 3.5.) If you are coding by using the
ActiveX
Data Objects (ADO) object model to the OLE DB Provider for ODBC (MSDASQL),
connection pooling will be turned on for you. If you are not using ADO and
are coding directly to the ODBC API, you will need to turn on pooling
yourself.

Though not directly related, it would me make think from a general point
of
view that it is better to explicitely ask for the behavior you depend on
rather than to rely on some kind of default (that could vary in time)...

--

Patrice
"Nick Gilbert" <Ni***@newsgroup.nospam> a écrit dans le message de
news:Os****************@TK2MSFTNGP14.phx.gbl...

Patrice wrote:

> Ok it looks actually that the problem is that pooling is not enabled by
> default for Jet (or perhaps depending on the OS or whatever ???)
>
> What I saw here :
> - with a basic connection string, creating/closing 100 connections took
> around 4 s.
> - surprisingly it made no difference when disabling pooling.
> - I tested then to see what happens when explictely enabling pooling
> and it > gave a much better 0,1 s result
>
> For now, it looks like to me that pooling is actually disabled by default > (at least on a workstation OS ???).
>
> 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.

Nick...

[Patrice]

I promise. I won't ask Microsoft to enable this by default dismissing your
past efforts ;-)

Actually they done the same thing with Windows Server 2003 (disabling all by
default). Got the big picture now ;-)

Patrice

--

"Adrian Moore" <qu***********@hotmail.com> a écrit dans le message de
news:%2****************@TK2MSFTNGP10.phx.gbl...

Patrice,

For anyone around here who survived the Window NT days, you would have
experienced the joy of debugging connection pooling problems (which of
course, was enabled by default, back then). On multiple CPU's, under
moderate stress, it worked horribly. Thanks to our brave efforts,
Microsoft, in their wisdom, now disables it by default and it is a far more stable and predictable sub-system. MDAC 2.61 on Windows 2000 was the first release where it actually worked predictably and correctly.

That's a battle I won't soon forget ... as I write this from my padded room ;-)

Ad.

"Patrice" <no****@nowhere.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

My first though would be it depends on the OS (connection pooling would
make
more sense on an server OS rather than a workstation OS).

From
http://msdn.microsoft.com/library/de...l/pooling2.asp and though this is not directly related (ODBC) :
Enabling Connection Pooling
For Internet Information Services (IIS) version 3.0, pooling is set to off by default, so you need to manually turn on connection pooling. For IIS
version 4.0 and later, pooling has been set to on by default. (IIS 3.0
uses
ODBC 3.0, and IIS 4.0 uses ODBC 3.5.) If you are coding by using the
ActiveX
Data Objects (ADO) object model to the OLE DB Provider for ODBC (MSDASQL), connection pooling will be turned on for you. If you are not using ADO and are coding directly to the ODBC API, you will need to turn on pooling
yourself.

Though not directly related, it would me make think from a general point
of
view that it is better to explicitely ask for the behavior you depend on
rather than to rely on some kind of default (that could vary in time)...

--

Patrice
"Nick Gilbert" <Ni***@newsgroup.nospam> a écrit dans le message de
news:Os****************@TK2MSFTNGP14.phx.gbl...

Patrice wrote:
> Ok it looks actually that the problem is that pooling is not enabled by > default for Jet (or perhaps depending on the OS or whatever ???)
>
> What I saw here :
> - with a basic connection string, creating/closing 100 connections took > around 4 s.
> - surprisingly it made no difference when disabling pooling.
> - I tested then to see what happens when explictely enabling pooling
> and

it

> gave a much better 0,1 s result
>
> For now, it looks like to me that pooling is actually disabled by

default

> (at least on a workstation OS ???).
>
> 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.

Nick...

[Nick Gilbert]

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

[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***@newsgroup.nospam> wrote in message
news:%2****************@tk2msftngp13.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***@newsgroup.nospam> wrote in message
news:Or****************@TK2MSFTNGP09.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...