Alantric Books Codes





Public Class clsBookDA
    Private Shared dcnBooks As New _
        OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=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.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
Public Class frmAddBooks

    Private books As New ArrayList

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If txtBookID.Text.Length = 0 Or txtAuthor.Text.Length = 0 Or _
            txtTitle.Text.Length = 0 Or txtPurchasePrice.Text.Length = 0 Or _
            txtSalePrice.Text.Length = 0 Or txtInventory.Text.Length = 0 Then
            MessageBox.Show("Please enter all the data.")
        ElseIf Not IsValidISBN(txtBookID.Text) Then
            MessageBox.Show("Invalid ISBN. Please enter a valid ISBN.")
            txtBookID.Focus()
        ElseIf Not IsNumeric(txtPurchasePrice.Text) Then
            MessageBox.Show("Please enter a numeric Purchase Price.")
            txtPurchasePrice.Focus()
        ElseIf Not IsNumeric(txtSalePrice.Text) Then
            MessageBox.Show("Please enter a numeric Sale Price.")
            txtSalePrice.Focus()
        ElseIf Not IsNumeric(txtInventory.Text) Then
            MessageBox.Show("Please enter a numeric Number of Items on Stock.")
            txtInventory.Focus()
        Else
            Dim aBook As New clsBook(txtBookID.Text, txtAuthor.Text, _
                txtTitle.Text, CDec(txtPurchasePrice.Text), CDec(txtSalePrice.Text), _
                CInt(txtInventory.Text))
            Try
                aBook.Add()
                books.Add(aBook)
                lblNumberRecords.Text = books.Count
                MessageBox.Show("Record added to the database.")
                btnClear.PerformClick()
            Catch
                MessageBox.Show("A book with ISBN = " & txtBookID.Text & _
                    " already exists in the database." & vbCrLf & _
                    "Please enter a new ISBN or Press Clear to enter details of a new book.")
                txtBookID.Focus()
            End Try
        End If
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        txtBookID.Text = ""
        txtAuthor.Text = ""
        txtTitle.Text = ""
        txtPurchasePrice.Text = ""
        txtSalePrice.Text = ""
        txtInventory.Text = ""
        txtBookID.Focus()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Function IsValidISBN(ByVal ISBN As String) As Boolean
        If ISBN.Length = 10 Then Return IsValidISBN10(ISBN) Else Return IsValidISBN13(ISBN)
    End Function

    Private Function IsValidISBN10(ByVal ISBN As String) As Boolean
        Dim i, sum, cd As Integer
        Dim checkDigit As Char
        For i = 0 To ISBN.Length - 2
            sum = sum + Val(ISBN.Chars(i)) * (10 - i)
        Next
        cd = (11 - sum Mod 11) Mod 11
        If cd = 10 Then checkDigit = "X" Else checkDigit = cd.ToString()
        If checkDigit = ISBN.Chars(i) Then Return True Else Return False
    End Function

    Private Function IsValidISBN13(ByVal ISBN As String) As Boolean
        Dim i, sum, cd As Integer
        Dim checkDigit As Char
        For i = 0 To 10 Step 2
            sum = sum + Val(ISBN.Chars(i))
            sum = sum + Val(ISBN.Chars(i + 1)) * 3
        Next
        cd = (10 - sum Mod 10) Mod 10
        checkDigit = cd.ToString()
        If checkDigit = ISBN.Chars(i) Then Return True Else Return False
    End Function

    Private Sub frmAddBooks_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        books = clsBook.GetAll()
        lblNumberRecords.Text = books.Count
    End Sub
