Creating the QueryTrackerAdministrator Application


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 .

Creating the Application Manager Window

The frmApplicationManager class defines the Application Manager window.

Listing 4-2 shows the code for the frmApplicationManager.vb file that defines the frmApplicationManager class:

Listing 4-2: The frmApplicationManager.vb File
start example
 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 
end example
 

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:

click to expand: this figure shows the application manager window that provides options for the administrator to navigate through the application.
Figure 4-3: The Application Manager Window in Design View

Creating the Login Manager Window

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:

Listing 4-3: The frmLoginManager.vb File
start example
 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 
end example
 

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:

this figure shows the login manager window where an end user can specify the user name and password and log on to the application.
Figure 4-4: The Login Manager Window

Creating the Executive Manager Window

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:

Listing 4-4: The frmExecutiveManager.vb File
start example
 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 
end example
 

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:

this figure shows the executive manager window, where the administrator can add information about a new executive.
Figure 4-5: The Executive Manager Window

Creating the Query Manager Window

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:

Listing 4-5: The frmQueryManager.vb File
start example
 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 
end example
 

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:

click to expand: this figure shows the query manager window where the administrator can enter customer queries.
Figure 4-6: The Query Manager Window in Design View

Creating the Forward Pending Query to Executive Window

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:

Listing 4-6: The frmForwardQueryToExecutive.vb File
start example
 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 
end example
 

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:

click to expand: this figure shows the forward pending query to executive window that enables the administrator to assign pending queries to executives.
Figure 4-7: The Forward Pending Query to Executive Window

Creating the View Query Status Window

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:

Listing 4-7: The frmViewStatus.vb File
start example
 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 
end example
 

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:

click to expand: this figure shows the view query status window, which consists of the view options menu. the administrator can view the status of queries submitted by customers using this window.
Figure 4-8: The View Query Status Window



NET InstantCode. UML with Visio and Visual Studio .NET
NET InstantCode. UML with Visio and Visual Studio .NET
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 49

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net