NET InstantCode. UML with Visio and Visual Studio .NET

The Book Store Administrator application consists of the following modules:

• Application Manager

• Books Information

• Books Category

• Modify Book Information

• View Books Information

• View Order Information

• View Orders windows

• Shared

Creating the Shared Module

The Shared.vb file defines the Shared module and provides the main() function, which initiates the Book Store Administrator application.

Listing 3-2 shows the code for the Shared.vb file:

Listing 3-2: The Shared.vb File

Imports System.Data.SqlClient Imports System.IO Module _Shared Public boolConfirmation As Boolean Public travelerId As Integer Public sqlConnection As New sqlConnection Public Sub main() 'Try to establish SQL connection to database bookstore through SQL Connection object Try Try 'Read connectionstring from a text file settings.txt located in /bin folder of the application Dim sr As New StreamReader("Settings.txt") sqlConnection.ConnectionString = sr.ReadLine sr.Close() Catch ex As Exception sqlConnection.ConnectionString = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=BookStore" End Try sqlConnection.Open() Dim objApplicationManager As New ApplicationManager objApplicationManager.ShowDialog() Catch ex As Exception MsgBox("Error in establishing connection.", MsgBoxStyle.OKOnly, "CONNECTION ERROR") Exit Sub End Try End Sub End Module

Download this Listing .

In the above listing, the main() function establishes a connection with the BookStore database through a SqlConnection object and invokes the Application Manager window.

Creating the Books Category Window

The BookCategory class defines the Books Category window. The BookCategory class enables the administrator to view, add, delete, and modify information on book categories.

Listing 3-3 shows the code for the frmBooksCategory.vb that defines the BookCategory class:

Listing 3-3: The frmBooksCategory.vb File

Imports System.Data.SqlClient Public Class BookCategory Inherits System.Windows.Forms.Form Dim sqlCommand As SqlCommand Dim sqlda As SqlDataAdapter Dim ds As DataSet Dim tempListViewItem As ListViewItem Private Sub BookCategory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Retrieve existing book categories in the database Try sqlCommand = New SqlCommand("select * from [Books Category]", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) Catch ex As Exception MsgBox("Unable to retrieve book categories.", MsgBoxStyle.OKOnly, "Retrieval Error") Exit Sub End Try If Not ds.Tables(0).Rows.Count = 0 Then Dim rowcount As Integer For rowcount = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = listviewBooksCategory.Items.Add(ds.Tables(0).Rows(rowcount).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(1)) Next End If End Sub Private Sub cmdDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdDelete.Click If Not txtCategory.Text = "" And Not txtCategoryId.Text = "" Then 'Delete the specified book category from table Books Category Try sqlCommand = New SqlCommand("delete from [Books Category] where CategoryId='" & txtCategoryId.Text & "'", sqlConnection) sqlCommand.ExecuteNonQuery() listviewBooksCategory.Items.Remove(listviewBooksCategory.SelectedItems(0)) Catch ex As Exception MsgBox("Unable to delete the specified book category.", MsgBoxStyle.OKOnly, "Database Error") Exit Sub End Try txtCategory.Text = "" txtCategoryId.Text = "" txtCategoryId.Focus() End If End Sub Private Sub listviewBooksCategory_Click(ByVal sender As Object, ByVal e As _ System.EventArgs) Handles listviewBooksCategory.Click If Not listviewBooksCategory.SelectedItems(0).Text = "" Then txtCategoryId.Text = listviewBooksCategory.SelectedItems(0).Text txtCategory.Text = listviewBooksCategory.SelectedItems(0).SubItems(1).Text End If End Sub Private Sub cmdAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdAdd.Click If Not txtCategory.Text = "" And Not txtCategoryId.Text = "" Then 'Insert the new book category into table Books Category Try sqlCommand = New SqlCommand("insert into [Books Category] values('" & txtCategoryId.Text & "','" & txtCategory.Text & "')", sqlConnection) sqlCommand.ExecuteNonQuery() tempListViewItem = listviewBooksCategory.Items.Add(txtCategoryId.Text) tempListViewItem.SubItems.Add(txtCategory.Text) Catch ex As Exception MsgBox("Unable to add the specified book category.", MsgBoxStyle.OKOnly, "Database Error") Exit Sub End Try txtCategory.Text = "" txtCategoryId.Text = "" txtCategoryId.Focus() End If End Sub Private Sub cmdUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click If Not txtCategory.Text = "" And Not txtCategoryId.Text = "" Then 'Update the specified book category from table Books Category Try sqlCommand = New SqlCommand("update [Books Category] set Category='" & txtCategory.Text & "'where CategoryId='" & txtCategoryId.Text & "'", sqlConnection) sqlCommand.ExecuteNonQuery() listviewBooksCategory.SelectedItems(0).SubItems(1).Text = txtCategory.Text Catch ex As Exception MsgBox("Unable to update the specified book category.", MsgBoxStyle.OKOnly, "Database Error") Exit Sub End Try txtCategory.Text = "" txtCategoryId.Text = "" txtCategoryId.Focus() End If End Sub Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click Me.Close() End Sub End Class

