1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

HELP! I keep getting the same error message!

Discussion in 'C#' started by JEP_Dude, Sep 10, 2011.

  1. #1
    Hey Experts!

    I have this SQL Database that has about 2,700 tables. (The completed version could have about 3,500 tables.) Each of these tables changes VERY fast and MUST be kept up to date! That being said, I've been led to believe the fastest way to remove all tables was to "DROP DATABASE database-name" and to use "CREATE DATABASE database-name" followed by "CREATE TABLE table-name".

    This sounds good. Yet, I keep getting, "Cannot drop the database 'TEST', because it does not exist or you do not have permission." I've checked it and it does exist. So I guess that's not the problem.

    Could the problem be within the SQL connection string that I've named, "ConString"? Or could the problem stem from the way I ".Close()" and then ".Dispose()"?

    Any help you can give me that would solve this problem would be greatly appreciated! Thanks for listening to me vent my frustrations!

    May you have a blessed day as the LORD wills.

    JEP_Dude

    PS: The code is listed below.

    ---

    Const ConString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\TEST.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    Public G_ErrorCount as integer = 0

    Friend Sub Main()
    .
    .
    If SQL_DropDataBase("TEST") = True Then
    .
    .
    END IF
    .
    .
    END SUB


    Friend Function SQL_DropDataBase(ByVal L_DBName As String) As Boolean
    Dim L_SQLString As String = "DROP DATABASE " & L_DBName
    Dim L_Result As Boolean

    Using L_SQL_Connection As New SqlClient.SqlConnection(ConString)
    Dim L_SQLCommand As New SqlClient.SqlCommand(L_SQLString, L_SQL_Connection)

    L_SQLCommand.Connection.Open()
    Try
    L_Result = True

    L_SQLCommand.ExecuteNonQuery()
    Catch ex As Exception
    L_Result = False

    Beep()
    G_ErrorCount += 1
    MessageBox.Show(ex.Message)
    End Try

    L_SQL_Connection.Close()
    L_SQL_Connection.Dispose()
    End Using

    Return L_Result
    End Function
     
    JEP_Dude, Sep 10, 2011 IP
  2. geekstrack

    geekstrack Well-Known Member

    Messages:
    72
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #2
    check your connection string is same details which you use in your sql server?
     
    geekstrack, Feb 23, 2012 IP
  3. sbglobal79

    sbglobal79 Banned

    Messages:
    724
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    55
    #3
    so are you using vb.net with asp.net ?
     
    sbglobal79, Feb 26, 2012 IP
  4. geekstrack

    geekstrack Well-Known Member

    Messages:
    72
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #4
    geekstrack, Feb 26, 2012 IP
  5. agitetech

    agitetech Peon

    Messages:
    122
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    It seems like there is problem in your connection string .Remove it and again put the new string instead of it.
     
    agitetech, Dec 26, 2012 IP
  6. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #6
    Two coding errors:
    If SQL_DropDataBase("TEST") = True Then
    Code (markup):
    This is a pleonasm. Change it to
    If SQL_DropDataBase("TEST") Then
    Code (markup):
    SQL_DropDataBase is returning a Boolean, which the If is testing. There's no need to compare the Boolean with True - that's the default.

    Friend Function SQL_DropDataBase(ByVal L_DBName As String) As Boolean
      Dim L_SQLString As String = "DROP DATABASE " & L_DBName
      Dim L_Result As Boolean
    
      [B][COLOR=#ff0000]Using[/COLOR][/B] L_SQL_Connection As New SqlClient.SqlConnection(ConString)
        Dim L_SQLCommand As New SqlClient.SqlCommand(L_SQLString, L_SQL_Connection)
    
        L_SQLCommand.Connection.Open()
        Try
          L_Result = True
    
          L_SQLCommand.ExecuteNonQuery()
        Catch ex As Exception
          L_Result = False
    
          Beep()
          G_ErrorCount += 1
          MessageBox.Show(ex.Message)
        End Try
    
        L_SQL_Connection.Close()
        L_SQL_Connection.Dispose()
      [B][COLOR=#ff0000]End Using[/COLOR][/B]
    
      Return L_Result
    End Function
    Code (markup):
    Drop the Using. Using a variable that's being Used (you do it 3 times) is not only wasteful and semantically incorrect, it can cause your code to fail.
     
    Rukbat, Dec 30, 2012 IP
  7. dwirch

    dwirch Well-Known Member

    Messages:
    239
    Likes Received:
    12
    Best Answers:
    1
    Trophy Points:
    135
    #7
    does the account that is performing the drop have the necessary permissions ?
     
    dwirch, Jan 14, 2013 IP
  8. brandon813

    brandon813 Member

    Messages:
    77
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    45
    #8
    You are trying to drop the entire database? I would expect that you can't drop a database from code like this if there are active connections. And if you are actively connecting to the database in question in order to drop it, then you would have an active connection.

    So, my guess is that you need to do DROP TABLE instead of DROP DATABASE
     
    brandon813, Feb 9, 2013 IP
  9. brandon813

    brandon813 Member

    Messages:
    77
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    45
    #9
    Here is a way to do this;

    http://www.fragmentedcode.com/2008/04/22/sql-server-drop-tables-database-quickly/

    If I were you, I'd create this code as a stored procedure and this call the stored procedure from code. Again, you probably will never be able to drop a database while you have an active connection to it.



    SELECT NAME
    INTO #tables
    FROM sys.objects
    WHERE type = 'U'
     
    WHILE (
            SELECT count(1)
            FROM #tables
        ) > 0
    BEGIN
        DECLARE @sql VARCHAR(max)
        DECLARE @tbl VARCHAR(255)
     
        SELECT TOP 1 @tbl = NAME
        FROM #tables
     
        SET @sql = 'DROP TABLE ' + @tbl
     
        EXEC (@sql)
     
        DELETE
        FROM #tables
        WHERE NAME = @tbl
    END
     
    DROP TABLE #tables;
    
    Code (markup):
     
    brandon813, Feb 9, 2013 IP
  10. funkseo

    funkseo Greenhorn

    Messages:
    23
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #10
    integrated security=true means that you're connection to sql with current windows user.
    Are you sure that your logged windows user is admin?
     
    funkseo, Mar 11, 2013 IP