Creating the Book Store Administrator Application

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