Problem with database connection not releasing the database

I have a project that works a lot like SQL Enterprise Manager and allows for making modifications to the database for stuff like Update, Select, Delete, etc...  I am adding a function to delete the database, but everytime I try to delete it it says that the database is in use.  But, I've only got 1 SqlConnection object and before I try to delete it I am executing these 3 lines:

dbConn.Close
dbConn.Dispose
dbConn = Nothing

This is the 1 and only db connection I have.  Also, this is running on my local pc, so there is no way that another person might be using the database.  If I avoid opening the database at all, it works fine so I know it has something to do with the fact that I'm opening dbConn.  I have also tried adding Pooling=False to my connection strings, but this has had no effect.  Anyone have any ideas?

Here is the connection string I'm using:
sProvider = "data source=" & sServerName & ";integrated security=SSPI"

Here is the code where I first open the database (if I REM this code out, I have no problems):

<CODE SNIPPET>
                '**** At this point, sDatabaseName = Subscriber
      Dim sSQL As String = vbNullString
      Dim iTotRecords As Integer
      Dim dbConn As New SqlConnection
                Dim dsData As New DataSet
                Dim daDataAdapt As New SqlDataAdapter
      sSql = "Select name,xtype From sysobjects Where UPPER(LTrim(RTrim(xtype)))='U' order by Name"
      dbConn.ConnectionString = sProvider & ";database=" & sDatabaseName
      dbConn.Open()
      daDataAdapt.SelectCommand = New SqlCommand(sSql, dbConn)
      daDataAdapt.Fill(dsData, "sysobjects")
      iTotRecords = dsData.Tables("sysobjects").Rows.Count
      If dsData.Tables("sysobjects").Rows.Count <> 0 Then
            ListBox1.Items.Clear()
            For L = 0 To iTotRecords - 1
                  ListBox1.Items.Add(UCase(dsData.Tables("sysobjects").Rows(L)("name").ToString()))
            Next
      End If
      dsData.Clear()
      dsData.Dispose()
      dsData = Nothing
                daDataAdapt.SelectCommand = Nothing
      daDataAdapt.Dispose()
      daDataAdapt = Nothing
                dbConn.Dispose()
      dbConn.Close()
      dbConn = Nothing
</CODE SNIPPET>

Here's where it's giving me the problem:

<CODE SNIPPET>
                '**** At this point, sDataBaseToDelete = Subscriber
      Dim sSQL As String = vbNullString
      Dim TempConnection As New SqlConnection
                Dim command As SqlCommand
      TempConnection.ConnectionString = sProvider & ";Database=master"
      TempConnection.Open()
      sSql = "DROP DATABASE [" & sDataBaseToDelete & "]"
      command = New SqlCommand(sSql, TempConnection)
      command.ExecuteNonQuery()
</CODE SNIPPET>

When it tries to execute the command.ExecuteNonQuery() I get an error saying that the database is in use.  But if I rem out the first set of code, it works just fine.  Anyone have any ideas?
LVL 5
codeconquerorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff CertainCommented:
Have you tried GC.Collect to force garbage collection?
codeconquerorAuthor Commented:
I just tried it, no luck.

<CODE SNIPPET>
     '**** At this point, sDataBaseToDelete = Subscriber
     Dim sSQL As String = vbNullString
     Dim TempConnection As New SqlConnection
     Dim command As SqlCommand
     GC.Collect()
     TempConnection.ConnectionString = sProvider & ";Database=master"
     TempConnection.Open()
     sSql = "DROP DATABASE [" & sDataBaseToDelete & "]"
     command = New SqlCommand(sSql, TempConnection)
     command.ExecuteNonQuery()
</CODE SNIPPET>
codeconquerorAuthor Commented:
Any other ideas?

Anyone?

Thanks all.  :)
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

amyhxuCommented:
You have to kill all user processes that are currently connected to that database. Even if your application seems to have closed everything, the processes it started are still in sleeping mode for a while. The information is stored in master database sysprocesses talbe. The following code is working in my test project (adding code to your second code snippet):

     Dim sDataBaseToDelete As String = "database_name"
     Dim sSQL As String = vbNullString
     Dim TempConnection As New SqlConnection
     Dim command As SqlCommand
     TempConnection.ConnectionString = sProvider & ";Database=master"

        Dim cmdSPID As New SqlCommand
        With cmdSPID
            .Connection = TempConnection
            .CommandText = "select spid from sysprocesses where db_name(dbid) = '" & sDataBaseToDelete & "'"
        End With

        Dim al As New ArrayList

        TempConnection.Open()
        Dim dr As SqlDataReader = cmdSPID.ExecuteReader()
        While dr.Read
            al.Add(dr("spid"))
        End While
        dr.Close()

        For i As Integer = 0 To al.Count - 1
            Dim cmdKillSP As New SqlCommand
            With cmdKillSP
                .Connection = TempConnection
                .CommandText = "Kill " & al(i)
            End With
            cmdKillSP.ExecuteNonQuery()
        Next
        TempConnection.Close()

     TempConnection.Open()
     sSql = "DROP DATABASE [" & sDataBaseToDelete & "]"
     command = New SqlCommand(sSql, TempConnection)
     command.ExecuteNonQuery()
     TempConnection.Close()


Also, in your first code snippet, it's better to write the code in this order:
      dbConn.Close()
      dbConn.Dispose()
      dbConn = Nothing

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
codeconquerorAuthor Commented:
Thanks amyhxu.  It might take me until Monday morning to test that out, but I appreciate the code.  I'll let you know how it goes.

If anyone else has any ideas in the meantime, by all means speak up, this way if 1 method fails I'll have a backup plan.  :)  Thanks all.
codeconquerorAuthor Commented:
Thanks amyhxu.  Works like a champ.  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.