Download this Listing .

The above listing defines the following methods :

• BookCategory_Load() : Retrieves the available book categories from the Books Category table and displays the categories in the listviewBooksCategory list box.

• cmdAdd_Click() : Executes when the administrator specifies the Category Id and Category values, and clicks the Add Category button. This method saves information about new book categories in the Books Category table.

• cmdDelete_Click() : Executes when the administrator selects a book category from the listviewBooksCategory list box and clicks the Delete Category button. This method deletes the information about the selected book category from the Books Category table.

Figure 3-4 shows the output of Listing 3-3:

Figure 3-4: The Books Category Window

Creating the Books Information Window

The Books class defines the Books Information window. The Books class enables the administrator to select a book category and add books to that book category.

Listing 3-4 shows the code for the frmBooks.vb that defines the Books class:

Listing 3-4: The frmBooks.vb File

Imports System.Data.SqlClient Public Class Books Inherits System.Windows.Forms.Form Dim sqlCommand As SqlCommand Dim sqlda As SqlDataAdapter Dim ds As DataSet Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click If Not booksCombo.Text = "" Then If txtName.Text = "" Then MsgBox("Please specify the name of the book.", MsgBoxStyle.OKOnly, "Check the information") txtName.Focus() Exit Sub End If If txtAuthor.Text = "" Then MsgBox("Please specify the author of the book.", MsgBoxStyle.OKOnly, "Check the information") txtAuthor.Focus() Exit Sub End If If txtPublisher.Text = "" Then MsgBox("Please specify the publisher of the book.", MsgBoxStyle.OKOnly, "Check the information") txtPublisher.Focus() Exit Sub End If If txtDescription.Text = "" Then MsgBox("Please specify the description for the book.", MsgBoxStyle.OKOnly, "Check the information") txtDescription.Focus() Exit Sub End If If txtPrice.Text = "" Then MsgBox("Please specify the price of the book.", MsgBoxStyle.OKOnly, "Check the information") txtPrice.Focus() Exit Sub End If If txtImagePath.Text = "" Then MsgBox("Please specify the path of the image for the book.", MsgBoxStyle.OKOnly, "Check the information") txtImagePath.Focus() Exit Sub End If 'Insert the new book information into table Books Try sqlCommand = New SqlCommand("select categoryId from [Books Category] where category='" & booksCombo.Text & "'", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) If Not ds.Tables(0).Rows.Count = 0 Then sqlCommand = New SqlCommand("insert into Books values('" & txtName.Text & "','" & ds.Tables(0).Rows(0).Item(0) & "','" & txtAuthor.Text & "','" & txtPublisher.Text & "'," & txtPrice.Text & ",'" & txtDescription.Text & "','" & txtImagePath.Text & "')", sqlConnection) sqlCommand.ExecuteNonQuery() End If Catch ex As Exception MsgBox("Unable to add information for new book.", MsgBoxStyle.OKOnly, "Database Error") Exit Sub End Try txtName.Text = "" txtPrice.Text = "" txtDescription.Text = "" txtAuthor.Text = "" txtPublisher.Text = "" txtImagePath.Text = "" txtName.Focus() End If End Sub Private Sub Books_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Retrieve existing book categories in the database Try sqlCommand = New SqlCommand("select * from [Books Category]", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) Catch ex As Exception MsgBox("Unable to retrieve book categories.", MsgBoxStyle.OKOnly, "Retrieval Error") Exit Sub End Try If Not ds.Tables(0).Rows.Count = 0 Then 'Add the retrieved categories in the booksCombo Dim rowcount As Integer For rowcount = 0 To ds.Tables(0).Rows.Count 1 booksCombo.Items.Add(ds.Tables(0).Rows(rowcount).Item(1)) Next End If If Not booksCombo.Items.Count = 0 Then booksCombo.SelectedIndex = 0 End If End Sub Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click Me.Close() End Sub End Class

