?
Solved

How do I use VB .Net to delete a table in SQL Server?

Posted on 2006-03-23
5
Medium Priority
?
299 Views
Last Modified: 2010-04-23
Hi Experts,

The code attached below is crashing at the point marked <=***

Where in the previous code have I gone wrong?

Many thanks in advance,

Seamie

    Public Function DropDatabase(ByVal connString As String, ByVal sDBName As String) As Boolean
        Dim cn As New SqlConnection(connString)
        Try
            cn.Open()
            'Dim cmdDrop As New SqlCommand("DROP DATABASE " & sDBName, cn) [my note: I've alternated between this line and the next by remarking the other one out]
            Dim cmdDrop As New SqlCommand("DROP DATABASE " & sDBName)
            cmdDrop.ExecuteNonQuery()                                                              <=***
            WriteToFile("Setup", "Database Deleted")
        Catch ex As Exception
            MsgBox("Encountered an Error in deleting the database: " & sDBName & vbCrLf & "It is important that you contact the SQL Server Administrator and tell him/her that this database needs to be deleted!", MsgBoxStyle.Critical)
            WriteToFile("Setup", "Database not deleted (" & sDBName & ")" & vbCrLf & "Connection String= " & connString)
        Finally
            cn.Close()
        End Try
    End Function
0
Comment
Question by:Seamie_
5 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16273068
Hi Seamie_,

What is the error message?

Is the user (used in your connection string) is allowed to drop database?

Your second declaration does not have any connection to run against.

Cheers!
0
 

Author Comment

by:Seamie_
ID: 16273419
Hi Emoreau

Error Message:=> ExecuteNonQuery: Connection Property has not been initialised

Thanks

Seamie
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 16273445
Change this line:

    Dim cmdDrop As New SqlCommand("DROP DATABASE " & sDBName)

To:

    Dim cmdDrop As New SqlCommand("DROP DATABASE " & sDBName, cn)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16273474
you must use the connection object along with the Command object, like this:

 cn.Open()
            'Dim cmdDrop As New SqlCommand("DROP DATABASE " & sDBName, cn) [my note: I've alternated between this line and the next by remarking the other one out]
            Dim cmdDrop As SqlCommand = New SqlCommand("DROP DATABASE " & sDBName, cn)
            cmdDrop.ExecuteNonQuery()                                                              <=***
            WriteToFile("Setup", "Database Deleted")
        Catch ex As Exception

when you say you have alternated between the two forms, what do you get with this syntax?

AW
0
 

Author Comment

by:Seamie_
ID: 16273559
Yup, it worked thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question