Hello,

When I have closed my appliciation in VB.NET, and I try to detach my 
database from SQL SERVER, I often get a message that the database cannot be 
detachted, because it is still in use.

So I think there is some code missing in my application (in the 
MDI_FormClosing?) 
to make my database is no longer in use.

I already put a Connection.Close in the FormClosing also in a Finally-block 
of 

that FormClosing, but that does not seem to be enough.

Can someone help me with that?

Many thanks and greetings,

Michel

Remember, if you open a connection to the database, the connection remains 
open even AFTER you close the connection--until the Connection pool is 
flushed. This can be done in code in ADO.NET 2.0. If you're using the SSMS 
tools, be sure to check the "close connections" option first.

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Michel"  wrote in message 
news:15A2F24A-6D01-4C0A-80BE-133BEFDF5193@microsoft.com...

> Hello,
>
> When I have closed my appliciation in VB.NET, and I try to detach my
> database from SQL SERVER, I often get a message that the database cannot 
> be
> detachted, because it is still in use.
>
> So I think there is some code missing in my application (in the
> MDI_FormClosing?)
> to make my database is no longer in use.
>
> I already put a Connection.Close in the FormClosing also in a 
> Finally-block
> of
>
> that FormClosing, but that does not seem to be enough.
>
> Can someone help me with that?
>
> Many thanks and greetings,
>
> Michel
> 

Hello,

"William Vaughn" wrote:


> Remember, if you open a connection to the database, the connection remains 
> open even AFTER you close the connection--until the Connection pool is 
> flushed. This can be done in code in ADO.NET 2.0. If you're using the SSMS 
> tools, be sure to check the "close connections" option first.


I am new to all this, so can you tell me how to flush the connection pool? 
What code should I use to do this?
Can you also enlighten me on SSMS tools.  What are they for?

The only thing I want for now is to be able to detach my database, once my 
program ends.

Many thanks for your help.

Michel

You have kind of a catch 22. In order to send commands like sp_detach to a 
database server instance, you have to have a connection. If you flush the 
pool to free the connection, you can't detach the database if the connection 
references it. The trick is to open a connection to another  database 
(usually master). This assumes that you have rights to access the master 
database. Another approach is to change the current (default) database by 
using the USE <db> statement.

SqlConnection.ClearAllPools()     ' Clears all connection pools (and closes 
all connections)

SqlConnection.ClearPool(cn) ' Clear the pool for the selected connection

SQL Server Management Studio (SSMS) is the interactive management suite of 
tools that can be used to perform any and all kinds of maintenance on the 
database.

SMO (System Maintenance Objects) is the object interface used by SSMS. It 
can also detach databases programmatically. You might have better luck with 
this as its designed to perform these tasks where ADO.NET is not.

You might also benefit from my book as it discusses many of these issues.

hth-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------


"Michel"  wrote in message 
news:9358F28F-014E-406D-8B50-54FC8500AD9F@microsoft.com...

> Hello,
>
> "William Vaughn" wrote:
>
>> Remember, if you open a connection to the database, the connection 
>> remains
>> open even AFTER you close the connection--until the Connection pool is
>> flushed. This can be done in code in ADO.NET 2.0. If you're using the 
>> SSMS
>> tools, be sure to check the "close connections" option first.
>
> I am new to all this, so can you tell me how to flush the connection pool?
> What code should I use to do this?
> Can you also enlighten me on SSMS tools.  What are they for?
>
> The only thing I want for now is to be able to detach my database, once my
> program ends.
>
> Many thanks for your help.
>
> Michel
> 

Also in 2.0 you have a new connection string option that allows to attach 
the DB and to have it detached automatically :
http://msdn.microsoft.com/vstudio/tour/vs2005_guided_tour/VS2005pro/Smart_Client/AttachDatabase.htm

--
Patrice


"William Vaughn"  a crit dans le message de news: 
DA52D16C-6BC2-4745-83E7-0E1FAE72FDCD@microsoft.com...

> You have kind of a catch 22. In order to send commands like sp_detach to a 
> database server instance, you have to have a connection. If you flush the 
> pool to free the connection, you can't detach the database if the 
> connection references it. The trick is to open a connection to another 
> database (usually master). This assumes that you have rights to access the 
> master database. Another approach is to change the current (default) 
> database by using the USE <db> statement.
>
> SqlConnection.ClearAllPools()     ' Clears all connection pools (and 
> closes all connections)
>
> SqlConnection.ClearPool(cn) ' Clear the pool for the selected connection
>
> SQL Server Management Studio (SSMS) is the interactive management suite of 
> tools that can be used to perform any and all kinds of maintenance on the 
> database.
>
> SMO (System Maintenance Objects) is the object interface used by SSMS. It 
> can also detach databases programmatically. You might have better luck 
> with this as its designed to perform these tasks where ADO.NET is not.
>
> You might also benefit from my book as it discusses many of these issues.
>
> hth-- 
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no 
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> -----------------------------------------------------------------------------------------------------------------------
>
>
> "Michel"  wrote in message 
> news:9358F28F-014E-406D-8B50-54FC8500AD9F@microsoft.com...
>> Hello,
>>
>> "William Vaughn" wrote:
>>
>>> Remember, if you open a connection to the database, the connection 
>>> remains
>>> open even AFTER you close the connection--until the Connection pool is
>>> flushed. This can be done in code in ADO.NET 2.0. If you're using the 
>>> SSMS
>>> tools, be sure to check the "close connections" option first.
>>
>> I am new to all this, so can you tell me how to flush the connection 
>> pool?
>> What code should I use to do this?
>> Can you also enlighten me on SSMS tools.  What are they for?
>>
>> The only thing I want for now is to be able to detach my database, once 
>> my
>> program ends.
>>
>> Many thanks for your help.
>>
>> Michel
>>
>