Download this Listing .

The above listing defines the following methods:

• Books_Load() : Retrieves the available book categories from the Books Category table in the booksCombo drop-down list box.

• cmdAdd_Click() : Executes when the administrator selects a book category, provides information about a book, and clicks the Add Book button to add the book to the selected book category. This method saves information on new books in the Books table.

Figure 3-5 shows the output of Listing 3-4:

Figure 3-5: The Book Information Window

Creating the View Books Information Window

The ViewBooks class defines the View Books Information window. The ViewBooks class enables the administrator to view the books that belong to a specific category.

Listing 3-5 shows the code for the frmViewBooks.vb that defines the ViewBooks class:

Listing 3-5: The frmViewBooks.vb File

Imports System.Data.SqlClient Public Class ViewBooks Inherits System.Windows.Forms.Form Dim sqlCommand As sqlCommand Dim sqlda As SqlDataAdapter Dim ds As DataSet Dim tempListViewItem As ListViewItem Private Sub ViewBooks_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Retrieve existing book categories in the database Try sqlCommand = New SqlCommand("select * from [Books Category]", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) Catch ex As Exception MsgBox("Unable to retrieve book categories.", MsgBoxStyle.OKOnly, "Retrieval Error") Exit Sub End Try If Not ds.Tables(0).Rows.Count = 0 Then 'Add the retrieved categories in the booksCombo Dim rowcount As Integer For rowcount = 0 To ds.Tables(0).Rows.Count 1 booksCombo.Items.Add(ds.Tables(0).Rows(rowcount).Item(1)) Next End If If Not booksCombo.Items.Count = 0 Then booksCombo.SelectedIndex = 0 End If End Sub Private Sub booksCombo_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles booksCombo.SelectedIndexChanged listviewBooks.Items.Clear() 'Retrieve information for the books that belongs to the selected book category If Not booksCombo.Text = "" Then Try sqlCommand = New SqlCommand("select * from Books where CategoryId=(Select CategoryId from [Books Category] where Category='" & booksCombo.Text & "')", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) Catch ex As Exception MsgBox("Unable to retrieve books for the selected category.", MsgBoxStyle.OKOnly, "Retrieval Error") Exit Sub End Try If Not ds.Tables(0).Rows.Count = 0 Then Dim rowcount As Integer For rowcount = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = listviewBooks.Items.Add(ds.Tables(0).Rows(rowcount).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(1)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(3)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(4)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(5)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(6)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(7)) Next End If End If End Sub Private Sub cmdModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)Handles cmdModify.Click If Not listviewBooks.SelectedItems.Count = 0 Then Dim objModifyBook As New ModifyBook(CInt(listviewBooks.SelectedItems(0).Text), booksCombo.Text, listviewBooks.SelectedItems(0).SubItems(1).Text, listviewBooks.SelectedItems(0).SubItems(2).Text, listviewBooks.SelectedItems(0).SubItems(3).Text, listviewBooks.SelectedItems(0).SubItems(4).Text, listviewBooks.SelectedItems(0).SubItems(5).Text, listviewBooks.SelectedItems(0).SubItems(6).Text) objModifyBook.ShowDialog() booksCombo_SelectedIndexChanged(sender, e) End If End Sub Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click Me.Close() End Sub End Class

Download this Listing .

The above listing defines the following methods:

• ViewBooks_Load() : Retrieves the available book categories from the Books Category table in the booksCombo drop-down list box.

• booksCombo_SelectedIndexChanged() : Executes when the administrator selects a book category from the booksCombo drop-down list box to view the books available in that category. This method retrieves information about books that belong to a selected book category and displays the information in the listviewBooks list view control.

• cmdModify_Click() : Executes when the administrator selects a book from the listviewBooks list view control and clicks the Modify button to modify the information about that book. This method invokes the Modify Book Information window.

Figure 3-6 shows the output of Listing 3-5:

Figure 3-6: The View Books Information Window

Creating the Modify Book Information Window

The ModifyBook class defines the Modify Book Information window. The ModifyBook class enables the administrator to modify the information for a book.

