none
刪除資料庫的問題 RRS feed

  • 問題

  • 各位大大好~小弟有使用以下的程式碼建立的一個名為 "ABC.mdf" 的資料庫,

    1. 請問如果要把這個資料庫刪除的話,程式碼應該要怎麼寫呢?

    2. 我自己有手動去刪除這個資料庫 (因為我要測試看是不是能正常建立) ,然後又再次建立資料庫,

    結果出現 "資料庫 'ABC' 已經存在。請選擇其他資料庫名稱。"

    可是資料庫已經沒有了阿?


            Dim ConnectionStr As String = "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True"
            Dim Connection As New SqlClient.SqlConnection(ConnectionStr)

            Dim SQLCmd As String = "Create Database ABC On (Name=ABC,FileName='C:\ABC.mdf')"

    Dim Command As New SqlClient.SqlCommand(SQLCmd, Connection)

            Try
                Connection.Open()
                Command.ExecuteNonQuery()
                MsgBox("已成功建立《 ABC 》資料庫")
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                If (Connection.State = ConnectionState.Open) Then
                    Connection.Close()
                    Connection.Dispose()
                    Connection = Nothing
                End If
            End Try

    2011年2月15日 上午 05:22

解答

  • If using query, you should run "drop database" first.

    http://technet.microsoft.com/zh-tw/library/ms178613.aspx

    The reason why you cannot create another one, because, the file is deleted but the SQL master table still having the ABC database record.


    大家一齊探討、學習和研究,謝謝!
    Microsoft MVP, Microsoft Community Star(TW & HK), MCT,
    MCSD, MCAD, MCSE+I, MCDBA, MCDST, MCSA, MCTS, MCITP, MCPD
    • 已標示為解答 暗夜鬼 2011年2月16日 上午 02:49
    2011年2月15日 上午 05:58
  • 感謝大大~我已經解決問題了~

    下面是新增資料庫的部份

    Dim ConnStr As String = "Server=127.0.0.1\SQLEXPRESS;uid=sa;pwd=XXX;database=master"

            Dim Connection As New SqlClient.SqlConnection(ConnStr)

            Dim SQLCmd As String = "Create Database ABC On PRIMARY (Name=ABC,FileName='C:\backup\ABC.mdf')"
            Dim Command As New SqlClient.SqlCommand(SQLCmd, Connection)

            Try
                Connection.Open()
                Command.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                If (Connection.State = ConnectionState.Open) Then
                    Connection.Close()
                    Connection.Dispose()
                    Connection = Nothing
                End If
            End Try

    下面是刪除資料庫的部份

    Dim ConnStr As String = "Server=127.0.0.1\SQLEXPRESS;uid=sa;pwd=XXX;database=master"

            Dim Connection As New SqlClient.SqlConnection(ConnStr)
            Dim SQLCmd As String = "DROP DATABASE ABC"

            Dim Command As New SqlClient.SqlCommand(SQLCmd, Connection)

            Try
                Connection.Open()
                Command.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                If (Connection.State = ConnectionState.Open) Then
                    Connection.Close()
                    Connection.Dispose()
                    Connection = Nothing
                End If
            End Try

    • 已標示為解答 暗夜鬼 2011年2月16日 上午 02:49
    2011年2月16日 上午 02:49

所有回覆

  • If using query, you should run "drop database" first.

    http://technet.microsoft.com/zh-tw/library/ms178613.aspx

    The reason why you cannot create another one, because, the file is deleted but the SQL master table still having the ABC database record.


    大家一齊探討、學習和研究,謝謝!
    Microsoft MVP, Microsoft Community Star(TW & HK), MCT,
    MCSD, MCAD, MCSE+I, MCDBA, MCDST, MCSA, MCTS, MCITP, MCPD
    • 已標示為解答 暗夜鬼 2011年2月16日 上午 02:49
    2011年2月15日 上午 05:58
  • 感謝大大~下面是刪除的程式碼,結果出現

     

    無法開啟實體檔案 "C:\ABC.mdf"。作業系統錯誤 32: "32(程序無法存
    取檔案,因為檔案正由另一個程序使用。)"。

     

    Dim ConnStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\ABC.mdf;Integrated Security=True;User Instance=True"

    Dim Connection As New SqlClient.SqlConnection(ConnStr)

    Dim SQLCmd As String = "DROP DATABASE ABC"

    Dim Command As New SqlClient.SqlCommand(SQLCmd, Connection)

    Try
        Connection.Open()
        Command.ExecuteNonQuery()
    Catch ex As Exception
        MsgBox(ex.ToString)
    Finally
        If (Connection.State = ConnectionState.Open) Then
            Connection.Close()
            Connection.Dispose()
            Connection = Nothing
        End If
    End Try

    2011年2月16日 上午 01:57
  • From your message "程序無法存取檔案,因為檔案正由另一個程序使用。"

    The file is also opening by other program.


    大家一齊探討、學習和研究,謝謝!
    Microsoft MVP, Microsoft Community Star(TW & HK), MCT,
    MCSD, MCAD, MCSE+I, MCDBA, MCDST, MCSA, MCTS, MCITP, MCPD
    2011年2月16日 上午 02:00
  • 感謝大大~我已經解決問題了~

    下面是新增資料庫的部份

    Dim ConnStr As String = "Server=127.0.0.1\SQLEXPRESS;uid=sa;pwd=XXX;database=master"

            Dim Connection As New SqlClient.SqlConnection(ConnStr)

            Dim SQLCmd As String = "Create Database ABC On PRIMARY (Name=ABC,FileName='C:\backup\ABC.mdf')"
            Dim Command As New SqlClient.SqlCommand(SQLCmd, Connection)

            Try
                Connection.Open()
                Command.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                If (Connection.State = ConnectionState.Open) Then
                    Connection.Close()
                    Connection.Dispose()
                    Connection = Nothing
                End If
            End Try

    下面是刪除資料庫的部份

    Dim ConnStr As String = "Server=127.0.0.1\SQLEXPRESS;uid=sa;pwd=XXX;database=master"

            Dim Connection As New SqlClient.SqlConnection(ConnStr)
            Dim SQLCmd As String = "DROP DATABASE ABC"

            Dim Command As New SqlClient.SqlCommand(SQLCmd, Connection)

            Try
                Connection.Open()
                Command.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                If (Connection.State = ConnectionState.Open) Then
                    Connection.Close()
                    Connection.Dispose()
                    Connection = Nothing
                End If
            End Try

    • 已標示為解答 暗夜鬼 2011年2月16日 上午 02:49
    2011年2月16日 上午 02:49