End Class
Public Class frmDeleteBooks

    Private books As New ArrayList
    Private index As Integer = 0

    Private Sub ShowCurrentRecord()
        Dim PurchasePrice, SalePrice As Decimal
        If books.Count > 0 Then
            txtBookID.Text = books(index).BookID
            txtAuthor.Text = books(index).Author
            txtTitle.Text = books(index).Title
            PurchasePrice = books(index).PurchasePrice
            txtPurchasePrice.Text = PurchasePrice.ToString("N")
            SalePrice = books(index).SalePrice
            txtSalePrice.Text = SalePrice.ToString("N")
            txtInventory.Text = books(index).Inventory
        Else
            txtBookID.Text = ""
            txtAuthor.Text = ""
            txtTitle.Text = ""
            txtPurchasePrice.Text = ""
            txtSalePrice.Text = ""
            txtInventory.Text = ""
        End If
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If books.Count > 0 Then
            Dim aBook As New clsBook(txtBookID.Text, txtAuthor.Text, _
                txtTitle.Text, CDec(txtPurchasePrice.Text), _
                CDec(txtSalePrice.Text), CInt(txtInventory.Text))
            aBook.Delete()
            books.RemoveAt(index)
            lblNumberRecords.Text = books.Count
            If index = books.Count Then index = index - 1
            Me.ShowCurrentRecord()
            MessageBox.Show("Record has been deleted from the database.")
        Else
            MessageBox.Show("There are no more records in the database.")
        End If
    End Sub

    Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
        index = 0
        Me.ShowCurrentRecord()
    End Sub

    Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
        If index > 0 Then
            index = index - 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
        If index < (books.Count - 1) Then
            index = index + 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
        If books.Count > 0 Then
            index = books.Count - 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub frmDeleteBooks_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        books = clsBook.GetAll()
        If books.Count = 0 Then
            MessageBox.Show("There are no records in the database.")
            Me.Close()
        Else
            lblNumberRecords.Text = books.Count
            Me.ShowCurrentRecord()
        End If
    End Sub
End Class

Public Class frmMainMenu

    Private Sub btnAddBooks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddBooks.Click
        Me.Hide()
        Dim frmAddBooksGUI = New frmAddBooks()
        frmAddBooksGUI.ShowDialog()
        Me.Show()
    End Sub

    Private Sub btnUpdateBooks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateBooks.Click
        Me.Hide()
        Dim frmUpdateBooksGUI = New frmUpdateBooks()
        frmUpdateBooksGUI.ShowDialog()
        Me.Show()
    End Sub

    Private Sub frmDeleteBooks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles frmDeleteBooks.Click
        Me.Hide()
        Dim frmDeleteBooksGUI = New frmDeleteBooks()
        frmDeleteBooksGUI.ShowDialog()
        Me.Show()
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        clsBook.Terminate()
        Me.Close()
    End Sub

    Private Sub frmMainMenu_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        clsBook.Initialize()
    End Sub

End Class
Public Class frmUpdateBooks

    Private books As New ArrayList
    Private index As Integer = 0

    Private Sub ShowCurrentRecord()
        Dim PurchasePrice, SalePrice As Decimal
        If books.Count > 0 Then
            txtBookID.Text = books(index).BookID
            txtAuthor.Text = books(index).Author
            txtTitle.Text = books(index).Title
            PurchasePrice = books(index).PurchasePrice
            txtPurchasePrice.Text = PurchasePrice.ToString("N")
            SalePrice = books(index).SalePrice
            txtSalePrice.Text = SalePrice.ToString("N")
            txtInventory.Text = books(index).Inventory
        End If
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        If books.Count > 0 Then
            Dim aBook As New clsBook(txtBookID.Text, txtAuthor.Text, _
                txtTitle.Text, CDec(txtPurchasePrice.Text), _
                CDec(txtSalePrice.Text), CInt(txtInventory.Text))
            aBook.Update()
            books(index) = aBook
            MessageBox.Show("Record has been updated in the database.")
        End If
    End Sub

    Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
        index = 0
        Me.ShowCurrentRecord()
    End Sub

    Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
        If index > 0 Then
            index = index - 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
        If index < (books.Count - 1) Then
            index = index + 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
        If books.Count > 0 Then
            index = books.Count - 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub frmUpdateBooks_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
        books = clsBook.GetAll()
        If books.Count = 0 Then
            MessageBox.Show("There are no records in the database.")
            Me.Close()
        Else
            lblNumberRecords.Text = books.Count
            Me.ShowCurrentRecord()
        End If
    End Sub

End Class