Listing 3-6 shows the code for the frmModifyBook.vb that defines the ModifyBook class:

Listing 3-6: The frmModifyBook.vb File

Imports System.Data.SqlClient Public Class ModifyBook Inherits System.Windows.Forms.Form Dim sqlCommand As sqlCommand Dim sqlda As SqlDataAdapter Dim ds As DataSet Dim 'This variable holds the information of the book to be modified Dim 'These variable bookId As Integer Dim category As String Dim bookname As String Dim author As String Dim publisher As String Dim description As String Dim price As Double Dim imagePath As String Public Sub New(ByVal Id As Integer, ByVal strcategory As String, ByVal strName As String, ByVal strAuthor As String, ByVal strPublisher As String, ByVal dblPrice As Double, ByVal strDescription As String, ByVal strImagePath As String) MyBase.New() InitializeComponent() bookId = Id category = strcategory bookname = strName author = strAuthor publisher = strPublisher description = strDescription price = dblPrice imagePath = strImagePath End Sub Private Sub ModifyBook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Retrieve existing book categories in the database Try sqlCommand = New SqlCommand("select * from [Books Category]", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) Catch ex As Exception MsgBox("Unable to retrieve book categories.", MsgBoxStyle.OKOnly, "Retrieval Error") Exit Sub End Try If Not ds.Tables(0).Rows.Count = 0 Then 'Add the retrieved categories in the booksCombo Dim rowcount As Integer For rowcount = 0 To ds.Tables(0).Rows.Count 1 booksCombo.Items.Add(ds.Tables(0).Rows(rowcount).Item(1)) Next End If txtName.Text = bookname txtBookId.Text = bookId txtAuthor.Text = author txtDescription.Text = description txtPrice.Text = price txtImagePath.Text = imagePath txtPublisher.Text = publisher booksCombo.Text = category End Sub Private Sub cmdUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click If Not booksCombo.Text = "" Then If txtName.Text = "" Then MsgBox("Please specify the name of the book.", MsgBoxStyle.OKOnly, "Check the information") txtName.Focus() Exit Sub End If If txtAuthor.Text = "" Then MsgBox("Please specify the author of the book.", MsgBoxStyle.OKOnly, "Check the information") txtAuthor.Focus() Exit Sub End If If txtPublisher.Text = "" Then MsgBox("Please specify the publisher of the book.", MsgBoxStyle.OKOnly, "Check the information") txtPublisher.Focus() Exit Sub End If If txtDescription.Text = "" Then MsgBox("Please specify the description for the book.", MsgBoxStyle.OKOnly, "Check the information") txtDescription.Focus() Exit Sub End If If txtPrice.Text = "" Then MsgBox("Please specify the price of the book.", MsgBoxStyle.OKOnly, "Check the information") txtPrice.Focus() Exit Sub End If If txtImagePath.Text = "" Then MsgBox("Please specify the path of the image for the book.", MsgBoxStyle.OKOnly, "Check the information") txtImagePath.Focus() Exit Sub End If Try sqlCommand = New SqlCommand("select categoryId from [Books Category] where category='" & booksCombo.Text & "'", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) sqlCommand = New SqlCommand("update Books set name='" & txtName.Text & "', categoryid=" & ds.Tables(0).Rows(0).Item(0) & ", author= '" & txtAuthor.Text & "', publisher='" & txtPublisher.Text & "', price=" & txtPrice.Text & ", description='" & txtDescription.Text & "', picturepath='" & txtImagePath.Text & "' where bookid=" & bookId & "", sqlConnection) sqlCommand.ExecuteNonQuery() Catch ex As Exception MsgBox("Unable to update information for the selected book.", MsgBoxStyle.OKOnly, "Database Error") Me.Close() End Try Me.Close() End If End Sub End Class

Download this Listing .

The above listing defines the following methods:

• ModifyBook_Load() : Retrieves the book categories available from the Books Category table and loads the categories in the booksCombo drop-down list box.

• cmdUpdate_Click() : Executes when the administrator changes the required information for a book and clicks the Update button to update the changes. This method updates the information about the selected book in the Books table.

Figure 3-7 shows the output of Listing 3-6:

Figure 3-7: The Modify Book Information Window

Creating the View Orders Window

The ViewOrders class defines the View Orders window. The ViewOrders class displays the orders placed by end users and enables the administrator to update orders.

