To create the QueryTrackerAdministrator application, you need to create the Application Manager, Executive Manager, Forward Pending Query To Executive, Login Manager, Query Manager, and View Query Status windows .
The frmApplicationManager class defines the Application Manager window.
Listing 4-2 shows the code for the frmApplicationManager.vb file that defines the frmApplicationManager class:
Public Class frmApplicationManager Inherits System.Windows.Forms.Form Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click 'An object reference to class ExecutiveManager to define window Executives Manager Dim objExecManager As New ExecutiveManager objExecManager.ShowDialog() End Sub Private Sub MenuItem6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem6.Click 'An object reference to class QueryManager to define window Query Manager Dim objQueryManager As New QueryManager objQueryManager.ShowDialog() End Sub Private Sub MenuItem9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem9.Click 'An object reference to class ForwardQueryToExecutive to define window Forward Query To Executive Dim objForwardQuery As New ForwardQueryToExecutive objForwardQuery.ShowDialog() End Sub Private Sub MenuItem7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem7.Click 'An object reference to class ViewStatus to define window View Query Status Dim objViewStatus As New ViewStatus objViewStatus.ShowDialog() End Sub End Class
Download this Listing .
The above listing defines the following methods :
MenuItem2_Click() : Executes when the administrator selects the Executive Manager->Add Executive menu option. This method invokes the Executive Manager window.
MenuItem6_Click() : Executes when the administrator selects the Query Manager->Add Query menu option. This method invokes the Query Manager window.
MenuItem7_Click() : Executes when the administrator selects the Query Manager->View Status menu option. This method invokes the View Query Status window.
MenuItem9_Click() : Executes when the administrator selects the Query Manager->Forward Query To Executive menu option. This method invokes the Forward Pending Query to Executive window.
Figure 4-3 shows the output of Listing 4-2:
The LoginManager class defines the Login Manager window. This window enables the administrator to log on, enter new queries, and view the status of existing queries.
Listing 4-3 shows the code for the frmLoginManager.vb file that defines the LoginManager class:
Imports System.Data.SqlClient Imports System.IO Imports System.Collections Public Class LoginManager Inherits System.Windows.Forms.Form Dim strSqlConnection As String Dim SqlConn As New SqlConnection Dim dataReader As SqlDataReader Dim SqlCom As New SqlCommand Private Sub LoginManager_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Try to establish connection with database QueryTracker through SQLClient Connection object Try Try 'Read the connection string from a text file settings.txt located in '/bin folder of the application. Dim sr As New StreamReader("Settings.txt") strSqlConnection = sr.ReadLine sr.Close() Catch ex As Exception 'If file settings.txt not found strSqlConnection = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=QueryTracker" End Try SqlConn.ConnectionString = strSqlConnection SqlConn.Open() Catch ex As Exception MsgBox("Error in establishing connection", MsgBoxStyle.OKOnly, "CONNECTION ERROR") Exit Sub End Try End Sub Private Sub cmdGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGo.Click Try 'Verifying the specified information for Administrator Login SqlCom.CommandText = "select * from LoginAdministrator where adminName='" & _ txtUserName.Text & "' and adminPwd='" & txtPassWord.Text & "'" SqlCom.Connection = SqlConn dataReader = SqlCom.ExecuteReader Catch ex As Exception MsgBox("Error in administrator login", MsgBoxStyle.OKOnly, "LOGIN ERROR") txtUserName.Focus() Exit Sub End Try 'To check whether the login attempt is successful If dataReader.HasRows Then 'An object reference to frmMain form Dim objApplicationManager As New frmApplicationManager objApplicationManager.ShowDialog() Me.Dispose() txtUserName.Text = "" txtPassWord.Text = "" Else MsgBox("Error in administrator login.Login Unsuccessful", MsgBoxStyle.OKOnly, "INVALID LOGIN") txtUserName.Focus() Exit Sub End If 'closing the connection to the QueryTracker database SqlConn.Close() End Sub End Class
Download this Listing .
The above listing defines the following methods:
LoginManager_Load() : Establishes a connection with the QueryTracker database through an SQLConnection object.
cmdGo_Click() : Executes when the administrator specifies the login credentials and clicks the Login button. This method verifies the login credentials and enables the administrator to log on to the application.
Figure 4-4 shows the output of Listing 4-3:
The ExecutiveManager class defines the Executive Manager window. This window enables the administrator to add information about the new executive to the database.
Listing 4-4 shows the code for the frmExecutiveManager.vb file that defines the ExecutiveManager class:
Imports System.Data.SqlClient Imports System.IO Imports System.Collections Public Class ExecutiveManager Inherits System.Windows.Forms.Form Dim sqlCon As New SqlConnection Dim sqlCom As New SqlCommand Dim dataReader As SqlDataReader Dim strSQLConnection As String Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click 'To check whether the required parameters are specified by the end user or not 'Checking for First Name If txtFirstName.Text = "" Then MsgBox("Please enter first name of the executive", MsgBoxStyle.OKOnly, "ENTER FIRST NAME") txtFirstName.Focus() Exit Sub End If 'Checking for Last Name If txtLastName.Text = "" Then MsgBox("Please enter last name of the executive", MsgBoxStyle.OKOnly, "ENTER LAST NAME") txtLastName.Focus() Exit Sub End If 'Checking for Address If txtAddress.Text = "" Then MsgBox("Please enter address of the executive", MsgBoxStyle.OKOnly, "ENTER ADDRESS") txtAddress.Focus() Exit Sub End If 'Checking for Job Title If txtJobTitle.Text = "" Then MsgBox("Please enter job title of the executive", MsgBoxStyle.OKOnly, "ENTER JOB TITLE") txtJobTitle.Focus() Exit Sub End If 'Checking for Password If txtPassWord.Text = "" Then MsgBox("Please enter password for the executive", MsgBoxStyle.OKOnly, "ENTER PASSWORD") txtPassWord.Focus() Exit Sub End If 'If no department exists If ListDept.SelectedItems.Count = 0 Then MsgBox("Please select a department for the employee", MsgBoxStyle.OKOnly, "SELECT DEPARTMENT") ListDept.Focus() Exit Sub End If 'Inserting executive information into database Try sqlCom.CommandText = "insert into CustSupportExecutives values('" & _ txtFirstName.Text & "','" & txtLastName.Text & "','" & _ txtAddress.Text & "','" & txtJobTitle.Text & "','" & _ txtPassWord.Text & "')" sqlCom.ExecuteNonQuery() Catch ex As Exception MsgBox("Error in inserting new executive information", MsgBoxStyle.OKOnly, "INSERTION ERROR") Exit Sub End Try 'Getting the Department Id of the department selected for the executive Dim DeptId As Integer Try sqlCom.CommandText = "Select DId from departments where name='" & _ ListDept.SelectedItem & "'" dataReader = sqlCom.ExecuteReader() If dataReader.HasRows Then While dataReader.Read DeptId = dataReader.GetValue(0) End While End If dataReader.Close() Catch ex As Exception Exit Sub End Try 'Getting the Executive Id of the newly added executive Dim ExecId As Integer Try sqlCom.CommandText = "Select EId from CustSupportExecutives where firstname='" & _ txtFirstName.Text & "' and lastname='" & txtLastName.Text & _ "' and pwd='" & txtPassWord.Text & "'" dataReader = sqlCom.ExecuteReader() If dataReader.HasRows Then While dataReader.Read ExecId = dataReader.GetValue(0) End While End If dataReader.Close() Catch ex As Exception Exit Sub End Try 'Inserting information of executive's department Try sqlCom.CommandText = "insert into ExecDept values(" & ExecId & "," & DeptId & ")" sqlCom.ExecuteNonQuery() Catch ex As Exception MsgBox("Error in inserting executive's department information", MsgBoxStyle.OKOnly, "INSERTION ERROR") Exit Sub End Try 'Clear the fields txtFirstName.Text = "" txtLastName.Text = "" txtAddress.Text = "" txtJobTitle.Text = "" txtPassWord.Text = "" txtFirstName.Focus() End Sub Private Sub ExecutiveManager_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Try to establish SQL connection to database QueryTracker 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") strSQLConnection = sr.ReadLine sr.Close() Catch ex As Exception strSQLConnection = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=QueryTracker" End Try sqlCon.ConnectionString = strSQLConnection sqlCon.Open() Catch ex As Exception MsgBox("Error in establishing connection", MsgBoxStyle.OKOnly, "CONNECTION ERROR") cmdAdd.Enabled = False Exit Sub End Try Try 'Retrieving departments from the database sqlCom.CommandText = "select * from departments" sqlCom.Connection = sqlCon dataReader = sqlCom.ExecuteReader If dataReader.HasRows Then 'Populating ListDept with departments fetched by the datareader While dataReader.Read ListDept.Items.Add(dataReader.GetValue(1)) End While Else MsgBox("First enter the department details in the database table departments", _ MsgBoxStyle.OKOnly, "NO DEPARTMENT FOUND") cmdAdd.Enabled = False Exit Sub End If dataReader.Close() Catch ex As Exception MsgBox("Error in retrieving departments", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") cmdAdd.Enabled = False Exit Sub End Try End Sub End Class
Download this Listing .
The above listing defines the following methods:
ExecutiveManager_Load() : Establishes a connection with the QueryTracker database through an SQLConnection object. This method then retrieves the available departments and loads the ListDept list box control with the retrieved departments.
cmdAdd_Click() : Executes when the administrator specifies the information for the executive and clicks the Add New Executive button. This method checks for the validity of the specified information and if the information is valid, the code adds information about the new executive into the CustSupportExecutives table.
Figure 4-5 shows the output of Listing 4-4:
The QueryManager class defines the Query Manager window. This window enables the administrator to specify new queries and related information.
Listing 4-5 shows the code for the frmQueryManager.vb file that defines the QueryManager class:
Imports System.Data.SqlClient Imports System.IO Public Class QueryManager Inherits System.Windows.Forms.Form Dim sqlCon As New SqlConnection Dim sqlCom As New SqlCommand Dim dataReader As SqlDataReader Dim strSQLConnection As String Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click 'Verifying whether the required parameters are specified by the end user 'Checking for the Query Title If txtCompTitle.Text = "" Then MsgBox("Please enter title of the Query", MsgBoxStyle.OKOnly, "ENTER QUERY TITLE") txtCompTitle.Focus() Exit Sub End If 'Checking for the Query Description If txtCompDesc.Text = "" Then MsgBox("Please enter description of the Query", MsgBoxStyle.OKOnly, "ENTER QUERY DESCRIPTION") txtCompDesc.Focus() Exit Sub End If 'Checking for the Customer Information who registered the query If txtCustInfo.Text = "" Then MsgBox("Please enter customer information", MsgBoxStyle.OKOnly, "ENTER CUSTOMER INFORMATION") txtCustInfo.Focus() Exit Sub End If 'Inserting query information into database Try sqlCom.CommandText = "insert into Queries values('" & txtCompTitle.Text & _ "','" & txtCompDesc.Text & "','" & _ selQueryDate.Value.ToShortDateString & "','" & txtCustInfo.Text & "','pending',NULL)" sqlCom.Connection = sqlCon sqlCom.ExecuteNonQuery() Catch ex As Exception MsgBox("Error in inserting query information", MsgBoxStyle.OKOnly, "INSERTION ERROR") Exit Sub End Try 'Clear the fields txtCompTitle.Text = "" txtCompDesc.Text = "" txtCustInfo.Text = "" txtCompTitle.Focus() End Sub Private Sub QueryManager_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Try to establish SQL connection to database QueryTracker through SQL Connection object Try Try 'Read connection string from a text file settings.txt located in /bin folder of the application Dim sr As New StreamReader("Settings.txt") strSQLConnection = sr.ReadLine sr.Close() Catch ex As Exception 'If file settings.txt not found strSQLConnection = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=QueryTracker" End Try sqlCon.ConnectionString = strSQLConnection sqlCon.Open() Catch ex As Exception MsgBox("Error in establishing connection", MsgBoxStyle.OKOnly, "CONNECTION ERROR") cmdAdd.Enabled = False Exit Sub End Try 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:
QueryManager_Load() : Establishes a connection with the database through an SQLConnection object.
cmdAdd_Click() : Executes when the administrator specifies the information for the query such as the query description, query title, and customer name, and clicks the Add Query button. This method checks for the validity of the specified information and if the information is valid, the code adds the query information into the Queries table.
Figure 4-6 shows the output of Listing 4-5:
The ForwardQueryToExecutive class defines the Forward Pending Query to Executive window. This window enables the administrator to forward pending queries submitted by customers to customer support executives.
Listing 4-6 shows the code for the frmForwardQueryToExecutive.vb file that defines the ForwardQueryToExecutive class:
Imports System.Data.SqlClient Imports System.IO Imports System.Collections Public Class ForwardQueryToExecutive Inherits System.Windows.Forms.Form Dim sqlCon As New SqlConnection Dim sqlCom As New SqlCommand Dim dataReader As SqlDataReader Dim tempListViewItem As ListViewItem Dim strSQLConnection As String Private Sub ForwardQueryToExecutive_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Try to establish SQL connection to database QueryTracker through SQL Connection object Try Try 'Read the connection string from a text file settings.txt located in /bin folder of the application Dim sr As New StreamReader("Settings.txt") strSQLConnection = sr.ReadLine sr.Close() Catch ex As Exception strSQLConnection = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=QueryTracker" End Try sqlCon.ConnectionString = strSQLConnection sqlCon.Open() Catch ex As Exception MsgBox("Error in establishing connection", MsgBoxStyle.OKOnly, "CONNECTION ERROR") cmdForward.Enabled = False Exit Sub End Try Try 'Retrieving pending Queries information sqlCom.CommandText = "select QueryId, QueryTitle, QueryDesc, QueryDate from Queries where status='pending'" sqlCom.Connection = sqlCon dataReader = sqlCom.ExecuteReader If dataReader.HasRows Then While dataReader.Read 'Populating ListViewQuery with Query information fetched by the datareader tempListViewItem = ListViewQuery.Items.Add(dataReader.GetValue(0)) tempListViewItem.SubItems.Add(dataReader.GetValue(1)) tempListViewItem.SubItems.Add(dataReader.GetValue(2)) tempListViewItem.SubItems.Add(dataReader.GetValue(3)) End While Else 'No queries found pending cmdForward.Enabled = False Exit Sub End If dataReader.Close() Catch ex As Exception MsgBox("Error in retrieving queries", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") cmdForward.Enabled = False Exit Sub End Try Try 'Retrieving executives from database sqlCom.CommandText = "select EId, FirstName, LastName, Title from CustSupportExecutives" Dim adapter As New SqlDataAdapter(sqlCom) Dim ds As New DataSet 'Filling dataset with the retrieved executives adapter.Fill(ds) 'A variable to iterate through the data set Dim cnt As Integer Dim deptds As New DataSet If ds.Tables(0).Rows.Count > 0 Then For cnt = 0 To ds.Tables(0).Rows.Count - 1 'Populating ListViewExec with Executives related information fetched by the dataset tempListViewItem = ListViewExec.Items.Add(ds.Tables(0).Rows(cnt).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(1)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(2)) sqlCom.CommandText = "Select Name from departments where DId= _ (select DId from ExecDept where EId=" & ds.Tables(0).Rows(cnt).Item(0) & ")" adapter.SelectCommand = sqlCom adapter.Fill(deptds) tempListViewItem.SubItems.Add(deptds.Tables(0).Rows(0).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(3)) Next Else 'No Executives found cmdForward.Enabled = False Exit Sub End If Catch ex As Exception MsgBox("Error in retrieving executives", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") cmdForward.Enabled = False Exit Sub End Try End Sub Private Sub cmdForward_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdForward.Click 'Checking whether the end user has selected any pending Query to be forwarded If ListViewQuery.SelectedItems.Count = 0 Then MsgBox("Please select the query to be forwarded", MsgBoxStyle.OKOnly, "SELECT QUERY") Exit Sub End If 'Checking whether the end user has selected an Executive to whom the selected Query is to be forwarded If ListViewExec.SelectedItems.Count = 0 Then MsgBox("Please select the executive whom the query is to be forwarded", _ MsgBoxStyle.OKOnly, "SELECT EXECUTIVE") Exit Sub End If Try 'Inserting the Executive-Query related information sqlCom.CommandText = "insert into ExecQuery values(" & _ ListViewExec.SelectedItems(0).Text & " , " & _ ListViewQuery.SelectedItems(0).Text & ")" sqlCom.ExecuteNonQuery() 'Updating the status of the selected Query sqlCom.CommandText = "update Queries set status='forwarded' where QueryId=" & _ ListViewQuery.SelectedItems(0).Text & "" sqlCom.ExecuteNonQuery() 'Removing the forwarded Query from the ListViewQuery ListViewQuery.Items.RemoveAt(ListViewQuery.SelectedItems(0).Index) Catch ex As Exception MsgBox("Error in forwarding query to executive", MsgBoxStyle.OKOnly, "INSERTION ERROR") End Try 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:
ForwardQueryToExecutive_Load() : Establishes a connection to the QueryTracker database and retrieves information regarding pending queries. It then displays the result in the ListViewQuery list view control. This method also retrieves information regarding executives and displays the results in the ListViewExec list view control.
cmdForward_Click() : Executes when the administrator selects a pending query from the pending queries list, selects a particular executive to resolve the query, and clicks the Forward button. This method forwards the query to the selected executive, updates the status of the query, and removes the query from the pending queries list.
Figure 4-7 shows the output of Listing 4-6:
The ViewStatus class defines the View Query Status window. This window enables the administrator to view the status of queries and provides the flexibility to filter the information based on department, executive, and date.
Listing 4-7 shows the code for the frmViewStatus.vb file that defines the ViewStatus class:
Imports System.IO Imports System.Data.SqlClient Public Class ViewStatus Inherits System.Windows.Forms.Form Dim sqlCon As New SqlConnection Dim sqlCom As New SqlCommand Dim dataReader As SqlDataReader Dim strSQLConnection As String Dim tempListViewItem As ListViewItem Dim boolPending As Boolean Dim boolForwarded As Boolean Dim boolAttended As Boolean Private Sub ViewStatus_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load Try Try 'Read the connection string from a text file settings.txt located in '/bin folder of the application Dim sr As New StreamReader("Settings.txt") strSQLConnection = sr.ReadLine sr.Close() Catch ex As Exception 'If file settings.txt not found strSQLConnection = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=QueryTracker" End Try sqlCon.ConnectionString = strSQLConnection sqlCon.Open() Catch ex As Exception MsgBox("Error in establishing connection", MsgBoxStyle.OKOnly, "CONNECTION ERROR") Exit Sub End Try showPending() boolPending = True End Sub 'This method is used to show pending Queries which are not assigned to any Executive yet. Public Sub showPending() Try 'Retrieving Information of the pending Queries sqlCom.CommandText = _ "select QueryId, QueryTitle, QueryDate, CompDate from Queries where status='pending'" sqlCom.Connection = sqlCon Dim ds As New DataSet Dim execds As New DataSet Dim adapter As New SqlDataAdapter(sqlCom) 'Filling dataset with the Query Information adapter.Fill(ds) Dim cnt As Integer ListViewQuery.Items.Clear() If ds.Tables(0).Rows.Count > 0 Then For cnt = 0 To ds.Tables(0).Rows.Count - 1 'Population ListViewQuery with the information fetched by the dataset tempListViewItem = ListViewQuery.Items.Add(ds.Tables(0).Rows(cnt).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(1)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(2)) tempListViewItem.SubItems.Add("pending") Next End If Catch ex As Exception MsgBox("Error in retrieving pending queries", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit Sub End Try End Sub 'This method is used to show Queries which forwarded to Executives. Public Sub showForwarded() Try 'Retrieving Information of the forwarded Queries sqlCom.CommandText = _ "select QueryId,QueryTitle,QueryDate,CompDate from Queries where status='forwarded'" Dim ds As New DataSet Dim execds As New DataSet Dim adapter As New SqlDataAdapter(sqlCom) adapter.Fill(ds) Dim cnt As Integer ListViewQuery.Items.Clear() If ds.Tables(0).Rows.Count > 0 Then For cnt = 0 To ds.Tables(0).Rows.Count - 1 'Population ListViewQuery with the information fetched by the dataset tempListViewItem = ListViewQuery.Items.Add(ds.Tables(0).Rows(cnt).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(1)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(2)) tempListViewItem.SubItems.Add("forwarded") 'Fetching the Executive Name to whom the Query is forwared sqlCom.CommandText = _ "Select FirstName,LastName from CustSupportExecutives where EId= _ (Select EId from ExecQuery where QueryId=" & ds.Tables(0).Rows(cnt).Item(0) & ")" adapter.SelectCommand = sqlCom adapter.Fill(execds) 'Adding Executive Name tempListViewItem.SubItems.Add(execds.Tables(0).Rows(0).Item(0) & _ " " & execds.Tables(0).Rows(0).Item(1)) execds.Clear() Next End If Catch ex As Exception MsgBox("Error in retrieving forwarded queries", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit Sub End Try End Sub 'This method is used to show Queries which attended by the respective Executives. Public Sub showAttended() Try 'Retrieving Information of the attended Queries sqlCom.CommandText = "select QueryId,QueryTitle,QueryDate,CompDate from Queries where status='attended'" Dim ds As New DataSet Dim execds As New DataSet Dim adapter As New SqlDataAdapter(sqlCom) adapter.Fill(ds) Dim cnt As Integer ListViewQuery.Items.Clear() If ds.Tables(0).Rows.Count > 0 Then For cnt = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = ListViewQuery.Items.Add(ds.Tables(0).Rows(cnt).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(1)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(2)) tempListViewItem.SubItems.Add("attended") sqlCom.CommandText = _ "Select FirstName,LastName from CustSupportExecutives where EId= _ (Select EId from ExecQuery where QueryId=" & ds.Tables(0).Rows(cnt).Item(0) & ")" adapter.SelectCommand = sqlCom adapter.Fill(execds) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(0).Item(0) & _ " " & execds.Tables(0).Rows(0).Item(1)) If ds.Tables(0).Rows(cnt).IsNull(3) = True Then tempListViewItem.SubItems.Add("") Else 'Adding Query Attended On Date tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(3)) End If execds.Clear() Next End If Catch ex As Exception MsgBox("Error in retrieving attended queries", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit Sub End Try End Sub Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click boolPending = True boolForwarded = False boolAttended = False showPending() End Sub Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click boolPending = False boolForwarded = True boolAttended = False showForwarded() End Sub Private Sub MenuItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem4.Click boolPending = False boolForwarded = False boolAttended = True showAttended() End Sub Private Sub ViewNoFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ViewNoFilter.Click Label1.Visible = False ListFilter.Visible = False cmdShow.Visible = False End Sub Private Sub ViewFilterExec_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ViewFilterExec.Click Label1.Visible = True Label1.Text = "Select Executive" ListFilter.Visible = True cmdShow.Visible = True showExec() End Sub 'This method is used to retrieve Executives for filtering purpose Public Sub showExec() Try sqlCom.CommandText = "select FirstName,LastName from CustSupportExecutives" sqlCom.Connection = sqlCon dataReader = sqlCom.ExecuteReader ListFilter.Items.Clear() If dataReader.HasRows Then While dataReader.Read ListFilter.Items.Add(dataReader.GetValue(0) & " " & dataReader.GetValue(1)) End While End If dataReader.Close() Catch ex As Exception MsgBox("Error in retrieving executives", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Label1.Visible = False ListFilter.Visible = False cmdShow.Visible = False Exit Sub End Try End Sub 'This method is used to retrieve Departments for filtering purpose Public Sub ShowDept() Try sqlCom.CommandText = "select * from departments" dataReader = sqlCom.ExecuteReader ListFilter.Items.Clear() If dataReader.HasRows Then While dataReader.Read ListFilter.Items.Add(dataReader.GetValue(1)) End While End If dataReader.Close() Catch ex As Exception MsgBox("Error in retrieving departments", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Label1.Visible = False ListFilter.Visible = False cmdShow.Visible = False End Try End Sub 'This method is used to retrieve Dates for filtering purpose Public Sub ShowDate() Try sqlCom.CommandText = "select distinct QueryDate from Queries" dataReader = sqlCom.ExecuteReader ListFilter.Items.Clear() If dataReader.HasRows Then While dataReader.Read ListFilter.Items.Add(dataReader.GetValue(0)) End While End If dataReader.Close() Catch ex As Exception MsgBox("Error in retrieving query dates", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Label1.Visible = False ListFilter.Visible = False cmdShow.Visible = False End Try End Sub Private Sub ViewFilterDept_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ViewFilterDept.Click Label1.Visible = True Label1.Text = "Select Department" ListFilter.Visible = True cmdShow.Visible = True ShowDept() End Sub Private Sub ViewFilterDate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ViewFilterDate.Click Label1.Visible = True Label1.Text = "Select Date" ListFilter.Visible = True cmdShow.Visible = True ShowDate() End Sub Private Sub cmdShow_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdShow.Click 'Check whether there are Queries for filtering If ListViewQuery.Items.Count = 0 Then MsgBox("No queries to be filter", MsgBoxStyle.OKOnly) Exit Sub End If Dim adapter As New SqlDataAdapter Dim ds As New DataSet Dim cnt As Integer Dim execds As New DataSet Dim count As Integer 'If the end user chooses Filter by Department If Label1.Text = "Select Department" Then 'If pending Queries Information is displayed in the ListViewQuery If boolPending = True Then MsgBox("Pending queries are not assigned to any executive", MsgBoxStyle.OKOnly, "PLEASE CHECK") Exit Sub End If 'If any department is selected If ListFilter.SelectedItems.Count > 0 Then Dim DeptId As Integer Dim ExecId As Integer Try 'Retrieve the Dept Id of the department selected sqlCom.CommandText = "select DId from departments where name='" & _ ListFilter.SelectedItem & "'" adapter.SelectCommand = sqlCom adapter.Fill(ds) If ds.Tables(0).Rows.Count > 0 Then DeptId = ds.Tables(0).Rows(0).Item(0) End If ds.Clear() Catch ex As Exception MsgBox("Error retrieving department information", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit Sub End Try Try 'Retrieve information of all the Executives of the selected Department sqlCom.CommandText = _ "select EId,FirstName,LastName from CustSupportExecutives where EId in _ (select EId from ExecDept where DId=" & DeptId & ")" adapter.SelectCommand = sqlCom adapter.Fill(ds) Catch ex As Exception MsgBox("Error retrieving executives information", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit Sub End Try ListViewQuery.Items.Clear() For cnt = 0 To ds.Tables(0).Rows.Count - 1 'If forward Queries Information is displayed If boolForwarded = True Then Try 'Retrieve Query Information of Queries which are forwarded to the Executives of the selected Department sqlCom.CommandText = _ "select QueryId,QueryTitle,QueryDate,CompDate from Queries where status= _ 'forwarded' and QueryId in (select QueryId from ExecQuery where EId=" & _ ds.Tables(0).Rows(cnt).Item(1) & ")" adapter.SelectCommand = sqlCom adapter.Fill(execds) If execds.Tables(0).Rows.Count > 0 Then For count = 0 To execds.Tables(0).Rows.Count - 1 tempListViewItem = _ ListViewQuery.Items.Add(execds.Tables(0).Rows(count).Item(0)) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(1)) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(2)) tempListViewItem.SubItems.Add("forwarded") tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(2) & _ " " & ds.Tables(0).Rows(cnt).Item(3)) Next End If execds.Clear() Catch ex As Exception MsgBox("Error in retrieving forwarded queries", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit For End Try End If 'If attended Queries are displayed If boolAttended = True Then Try 'Retrieve Query Information of Queries which are attended by the 'Executives of the selected Department sqlCom.CommandText = _ "select QueryId,QueryTitle,QueryDate,CompDate from Queries where status='attended' _ and QueryId in (select QueryId from ExecQuery where EId=" & _ ds.Tables(0).Rows(cnt).Item(1) & ")" adapter.SelectCommand = sqlCom adapter.Fill(execds) If execds.Tables(0).Rows.Count > 0 Then For count = 0 To execds.Tables(0).Rows.Count 1 tempListViewItem = _ ListViewQuery.Items.Add(execds.Tables(0).Rows(count).Item(0)) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(1)) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(2)) tempListViewItem.SubItems.Add("attended") tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(2) & _ " " & ds.Tables(0).Rows(cnt).Item(3)) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(3)) Next End If execds.Clear() Catch ex As Exception MsgBox("Error in retrieving attended queries ", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit For End Try End If Next ds.Clear() Else MsgBox("First select a department", MsgBoxStyle.OKOnly, "SELECT DEPARTMENT") End If 'If the end user selects the filtering criteria as Filter By Executive ElseIf Label1.Text = "Select Executive" Then If boolPending = True Then MsgBox("Pending queries are not assigned to any executive", MsgBoxStyle.OKOnly, "PLEASE CHECK") Exit Sub End If 'To check whether an Executive is selected If ListFilter.SelectedItems.Count > 0 Then Dim strTempName As String Dim strTempNameSplit(2) As String Dim strTempFirstName As String Dim strTempLastName As String strTempName = ListFilter.SelectedItem 'Break the name of the selected EXecutive into First Name and Last Name strTempNameSplit = strTempName.Split(" "c) strTempFirstName = strTempNameSplit(0) strTempLastName = strTempNameSplit(1) Try sqlCom.CommandText = "select EId from CustSupportExecutives where FirstName='" & _ strTempFirstName & "' and LastName='" & strTempLastName & "'" adapter.SelectCommand = sqlCom adapter.Fill(ds) Catch ex As Exception MsgBox("Error retrieving executives information", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit Sub End Try ListViewQuery.Items.Clear() If boolForwarded = True Then Try 'Retrieve Query Information of Queries which are forwarded to the selected Executive sqlCom.CommandText = _ "select QueryId, QueryTitle, QueryDate, CompDate from Queries where _ status='forwarded' and QueryId in (select QueryId from ExecQuery where EId=" _ & ds.Tables(0).Rows(0).Item(0) & ")" adapter.SelectCommand = sqlCom adapter.Fill(execds) If execds.Tables(0).Rows.Count > 0 Then For count = 0 To execds.Tables(0).Rows.Count - 1 tempListViewItem = ListViewQuery.Items.Add(execds.Tables(0).Rows(count).Item(0)) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(1)) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(2) _ tempListViewItem.SubItems.Add("forwarded") tempListViewItem.SubItems.Add(ListFilter.SelectedItem) Next End If execds.Tables.Clear() Catch ex As Exception MsgBox("Error in retrieving forwarded queries", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") End Try End If If boolAttended = True Then Try 'Retrieve Query Information of Queries which are attended by the selected Executive. sqlCom.CommandText = "select QueryId, QueryTitle, QueryDate, CompDate from Queries where _ status='attended' and QueryId in (select QueryId from ExecQuery where EId=" & _ ds.Tables(0).Rows(0).Item(0) & ")" adapter.SelectCommand = sqlCom adapter.Fill(execds) If execds.Tables(0).Rows.Count > 0 Then For count = 0 To execds.Tables(0).Rows.Count - 1 tempListViewItem = ListViewQuery.Items.Add(execds.Tables(0).Rows(count).Item(0)) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(1)) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(2)) tempListViewItem.SubItems.Add("attended") tempListViewItem.SubItems.Add(ListFilter.SelectedItem) 'Adding Query Attended On Date tempListViewItem.SubItems.Add(execds.Tables(0).Rows(count).Item(3)) Next End If execds.Clear() Catch ex As Exception MsgBox("Error in retrieving attended queries ", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") End Try End If ds.Clear() Else MsgBox("First select an executive", MsgBoxStyle.OKOnly, "SELECT EXECUTIVE") End If Else 'The end user chooses Filter By Date option If boolPending = True Then Try 'Retrieve Query Information of pending Queries for a selected date sqlCom.CommandText = _ "select QueryId, QueryTitle, QueryDate, CompDate from Queries where status='pending' and QueryDate='" _ & ListFilter.SelectedItem & "'" adapter.SelectCommand = sqlCom adapter.Fill(ds) ListViewQuery.Items.Clear() ds.Tables(0).Rows.Count > 0 Then For cnt = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = ListViewQuery.Items.Add(ds.Tables(0).Rows(cnt).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(1)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(2)) tempListViewItem.SubItems.Add("pending") Next End If Catch ex As Exception MsgBox("Error in retrieving pending queries", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit Sub End Try ds.Clear() ElseIf boolForwarded = True Then Try 'Retrieve Query Information of forwarded Queries for a selected date sqlCom.CommandText = _ "select QueryId,QueryTitle,QueryDate,CompDate from Queries where status='forwarded' and QueryDate='" _ & ListFilter.SelectedItem & "'" adapter.SelectCommand = dsqlCom adapter.Fill(ds) ListViewQuery.Items.Clear() If ds.Tables(0).Rows.Count > 0 Then For cnt = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = ListViewQuery.Items.Add(ds.Tables(0).Rows(cnt).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(1)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(2)) tempListViewItem.SubItems.Add("forwarded") sqlCom.CommandText = _ "Select FirstName,LastName from CustSupportExecutives where EId= _ (Select EId from ExecQuery where QueryId=" _ & ds.Tables(0).Rows(cnt).Item(0) & ")" adapter.SelectCommand = sqlCom adapter.Fill(execds) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(0).Item(0) & _ " " & execds.Tables(0).Rows(0).Item(1)) execds.Clear() Next End If Catch ex As Exception MsgBox("Error in retrieving forwarded queries", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit Sub End Try ds.Clear() Else Try 'Retrieve Query Information of attended Queries for a selected date sqlCom.CommandText = _ "select QueryId, QueryTitle, QueryDate, CompDate from Queries where status='attended' and QueryDate='" _ & ListFilter.SelectedItem & "'" adapter.SelectCommand = sqlCom adapter.Fill(ds) ListViewQuery.Items.Clear() If ds.Tables(0).Rows.Count > 0 Then For cnt = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = ListViewQuery.Items.Add(ds.Tables(0).Rows(cnt).Item(0)) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(1) tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(2)) tempListViewItem.SubItems.Add("attended") sqlCom.CommandText = "Select FirstName,LastName from CustSupportExecutives where EId= _ (Select EId from ExecQuery where QueryId=" & ds.Tables(0).Rows(cnt).Item(0) & ")" adapter.SelectCommand = sqlCom adapter.Fill(execds) tempListViewItem.SubItems.Add(execds.Tables(0).Rows(0).Item(0) & _ " " & execds.Tables(0).Rows(0).Item(1)) 'Check whether the Query Attended On Date is not null If ds.Tables(0).Rows(cnt).IsNull(3) = True Then tempListViewItem.SubItems.Add("") Else tempListViewItem.SubItems.Add(ds.Tables(0).Rows(cnt).Item(3)) End If execds.Clear() Next End If Catch ex As Exception MsgBox("Error in retrieving attended queries", MsgBoxStyle.OKOnly, "RETRIEVAL ERROR") Exit Sub End Try End If End If End Sub End Class
Download this Listing .
The above listing defines the following methods:
ViewStatus_Load() : Retrieves pending queries and loads their information in the ListViewQuery list view control.
MenuItem3_Click() : Executes when the administrator selects the View Options->Forwarded Queries menu option. This method calls the showForwarded() method that retrieves information about the queries that have been forwarded to executives.
MenuItem4_Click() : Executes when the administrator selects the View Options->Attended Queries menu option. This method calls the showAttended() method that retrieves information about the queries attended to by executives.
Figure 4-8 shows the output of Listing 4-7: