Why does my connection states that its open but then when i tried to run it the error tells me that the connection is close?

Question

Why does my connection states that its open but then when i tried to run it the error tells me that the connection is close? Here is my code...

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

Thank you in advance.

Answer 1

You don't want to open connections until right before they are needed. They should be closed and disposed as soon as possible. Using...End Using blocks handle this. Connections do time out.

Always use parameters to avoid Sql injection and make writing the sql statements easier. Check your database for correct names of columns and datatypes. In Access it is important that paramaters are added to the parameter collection in the same order as they appear is the sql statment. Other providers can match parameters by name.

I think one of your problems was that you were putting values that are numbers in single quotes. Numbers have no single quotes. Single quotes indicate a string.

Normally inventories and Cost of Goods Sold are maintained in another table. Valuation of inventories can depend on LIFO, FIFO etc. and the price to the store will change with inflation, vendor, and quantity purchased among other things.

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