Greetings,

I am presently working a Web development contract where I am locked into
using an Access 2002 database as the backend datastore. I understand that
this is not optimal, but the web app will be accessed by a minimal number
of users as it is an internal system to the organization and they do not
wish to spend the money on an SQL Server or SQL Server hosting at this
time. Also, I do not have administrative priveleges on the Web server so
using the desktop version of SQL Server is also out of the question.
Ultimately I will employ dataset caching on the web server as most of the
data will remain static for long periods of time, further reducing the
load on the database.

That said, it has been some time since I have worked with OLE DB and the
MS Jet database engine, and I have hit a snag that I'm sure someone in
this group will be able to answer quickly.

My current architecture is as such:

I have an ASP.NET application written in C#, utilizing strongly typed
datasets. The data access code lives in business objects in a project
separate from the Web project within the VS.NET solution.

The data access methods I have written create the OLE DB connection,
run a stored procedure (parameter query) populating the dataset, close
the connection and return the strongly typed dataset to the calling
location.

This works flawlessly. The only problem I experience is when two clients
attempt to access the database at exactly the same time. Then one or
both of the clients recieves the following message:

"The connection is already Open (State=Open)"

Obviously, one client has already opened the connection and the other
is attempting to do the same. Initially I thought that this might be
a problem with my data access structure since I had created an API-like
structure using static methods, however the probem persists even with
the use of instance methods and variables.

I am hoping that there is some sort of setting that I am missing in my
connection string and that I am not going to have to write any crazy
threading/event handling code to do something that would seem to be
very common. Currently my connection string is pretty basic providing
the Provider and Path to the datastore.

Any insight into this issue is greatly appreciated.
Thanks in advance,
Lance.

After scouring message boards, experimenting with various settings,
buying a book on ADO.NET (at a local book store for way too
much money I might add) and numerous frustrations I seem to have
stumbled upon a solution to my own problem.

From what I have read in the MSDN and documentaton regarding
the OLE DB data provider the default behavior starts all OLE
DB Services. This includes Automatic Transaction Enlistment,
the Client Cursor Engine AND *TaDaaa* Connection Pooling. As
I mentioned in my earlier post I was using a very basic
connection string and I assumed that this "default behavior"
would be started automatically for me. I believe this to be
the nature of things that are "default". Anyway regardless
of what is supposed to happen or the semantics of that
description, I found that the solution to my problem was to
explicitly add the the setting...

"OLE DB Services = -1;"

....to my connection string which I have stored in my Web.config
file. -1 is the value that corresponds to the default behavior
enabling all services. If you wish to override this behavior or
turn any of the other services on or off, there is a list of
the values at:

http://msdn.microsoft.com/library/de...hapter19_8.asp

I am still unsure why this was an issue. I witnessed the same
behavior on my development servers running Win2K and XP with
VS.NET 2003, Access 2002 and SQL Server 2000 installed.
The behavior was also witnessed in the production environment
which I have little control over, though I believe the hosting
provider told me it was a 2000 Advanced Server. I suppose it
is possible that on all three machines there is something in
the machine.config that is inhibiting this behavior thusly
requiring the value in Web.config to be explicitly set, though
it is probably more likely that I am misunderstanding the default
implementation of this data adapter. I have never seen anything
like this working in the SqlClient namespace, but as I mentioned
I am a bit rusty with the Jet database engine. If anyone has
any information regarding why connection pooling does not seem to
start implicitly with the creation of an OLE DB data connection,
I would appreciate any responses out of purely academic curiosity.

Thanks,
lm

lmcdonald (AT) healthshare (DOT) com (Lippy Farnstocking) wrote in message news:<a19342ce.0312010651.513a5058 (AT) posting (DOT) google.com>...