Not able to drop database after opening datatable

[Utpal]

Hi everybody,

I am using VB Webforms as my front end and MSDE 2000 as my back end. I am not able to drop a database after executing the following vb code on that database:

Dim vConnection As New SqlConnection(vConnectionString)
        Dim vAdapter As New SqlDataAdapter("SELECT party_type_code, party_type_name, party_type_imported" & _
                                            " FROM Party_Type" & _
                                            " ORDER BY party_type_name", vConnection)
        Dim vDataTable As New DataTable()
        vAdapter.Fill(vDataTable)
        vAdapter.Dispose()
        vConnection.Dispose()

On trying to drop the database using the SQL Command "DROP DATABASE PPCABCD2005", I get the error message:
Cannot drop the database 'PPCABCD2005' because it is currently in use ...
However, if I don't execute the above code, I am able to drop it. Why is it so? What is the solution to this problem?

[Wokawidget]

try:

vConnection.Close
vConnection.Dispose()
vConnection = Nothing

Think it's the .Close bit that you need.

Woof

[Utpal]

Thanks Wokawidget. I tried the code you suggested, but that did not work. Still I am not able to drop the database.

[Wokawidget]

Hmmm...I know this will make your app not work...but just try:

vDataTable.Dispose
vDataTable = Nothing

add that after you toast the connection object in the above code.

Woka

[Utpal]

Hmmm...I know this will make your app not work...but just try:

visual basic code:--------------------------------------------------------------------------------
vDataTable.Dispose
vDataTable = Nothing
--------------------------------------------------------------------------------
add that after you toast the connection object in the above code.

I tried this too. Still I am getting the same error message.

[dgsatish]

Try this


vConnection.Close
vConnection.Dispose()
vConnection = Nothing
Sleep 2000

[nemaroller]

.Net uses connection pooling... its quite possible the connection is still alive, even if you dispose a reference to it within your assembly.

Open Enterprise Manager and look for the Management folder...

Open the Current Activity.. Process Info.



Look for your database... and see what connections may be active (by looking in the Status column).

[Utpal]

Thanks