Listing 3-7 shows the code for the frmViewOrders.vb that defines the ViewOrders class:

Listing 3-7: The frmViewOrders.vb File

Imports System.Data.SqlClient Public Class ViewOrders Inherits System.Windows.Forms.Form Dim sqlCommand As sqlCommand Dim sqlda As SqlDataAdapter Dim ds As DataSet Dim tempListViewItem As ListViewItem Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click Me.Close() End Sub Private Sub ViewOrders_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim dt As System.DateTime OrderdtPicker.Value = dt.Today Dim custds As DataSet 'Retrieve pending orders Try sqlCommand = New SqlCommand("select OrderId, OrderDate, CustomerId, Amount from Orders where status='P'", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) If Not ds.Tables(0).Rows.Count = 0 And Not ds.Tables.Count = 0 Then Dim rowcount As Integer For rowcount = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = listviewOrders.Items.Add(ds.Tables(0).Rows(rowcount).Item(0)) tempListViewItem.SubItems.Add(CType(ds.Tables(0).Rows(rowcount).Item(1), String)) sqlCommand = New SqlCommand("select username from customers where customerid=" & ds.Tables(0).Rows(rowcount).Item(2) & "", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) custds = New DataSet sqlda.Fill(custds) tempListViewItem.SubItems.Add(custds.Tables(0).Rows(0).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(3)) Next End If Catch ex As Exception MsgBox("Unable to retrieve pending orders.", MsgBoxStyle.OKOnly, "Retrieval Error") Exit Sub End Try End Sub Private Sub OrderdtPicker_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles OrderdtPicker.ValueChanged Dim custds As DataSet If Not listviewOrders.Items.Count = 0 Then listviewOrders.Items.Clear() Try sqlCommand = New SqlCommand("select OrderId, OrderDate, CustomerId, Amount from Orders where status='P' and orderdate='" & OrderdtPicker.Value.ToShortDateString & "'", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) If Not ds.Tables(0).Rows.Count = 0 And Not ds.Tables.Count = 0 Then Dim rowcount As Integer For rowcount = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = listviewOrders.Items.Add(ds.Tables(0).Rows(rowcount).Item(0)) tempListViewItem.SubItems.Add(CType(ds.Tables(0).Rows(rowcount).Item(1), String)) sqlCommand = New SqlCommand("select username from customers where customerid=" & ds.Tables(0).Rows(rowcount).Item(2) & "", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) custds = New DataSet sqlda.Fill(custds) tempListViewItem.SubItems.Add(custds.Tables(0).Rows(0).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(3)) Next End If Catch ex As Exception MsgBox("Unable to retrieve pending orders.", MsgBoxStyle.OKOnly, "Retrieval Error") Exit Sub End Try End If End Sub Private Sub cmdShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdShow.Click Dim custds As DataSet listviewOrders.Items.Clear() Try sqlCommand = New SqlCommand("select OrderId, OrderDate, CustomerId, Amount from Orders where status='P'", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) If Not ds.Tables(0).Rows.Count = 0 And Not ds.Tables.Count = 0 Then Dim rowcount As Integer For rowcount = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = listviewOrders.Items.Add(ds.Tables(0).Rows(rowcount).Item(0)) tempListViewItem.SubItems.Add(CType(ds.Tables(0).Rows(rowcount).Item(1), String)) sqlCommand = New SqlCommand("select username from customers where customerid=" & ds.Tables(0).Rows(rowcount).Item(2) & "", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) custds = New DataSet sqlda.Fill(custds) tempListViewItem.SubItems.Add(custds.Tables(0).Rows(0).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(3)) Next End If Catch ex As Exception MsgBox("Unable to retrieve pending orders.", MsgBoxStyle.OKOnly, "Retrieval Error") Exit Sub End Try End Sub Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click If Not listviewOrders.SelectedItems.Count = 0 Then 'Update the status of the order as completed Dim dt As System.DateTime Try sqlCommand = New SqlCommand("update orders set status='C', completiondate='" & dt.Today & "' where orderId=" & listviewOrders.SelectedItems(0).Text & "", sqlConnection) sqlCommand.ExecuteNonQuery() listviewOrders.Items.Remove(listviewOrders.SelectedItems(0)) Catch ex As Exception MsgBox("Unable to update the status of the selected order.", MsgBoxStyle.OKOnly, "Database Error") Exit Sub End Try End If End Sub Private Sub cmdViewInformation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdViewInformation.Click If Not listviewOrders.SelectedItems.Count = 0 Then Dim objViewOrderInfo As New ViewOrderInfo(listviewOrders.SelectedItems(0).Text) objViewOrderInfo.ShowDialog() End If End Sub End Class

