vb.net - 为什么我的连接表明它是打开的，但是当我尝试运行它时，错误告诉我连接已关闭？

Question

为什么我的连接表明它是打开的，但是当我尝试运行它时，错误告诉我连接已关闭？这是我的代码...

Public Class clsBookDA
    Private Shared dcnBooks As New OleDbConnection("Provider=Microsoft.Jet.OLDEB.4.0;DataSource=C:\temp\books.mdb")

    Private Shared books As New ArrayList()

    Private Shared aBook As clsBook
    Private Shared aBookID, 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
            aBookID = drwBook("BookID")
            anAuthor = drwBook("Author")
            aTitle = drwBook("Title")
            aPurchasePrice = drwBook("PurchasePrice")
            aSalePrice = drwBook("SalePrice")
            anInventory = drwBook("Inventory")
            Dim aBook As New clsBook(aBookID, 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.BookAU & "','" & aBook.BookTI & "','" & _
        aBook.BookPP & "','" & aBook.BookSP & "','" & aBook.BookINV & "')"

        dapBooks.UpdateCommand = New OleDbCommand(sqlQuery, dcnBooks)
        dapBooks.UpdateCommand.ExecuteNonQuery()<--- **i have a problem on this line. Keeps on telling that the connection is close.**
    End Sub

    Public Shared Sub Update(ByVal aBook As clsBook)
        Dim dapBooks As New OleDbDataAdapter()
        Dim sqlQuery As String = "UPDATE Books" & "SET Author='" & aBook.BookAU & "', Title = '" & aBook.BookTI & "', PurchasePrice = '" & _
            aBook.BookPP & "', SalePrice = '" & aBook.BookSP & "', Inventory = '" & aBook.BookINV & "'" & "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.netado.net

Answer 1

您不想在需要之前打开连接。它们应尽快关闭和处置。Using...End Using块处理这个。连接确实超时。

始终使用参数来避免 Sql 注入并使编写 sql 语句更容易。检查您的数据库以获取正确的列名和数据类型。在 Access 中，将参数添加到参数集合中的顺序与它们在 sql 语句中出现的顺序相同，这一点很重要。其他提供者可以按名称匹配参数。

我认为您的问题之一是您将数字值放在单引号中。数字没有单引号。单引号表示一个字符串。

通常库存和销售成本在另一个表中维护。存货估价可能取决于 LIFO、FIFO 等，并且商店的价格会随着通货膨胀、供应商和购买数量等因素而变化。

Public Class Book
    Public Property ID As Integer
    Public Property Author As String
    Public Property Title As String
    Public Property Price As Decimal
    Public Sub New()

    End Sub

    Public Sub New(BookID As Integer, BookAuthor As String, BookTitle As String, BookPrice As Decimal)
        ID = BookID
        Author = BookAuthor
        Title = BookTitle
        Price = BookPrice
    End Sub

End Class
Public Class AccessBooksData
    Private Shared cnStr As String = "Provider=Microsoft.Jet.OLDEB.4.0;DataSource=C:\temp\books.mdb"

    Public Shared Function GetAllBooks() As List(Of Book)
        Dim books As New List(Of Book)
        Dim dt As New DataTable
        Using cn As New OleDbConnection(cnStr),
           cmd As New OleDbCommand("Select * Form Books", cn)
            dt.Load(cmd.ExecuteReader)
        End Using

        For Each drwBook As DataRow In dt.Rows
            books.Add(New Book(CInt(drwBook("BookID")), CStr(drwBook("Title")), CStr(drwBook("Author")), CDec(drwBook("SalePrice"))))
        Next
        Return books
    End Function

    Public Shared Sub Add(ByVal bk As Book)
        'I am assuming that ID is an auto-increment field as Primary Key
        'Therefore you would not insert a value for that column
        Dim sqlQuery As String = "INSERT INTO Books ([Author], [Title], [SalePrice]) VALUES (@Author, @Title, @Price);"
        Using cn As New OleDbConnection(cnStr),
            cmd As New OleDbCommand(sqlQuery, cn)
            With cmd.Parameters
                .Add("@Author", OleDbType.VarChar).Value = bk.Author
                .Add("@Title", OleDbType.VarChar).Value = bk.Title
                .Add("@Price", OleDbType.Decimal).Value = bk.Price
            End With
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Public Shared Sub Update(ByVal aBook As Book)
        Dim sqlQuery As String = "UPDATE Books SET Author= @Author, Title = @Title, SalePrice = @Price, WHERE BookId = @ID;"
        Using cn As New OleDbConnection(cnStr),
            cmd As New OleDbCommand(sqlQuery, cn)
            With cmd.Parameters
                .Add("@Author", OleDbType.VarChar).Value = aBook.Author
                .Add("@Title", OleDbType.VarChar).Value = aBook.Title
                .Add("@SalePrice", OleDbType.Decimal).Value = aBook.Price
                .Add("@ID", OleDbType.Integer).Value = aBook.ID
            End With
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Public Shared Sub Delete(ByVal aBook As Book)
        Using cn As New OleDbConnection(cnStr),
            cmd As New OleDbCommand("DELETE FROM Books WHERE BookId = @ID;", cn)
            cmd.Parameters.Add("@ID", OleDbType.Integer).Value = aBook.ID
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub
End Class