vb.net - 我做错了什么这里'ExecuteNonQuery 需要一个开放且可用的连接。连接的当前状态是关闭的。'? 我使用 Access 2016

Question

当我尝试通过我的应用程序添加“书籍”时，它总是会说“System.InvalidOperationException：'ExecuteNonQuery 需要一个打开且可用的连接。连接的当前状态是关闭的。'”但我已经建立了 OleDbConnection。我在这里犯了什么错误？

Public Class clsBookDA
    Private Shared dcnBooks As New _
        OleDbConnection("Provider=Microsoft.Jet.OLEDB.12.0;DataSource=C:\Temp\books.accdb")
    Private Shared books As New ArrayList()

    Private Shared aBook As clsBook
    Private Shared aBookdID, anAuthor, aTitle As String
    Private Shared aPurchasePrice, aSalePrice As Decimal
    Private Shared anInventory As Integer

    Public Shared Sub Initialize()
        dcnBooks.Open()
    End Sub
    Public Shared Sub Terminate()
        dcnBooks.Close()
        dcnBooks.Dispose()
    End Sub
    Public Shared Function GetAll() As ArrayList
        Dim dapBooks As New OleDbDataAdapter()
        Dim dtbBooks As New DataTable()
        Dim drwBook As DataRow

        dapBooks = New OleDbDataAdapter("Select*From Books", dcnBooks)
        dapBooks.Fill(dtbBooks)

        books.Clear()
        For Each drwBook In dtbBooks.Rows
            aBookdID = drwBook("BookID")
            anAuthor = drwBook("Author")
            aTitle = drwBook("Title")
            aPurchasePrice = drwBook("PurchasePrice")
            aSalePrice = drwBook("SalePrice")
            anInventory = drwBook("Inventory")
            Dim aBook As New clsBook(aBookdID, anAuthor, aTitle, aPurchasePrice, aSalePrice, anInventory)
            books.Add(aBook)
        Next
        Return books
    End Function
    Public Shared Sub Add(ByVal aBook As clsBook)
        Dim dapBooks As New OleDbDataAdapter()
        Dim sqlQuery As String = "INSERT INTO Books" & "VALUES('" &
            aBook.BookID & "','" & aBook.Author & "','" & aBook.Title & "','" &
            aBook.PurchasePrice & "','" & aBook.SalePrice & "','" &
            aBook.Inventory & "')"

        dapBooks.UpdateCommand = New OleDbCommand(sqlQuery, dcnBooks)
        dapBooks.UpdateCommand.ExecuteNonQuery()
    End Sub
    Public Shared Sub Update(ByVal aBook As clsBook)
        Dim dapBooks As New OleDbDataAdapter()
        Dim sqlQuery As String = "UPDATE Books" & "SET Author = '" &
            aBook.Author & "', Title = '" & aBook.Title & "', PurchasePrice ='" &
            aBook.PurchasePrice & "', SalePrice= '" & aBook.SalePrice &
            "', Inventory= '" & aBook.Inventory & "'" &
            "WHERE BookID = '" & aBook.BookID & "'"

        dapBooks.UpdateCommand = New OleDbCommand(sqlQuery, dcnBooks)
        dapBooks.UpdateCommand.ExecuteNonQuery()
    End Sub

    Public Shared Sub Delete(ByVal aBook As clsBook)
        Dim dapBooks As New OleDbDataAdapter()
        Dim sqlQuery As String = "DELETE FROM Books WHERE BookID='" &
            aBook.BookID & "'"

        dapBooks.UpdateCommand = New OleDbCommand(sqlQuery, dcnBooks)
        dapBooks.UpdateCommand.ExecuteNonQuery()
    End Sub
End Class

标签： vb.netms-access-2016

Answer 1

ArrayList参见https://docs.microsoft.com/en-us/dotnet/api/system.collections.arraylist?view=net-5.0的备注部分

连接和命令等数据库对象需要尽快关闭和处置。最好将它们保留在使用它们的方法中。即使出现错误，使用块也能确保清理这些对象。

始终使用参数来避免 Sql 注入。在Access中，参数在sql语句中出现的顺序必须与参数添加到参数集合中的顺序一致。

DataAdapters 具有您在代码中未使用的额外功能。使用较轻的命令对象。

我看到您似乎在连接的 sql 字符串中的数字类型周围加上了单引号。如果这些确实是 Access 中的数字，这将导致问题。参数通过提供数据库类型来避免这个问题，因此在 sql 字符串中不需要单引号。

不要将整个传递clsBook给删除子。只传id。

我将留给您重写更新代码。

Public Class clsBookDA
    Private Shared ConStr As String = "Provider=Microsoft.Jet.OLEDB.12.0;DataSource=C:\Temp\books.accdb"

    Public Shared Function GetAll() As List(Of clsBook)
        Dim dtbBooks As New DataTable()
        Using cn As New OleDbConnection(ConStr),
                cmd As New OleDbCommand("Select * From Books", cn)
            cn.Open()
            Using reader = cmd.ExecuteReader
                dtbBooks.Load(reader)
            End Using
        End Using
        Dim lst As New List(Of clsBook)
        For Each drwBook As DataRow In dtbBooks.Rows
            Dim bk As New clsBook(drwBook("BookID").ToString, drwBook("Author").ToString, drwBook("Title").ToString, CDec(drwBook("PurchasePrice")), CDec(drwBook("SalePrice")), CInt(drwBook("Inventory")))
            lst.Add(bk)
        Next
        Return lst
    End Function
    Public Shared Sub Add(ByVal aBook As clsBook)
        Dim sqlQuery As String = "INSERT INTO Books VALUES(@ID, @Author, @Title, @PurchasePrice, @SalePrice, @Inventory);"
        Using cn As New OleDbConnection(ConStr),
                cmd As New OleDbCommand(sqlQuery, cn)
            With cmd.Parameters
                .Add("@ID", OleDbType.VarChar).Value = aBook.BookID
                .Add("@Author", OleDbType.VarChar).Value = aBook.Author
                .Add("@Title", OleDbType.VarChar).Value = aBook.Title
                .Add("@PurchasePrice", OleDbType.Decimal).Value = aBook.PurchasePrice
                .Add("@SalePrice", OleDbType.Decimal).Value = aBook.SalePrice
                .Add("@Inventory", OleDbType.Integer).Value = aBook.Inventory
            End With
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub
    Public Shared Sub Update(ByVal aBook As clsBook)
        'To do
    End Sub

    Public Shared Sub Delete(BookID As String)
        Dim sqlQuery As String = "DELETE FROM Books WHERE BookID=@BookID;"
        Using cn As New OleDbConnection(ConStr),
                cmd As New OleDbCommand(sqlQuery, cn)
            cmd.Parameters.Add("@BookID", OleDbType.VarChar).Value = BookID
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub
End Class

Public Class clsBook
    Public Property BookID As String
    Public Property Author As String
    Public Property Title As String
    Public Property PurchasePrice As Decimal
    Public Property SalePrice As Decimal
    Public Property Inventory As Integer

    Public Sub New(id As String, auth As String, titl As String, purch As Decimal, sale As Decimal, inv As Integer)
        BookID = id
        Author = auth
        Title = titl
        PurchasePrice = purch
        SalePrice = sale
        Inventory = inv
    End Sub

End Class