Database closing problem

3 May 2007 3:55 PM

John

Hi

I have a vb.net app that opens an access database runs several sql action
queries on the tables and closes the db. This is done every few minutes. The
problem is that sometimes I get an error 'ExecuteNonQuery requires an open
and available Connection. The connection's current state is closed'. My
question is; is there a situation where an app automatically closes the db
connection causing this error and is there a way to prevent this?

Thanks

Regards

3 May 2007 4:46 PM

rowe_newsgroups

On May 3, 11:55 am, "John" <J...@nospam.infovis.co.uk> wrote:
> Hi
>
> I have a vb.net app that opens an access database runs several sql action
> queries on the tables and closes the db. This is done every few minutes. The
> problem is that sometimes I get an error 'ExecuteNonQuery requires an open
> and available Connection. The connection's current state is closed'. My
> question is; is there a situation where an app automatically closes the db
> connection causing this error and is there a way to prevent this?
>
> Thanks
>
> Regards

I'm guessing you are trying to use a global connection object right?

If so, change the code to open a connection to the database, execute
your commands, and then close/dispose of the command and connection.
You should try to avoid using a "global" connection object at all
costs as it can lead to serious problems (with very few benefits). If
I guessed wrong, please post your code so I can take a look.

Thanks,

Seth Rowe

3 May 2007 5:53 PM

John

Hi Seth

It is true that I am opening a global connection. Executing only some of sql
queries will cause problem with the integrity of the data in the database.
By using a global connection I was hoping to provide a guaranteed connection
to all queries reducing any expected problems. Is there a way to ensure that
all queries get executed or none?

Thanks

Regards

Show quoteHide quote

"rowe_newsgroups" <rowe_em***@yahoo.com> wrote in message
news:1178210814.449498.96420@l77g2000hsb.googlegroups.com...
> On May 3, 11:55 am, "John" <J...@nospam.infovis.co.uk> wrote:
>> Hi
>>
>> I have a vb.net app that opens an access database runs several sql action
>> queries on the tables and closes the db. This is done every few minutes.
>> The
>> problem is that sometimes I get an error 'ExecuteNonQuery requires an
>> open
>> and available Connection. The connection's current state is closed'. My
>> question is; is there a situation where an app automatically closes the
>> db
>> connection causing this error and is there a way to prevent this?
>>
>> Thanks
>>
>> Regards
>
> I'm guessing you are trying to use a global connection object right?
>
> If so, change the code to open a connection to the database, execute
> your commands, and then close/dispose of the command and connection.
> You should try to avoid using a "global" connection object at all
> costs as it can lead to serious problems (with very few benefits). If
> I guessed wrong, please post your code so I can take a look.
>
> Thanks,
>
> Seth Rowe
>
>

4 May 2007 5:28 AM

Cor Ligthert [MVP]

> Is there a way to ensure that all queries get executed or none?
>

Follow the advice of Seth

Cor

Show quoteHide quote

"John" <John@nospam.infovis.co.uk> schreef in bericht
news:uaP11vajHHA.3120@TK2MSFTNGP05.phx.gbl...
> Hi Seth
>
> It is true that I am opening a global connection. Executing only some of
> sql queries will cause problem with the integrity of the data in the
> database. By using a global connection I was hoping to provide a
> guaranteed connection to all queries reducing any expected problems. Is
> there a way to ensure that all queries get executed or none?
>
> Thanks
>
> Regards
>
> "rowe_newsgroups" <rowe_em***@yahoo.com> wrote in message
> news:1178210814.449498.96420@l77g2000hsb.googlegroups.com...
>> On May 3, 11:55 am, "John" <J...@nospam.infovis.co.uk> wrote:
>>> Hi
>>>
>>> I have a vb.net app that opens an access database runs several sql
>>> action
>>> queries on the tables and closes the db. This is done every few minutes.
>>> The
>>> problem is that sometimes I get an error 'ExecuteNonQuery requires an
>>> open
>>> and available Connection. The connection's current state is closed'. My
>>> question is; is there a situation where an app automatically closes the
>>> db
>>> connection causing this error and is there a way to prevent this?
>>>
>>> Thanks
>>>
>>> Regards
>>
>> I'm guessing you are trying to use a global connection object right?
>>
>> If so, change the code to open a connection to the database, execute
>> your commands, and then close/dispose of the command and connection.
>> You should try to avoid using a "global" connection object at all
>> costs as it can lead to serious problems (with very few benefits). If
>> I guessed wrong, please post your code so I can take a look.
>>
>> Thanks,
>>
>> Seth Rowe
>>
>>
>
>

13 May 2007 6:12 AM

Mark

On Thu, 3 May 2007 18:53:27 +0100, "John" <John@nospam.infovis.co.uk>
wrote:

>Hi Seth
>
>It is true that I am opening a global connection. Executing only some of sql
>queries will cause problem with the integrity of the data in the database.
>By using a global connection I was hoping to provide a guaranteed connection
>to all queries reducing any expected problems. Is there a way to ensure that
>all queries get executed or none?

Use a proper database?

google: "ado.net transactions"