Download this Listing .

The above listing defines the following methods:

• ViewOrders_Load() : Retrieves information about pending orders and displays the information in the listviewOrders list view control.

• cmdViewInformation_Click() : Executes when the administrator selects an order from the Pending Orders list and clicks the View Information button to view information on the books for the selected order. This method invokes the View Order Information window.

• cmdUpdate_Click() : Executes when the administrator selects an order from the Pending Orders list and clicks the Update Order button to update the order status as completed. This method updates the status of the order as completed.

Figure 3-8 shows the output of Listing 3-7:

Figure 3-8: The View Orders Window

Creating the View Order Information Window

The ViewOrderInfo class defines the View Order Information window. The ViewOrderInfo class displays information on the books, such as the name of the book and the number of copies ordered.

Listing 3-8 shows the code for the frmViewOrderInfo.vb that defines the ViewOrderInfo class:

Listing 3-8: The frmViewOrderInfo.vb File

Imports System.Data.SqlClient Public Class ViewOrderInfo Inherits System.Windows.Forms.Form Dim orderId As Integer Public Sub New(ByVal id As Integer) MyBase.New() InitializeComponent() orderId = id End Sub Dim sqlCommand As sqlCommand Dim sqlda As SqlDataAdapter Dim ds As DataSet Dim tempListViewItem As ListViewItem Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click Me.Close() End Sub Private Sub ViewOrderInfo_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load Try sqlCommand = New SqlCommand("select OrderId, Name, sum(Qty) as total from Books, OrderDetails where Books.BookId=OrderDetails.BookId and OrderId=" & orderId & " group by OrderId, OrderDetails.BookId, name", sqlConnection) sqlda = New SqlDataAdapter(sqlCommand) ds = New DataSet sqlda.Fill(ds) If Not ds.Tables(0).Rows.Count = 0 And Not ds.Tables.Count = 0 Then Dim rowcount As Integer For rowcount = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = listviewOrders.Items.Add(ds.Tables(0).Rows(rowcount).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(1)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(rowcount).Item(2)) Next End If Catch ex As Exception MsgBox("Unable to retrieve order information.", MsgBoxStyle.OKOnly, "Retrieval Error") Exit Sub End Try End Sub End Class

Download this Listing .

In the above listing, the ViewOrderInfo_Load() method retrieves the information about the books for the selected order and displays the information in the listviewOrders list view control.

Figure 3-9 shows the output of Listing 3-8:

Figure 3-9: The View Order Information Window

Creating the Application Manager Window

The ApplicationManager class defines the Application Manager window that enables the administrator to access various windows of the Book Store Administrator application.

Listing 3-9 shows the code for the frmApplicationManager.vb that defines the ApplicationManager class:

Listing 3-9: The frmApplicationManager.vb File

Public Class ApplicationManager Inherits System.Windows.Forms.Form Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click Dim objBookCategory As New BookCategory objBookCategory.ShowDialog() End Sub Private Sub MenuItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem4.Click Dim objBooks As New Books objBooks.ShowDialog() End Sub Private Sub MenuItem5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem5.Click Dim objViewBooks As New ViewBooks objViewBooks.ShowDialog() End Sub Private Sub MenuItem6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem6.Click Dim objViewOrders As New ViewOrders objViewOrders.ShowDialog() End Sub End Class

Download this Listing .

The above listing defines the following methods:

• MenuItem3_Click() : Executes when the administrator selects Books->Add Book Category to add a category. This method invokes the Books Category window

• MenuItem4_Click() : Executes when the administrator selects Books->Add Book to add new books. This method invokes the Books Information window.

• MenuItem5_Click() : Executes when the administrator selects Books->View Books to view the information about available books. This method invokes the View Books window.

• MenuItem6_Click() : Executes when the administrator selects Orders->View Orders to view pending orders and update the status of pending orders. This method invokes the View Orders window.

Figure 3-10 shows the output of Listing 3-9:

Figure 3-10: The Application Manager Window at Design Time