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