Option Strict Off
Imports System
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Drawing

Public Class YouthJob
    Private dtApplicants As DataTable
    Private cmApplicants As CurrencyManager
    Private dtJobs As DataTable
    Private dvjobs As New DataView
    Private WithEvents cmJobs As CurrencyManager

    Private Sub ApplicantsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplicantsBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.ApplicantsBindingSource.EndEdit()
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
    End Sub

    Private Sub YouthJob_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'SYEP2007DataSet.JobTitles' table. You can move, or remove it, as needed.
        'Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        'Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Try
            Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try
        Try
            Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try

        dtApplicants = Me.SYEP2007DataSet.Applicants
        dtJobs = Me.SYEP2007DataSet.JobTitles

        'filltables() 'equivalent to filling tables with ...
        '... a dataadapter or a tableadapter

        'get and display the count of applicant records
        'Dim count As Integer = dtApplicants.Rows.Count 'So I can filter the count I added a Stored Procedure and us it instead _
        '                                                 of dtApplicants.Rows.Count.
        Dim count As Integer = CType(Me.ApplicantsTableAdapter.spCount_Applicants2008Total, Integer)
        txtTotalApplicants.Text = String.Format("{0} Applicants", count)

        'bind the applicants table to the datagridview
        ApplicantsDataGridView.DataSource = dtApplicants
        'set up the currencymanager - convenient for referring ...
        '... to the currently selected row in the applicants grid
        cmApplicants = CType(BindingContext(dtApplicants), CurrencyManager)

        'set up a dataview to filter the jobs table so it ...
        '... only shows those where some jobs remain available
        dvjobs.Table = dtJobs
        'dvjobs.RowFilter = "txtWorksiteJobTitlePositionsNumber > 0"         '<<<<<Moved to end of YouthJob_Load
        'bind the jobs table to the datagridview
        JobTitlesDataGridView.DataSource = dvjobs
        'set up the currency manager
        cmJobs = CType(BindingContext(dvjobs), CurrencyManager)

        'customise the grids - with a strongly typed dataset ...
        '... this could be done in the designer
        ApplicantsDataGridView.ReadOnly = False
        ApplicantsDataGridView.AllowUserToAddRows = False   'Was True.  I don't know why.
        'hide the applicant ID
        'ApplicantsDataGridView.Columns(0).Visible = True    'This is what whas keeping the columns from being hidden
        'hide the job ID ...
        'ApplicantsDataGridView.Columns(1).Visible = True    'This is what whas keeping the columns from being hidden
        'hide the ProgramID column in Applicants DGV
        'ApplicantsDataGridView.Columns(3).Visible = True    'This is what whas keeping the columns from being hidden
        '... but add an unbound column to show ...
        '... the job title matching that ID
        Dim TitleCol As New DataGridViewTextBoxColumn
        'TitleCol.HeaderText = "Job Title"

        'ApplicantsDataGridView.Columns(0).Width = 50  'AppID
        'ApplicantsDataGridView.Columns(1).Width = 50  'JobTitleID
        'ApplicantsDataGridView.Columns(2).Width = 100  'LastName
        'ApplicantsDataGridView.Columns(3).Width = 100  'FirstName
        'ApplicantsDataGridView.Columns(4).Width = 150  'Home City
        'ApplicantsDataGridView.Columns(5).Width = 60  'Zip
        'ApplicantsDataGridView.Columns(6).Width = 150  'City Preference
        'ApplicantsDataGridView.Columns(7).Width = 60  'Hours
        'ApplicantsDataGridView.Columns(8).Width = 50  'M-F
        ApplicantsDataGridView.Columns.Add("TitleCol", "Job Title Assigned")
        ApplicantsDataGridView.Columns("TitleCol").DisplayIndex = 7             'This places the JobTitle column on the GridView. It needs to do it this way to update
        'TitleCol.SortMode = DataGridViewColumnSortMode.Automatic        ' <-- This did not make the column sortable.  graye on EE said that it should work, though
        'ApplicantsDataGridView.Sort(ApplicantsDataGridView.Columns("TitleCol"), System.ComponentModel.ListSortDirection.Ascending) ' <---- Got an error with this

        '        For example:  <-- An example from graye on Experts Exchange of how he created a ComboBoxcolumn and made it sortable
        '            ' replace the Priority column with a combobox
        '            Dim Priority As New DataGridViewComboBoxColumn()
        '            Priority.DataSource = taPriority.GetActive
        '            Priority.DataPropertyName = "Priority"
        '            Priority.DisplayMember = "PriorityCode"
        '            Priority.Name = "Priority"
        '            Priority.HeaderText = "Pri"
        '            Priority.SortMode = DataGridViewColumnSortMode.Automatic
        '            dgvAssign.Columns.RemoveAt(13)
        '            dgvAssign.Columns.Insert(13, Priority)
        'This works for me....

        'ApplicantsDataGridView.Columns("TitleCol").Width = 250  'Job Title
        'ApplicantsDataGridView.Columns(10).Width = 200  'Work Preference
        'ApplicantsDataGridView.Columns(11).Width = 95  'Referred by
        'ApplicantsDataGridView.Columns(12).Width = 50  'Offender

        'Dim NumberofAdults As New DataGridViewTextBoxColumn  'Adding column this way did not show any data
        'NumberofAdults.HeaderText = "Adults"                 'Adding it in the designer shows the data
        'ApplicantsDataGridView.Columns.Add(NumberofAdults)   'The above Titlecol column goes on the end

        JobTitlesDataGridView.ReadOnly = True
        JobTitlesDataGridView.AllowUserToAddRows = False
        'JobTitlesDataGridView.Columns(0).Visible = False  'Hide JobTitleID


        'run the sub to fill the jobs numbers displays  ' ------------------Check to see if this needs to e here or just in the Update button-----
        updateTotals()

        'get the job titles for the applicants
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next

        dvjobs.RowFilter = "txtWorksiteJobTitlePositionsNumber > 0"   '<<<<<Moved to end of YouthJob_Load from above.

        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString  'The TableAdapter did have: "WHERE (ddlActivityType like '%SYEP%') AND (keyApplicantID > 200900000)" now it's: Status = '1'
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountWIAActivity().ToString 'No STEPS program for SYEP 2008
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString

        'This just pops the current deployment version number into that label in the top right corner e.g. v1.0.0.51
        If System.Deployment.Application _
        .ApplicationDeployment.IsNetworkDeployed Then
            lblVersion.Text = "v" & System.Deployment.Application _
            .ApplicationDeployment.CurrentDeployment.CurrentVersion.ToString
        Else
            lblVersion.Text = "v" & My.Application.Info.Version.ToString
        End If

    End Sub
    Private Sub YouthJob_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)

    End Sub

    ''This function is to get the currently selected row of JobTitlesDataGridView in order to return to the selected row after an assignment update.
    'Public Function getDGindex(ByVal ValueToLook As Integer) As Integer
    '    Dim dr As DataGridViewRow
    '    For Each dr In JobTitlesDataGridView.Rows 'DataGridView1.Rows
    '        If dr.Cells("JobTitleID").Value = ValueToLook Then
    '            Return dr.Index
    '        End If
    '    Next
    'End Function

    Function getJobTitleIndex(ByVal ID As Integer) As Integer
        For Each row As DataGridViewRow In Me.JobTitlesDataGridView.Rows
            If row.Cells(19).Value = ID Then
                Return row.Index
            End If
        Next
    End Function
    Function getApplicantIndex(ByVal ID As Integer) As Integer
        For Each row As DataGridViewRow In Me.ApplicantsDataGridView.Rows
            If row.Cells(5).Value = ID Then
                Return row.Index
            End If
        Next
    End Function

    Private Sub btnUpdatePosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdatePosition.Click
        ' ' -=== Assign Worksite to Youth Button ===-
        ' 'Call the function to get the currently selected row of JobTitlesDataGridView
        'Dim myPreviousValue As Long = Me.JobTitlesDataGridView.SelectedRows.Item(0).Cells(19).Value
        ' Stores the current row number
        ' Check if there's any selected row
        'If Not Me.JobTitlesDataGridView.SelectedRows Is Nothing Then 'CurrentRow Is Nothing Then
        If Me.JobTitlesDataGridView.SelectedRows.Count > 0 Then
            'If Not Me.ApplicantsDataGridView.SelectedRows Is Nothing Then 'CurrentRow Is Nothing Then
            If Me.ApplicantsDataGridView.SelectedRows.Count > 0 Then

                Dim SelectedID As Integer = Me.JobTitlesDataGridView.CurrentRow.Cells(19).Value
                Dim SelectedID2 As Integer = Me.ApplicantsDataGridView.CurrentRow.Cells(5).Value

                Me.Cursor = Cursors.WaitCursor
                Call UpdateAll()
                Call RefreshAll()
                Me.Cursor = Cursors.Default

                ' Set the old position
                Dim DGVindex As Integer = getJobTitleIndex(SelectedID)
                Me.JobTitlesDataGridView.ClearSelection()
                Me.JobTitlesDataGridView.Rows(DGVindex).Selected = True
                Me.JobTitlesDataGridView.FirstDisplayedScrollingRowIndex = DGVindex

                Dim DGVindex2 As Integer = getApplicantIndex(SelectedID2)
                Me.ApplicantsDataGridView.ClearSelection()
                Me.ApplicantsDataGridView.Rows(DGVindex2).Selected = True
                Me.ApplicantsDataGridView.FirstDisplayedScrollingRowIndex = DGVindex2

                Me.JobTitlesDataGridView.ClearSelection()
                Me.ApplicantsDataGridView.ClearSelection()

            Else
                Exit Sub
            End If
        End If
        ''Restore the previously selected row after an update.
        'Dim myRowIndex As Integer = getDGindex(myPreviousValue)
        'Me.JobTitlesDataGridView.Rows(myRowIndex).Selected = True
        'Me.JobTitlesDataGridView.FirstDisplayedScrollingRowIndex = myRowIndex

        ''Maybe put here UpdateTotals() then RefreshAll()  'Note for 20090312
        ''UpdateTotals() is already called in UpdateAll()


    End Sub
    Private Sub UpdateAll()
        Dim jobRow As DataRow()
        'use the currency manager to get the currently ...
        '... selected job in the jobs grid
        Dim jobsRow As DataRowView = CType(cmJobs.Current, DataRowView)
        'and save the job number
        Dim newJobNumber As Integer = CInt(jobsRow("keyJobTitleID"))
        'similar for the applicants
        Dim applicantsRow As DataRowView = CType(cmApplicants.Current, DataRowView)
        'check if this applicant already has a job
        If Not TypeOf (applicantsRow.Item("keyJobTitleID")) Is DBNull Then
            'if so, seek the relevant row from the datatable
            jobRow = dtJobs.Select("keyJobTitleID = " & Convert.ToString(applicantsRow.Item("keyJobTitleID")))
            'if there is one
            If jobRow.Length > 0 Then
                'it will be the first in the row array: update it
                Dim myVal As Integer = CInt(jobRow(0).Item("txtWorksiteJobTitlePositionsNumber"))
                myVal += 1 'or myVal = myVal + 1
                jobRow(0).Item("txtWorksiteJobTitlePositionsNumber") = myVal
            Else
                'if there isn't one ...
                MsgBox("Something wrong")
            End If
        End If
        'put the new job number in the applicant's record
        applicantsRow.Item("keyJobTitleID") = newJobNumber
        'decrement the number of jobs available
        'seek the relevant row from the datatable
        jobRow = dtJobs.Select("keyJobTitleID = " & newJobNumber)
        'if there is one
        If jobRow.Length > 0 Then
            'it will be the first in the row array: update it
            Dim myVal As Integer = CInt(jobRow(0).Item("txtWorksiteJobTitlePositionsNumber"))
            myVal -= 1 'or myVal = myVal - 1
            jobRow(0).Item("txtWorksiteJobTitlePositionsNumber") = myVal
        Else
            'if there isn't one ...
            MsgBox("Something wrong")
        End If

        ''commit the edits - this is necessary where ...
        ''... they have been done by via the currency ...
        ''... manager's Current record, rather than ...
        ''... (as above) in the datatable itself
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()

        'run the sub to update the jobs numbers displays
        Call updateTotals()

        'get the job title for this applicant
        Dim dgvr As DataGridViewRow = ApplicantsDataGridView.CurrentRow
        fillInTitle(dgvr)

        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)

    End Sub

    Private Sub btnCancelPosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelPosition.Click
        Me.Close()
    End Sub

    Private Sub cmJobs_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmJobs.PositionChanged
        'the jobs currency manager was declared WithEvents ...
        '... to make it easy to update the jobs numbers ...
        '... displays when a different job was selected ...
        '... in the grid
        If cmJobs.Count > 0 Then
            updateTotals()
        End If
    End Sub

    Private Sub updateTotals()
        If cmJobs.Count = 0 Then
            txtAvailable.Text = ""
            txtTaken.Text = ""
            txtTotal.Text = ""
            MsgBox("No more jobs")
            Exit Sub
        End If
        'use the currency manager to get the currently ...
        '... selected job in the jobs grid
        Dim jobsRow As DataRowView = CType(cmJobs.Current, DataRowView)
        'that says how many of that job are available
        Dim available As Integer = CInt(jobsRow.Item("txtWorksiteJobTitlePositionsNumber"))
        'get the job ID from that row
        Dim thisJob As Integer = CInt(jobsRow.Item("keyJobTitleID"))
        'use the job ID to find how many applicants ...
        '... already have that job
        Dim taken As Integer = CInt(dtApplicants.Compute("Count(keyJobTitleID)", "keyJobTitleID = " & thisJob))
        'the total is the sum of those two
        Dim total As Integer = available + taken
        'display the results
        txtAvailable.Text = available.ToString
        txtTaken.Text = taken.ToString
        txtTotal.Text = total.ToString

        ''update TextBoxes with Available, Taken and Total Job Positions
        ''ERROR:  There is already an open DataReader associated with this Command which must be closed first.
        txtPAvailable.Text = Me.JobTitlesTableAdapter.spSUM_JobTitles_PositionsAvailable().ToString
        txtPTaken.Text = Me.JobTitlesTableAdapter.spSUM_JobTitles_PositionsTaken().ToString
        txtPTotal.Text = Me.JobTitlesTableAdapter.spSUM_JobTitles_PositionsTotal().ToString

        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountWIAActivity().ToString   ' No STEPS Program for SYEP 2008
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString

        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
    End Sub

    Private Function getTitleFromID(ByVal ID As Integer) As String
        'Temporary line for debugging purposes
        'If that is reporting 0, then we know the problem is with the datatable.  
        'If it is reporting more than that, then the problem is with either the value 
        'that is being passed to the function, 
        'or with the syntax or references in the select statement.
        'Debug.WriteLine(dtJobs.Rows.Count)

        'use datatable filter to return only rows with this ID
        Dim dr As DataRow() = dtJobs.Select("keyJobTitleID = " & ID)
        'check if there are any such rowsI
        If dr.Length > 0 Then
            'if yes, there should only be one - in position 0
            'return the value in the JobName field from that
            '          Return CStr(dr(0)("txtWorksiteJobTitle"))
            Dim s As String = CStr(dr(0)("txtWorksiteJobTitle"))
            ' MsgBox("| s = " & s & "|")  'Here for troubleshooting
            Return s
        Else
            'otherwise, return empty string
            Return ""
        End If
    End Function

    Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
        'check if there is a value in the third cell ...
        '... (index 2, which is the "Job" field ...
        '... in this row from the datagridview
        If TypeOf dgvr.Cells("KeyJobTitleIDDataGridViewTextBoxColumn").Value Is DBNull Then
            'if not, then there is no Job Title
            dgvr.Cells("TitleCol").Value = ""
        Else
            'if there is, use it to get the Job Title
            'Debug.WriteLine("Cell 2 = " & CInt(dgvr.Cells(2).Value))  'I didn't see anything unusual in the debug window when this ran.
            'MsgBox("Cell 2 = " & CInt(dgvr.Cells(2).Value))  '  Here for troubleshooting.
            dgvr.Cells("TitleCol").Value = getTitleFromID(CInt(dgvr.Cells("KeyJobTitleIDDataGridViewTextBoxColumn").Value))
            'Debug.WriteLine("Cell 4 = '" & CInt(dgvr.Cells(4).Value) & "'")
            'MsgBox("Cell 4 = '" & CStr(dgvr.Cells(4).Value) & "'")   'Here for troubleshooting.
            'MsgBox("Cell 4 = '" & CStr(dgvr.Cells("TitleCol").Value) & "'")
        End If


    End Sub

    Private Sub ApplicantsDataGridView_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles ApplicantsDataGridView.DataError
        Debug.WriteLine(e.ColumnIndex)
        Debug.WriteLine(e.RowIndex)
        Debug.WriteLine(ApplicantsDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value)
        Debug.WriteLine(ApplicantsDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).EditedFormattedValue)
        e.ThrowException = True
    End Sub

    Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
        RefreshAll()
    End Sub
    Private Sub RefreshAll()
        ''get the job titles for the applicants
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next

        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountWIAActivity().ToString   'No STEPS Program for SYEP 2008
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString

        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)

        'Update Applicants(keyWorksiteID, keyEmployerID, WorksiteName, EmployerName) from JobTitles(keyWorksiteID, keyEmployerID, WorksiteName, EmployerName) in the database tables
        Try
            Me.ApplicantsTableAdapter.spUpdate_Applicants_WorksiteName_From_JobTitles()
            Me.ApplicantsTableAdapter.spUpdate_Applicants_CityName_From_tblCities(Me.SYEP2007DataSet.Applicants)
            Me.ApplicantsTableAdapter.spUpdate_Applicants_ZipCode_From_tblZips(Me.SYEP2007DataSet.Applicants)
            'Me.ApplicantsTableAdapter.spUpdate_Applicants_WorkersCompCode(Me.SYEP2007DataSet.Applicants)  '###### Check on this occasionally - It should be fine.
            Me.ApplicantsTableAdapter.spUpdate_Applicants_WorksiteCityPreference(Me.SYEP2007DataSet.Applicants)
            Me.JobTitlesTableAdapter.FillBy1(Me.SYEP2007DataSet.JobTitles) ' runs spUpdate_JobTitles_WorksiteName Re-loads JobTitles. Need to refresh JobTitles GridView
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_EmployerName(Me.SYEP2007DataSet.JobTitles)  'runs spUpdate_JobTitles_EmployerName to put Employer Names into the GridView
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_WorksiteCity(Me.SYEP2007DataSet.JobTitles) '****** Copy this Try/Catch to the Load Event
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_WorksiteZip(Me.SYEP2007DataSet.JobTitles)
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_PositionsAvailable(Me.SYEP2007DataSet.JobTitles)
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_PositionsTaken(Me.SYEP2007DataSet.JobTitles)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

        '*****Start  Copied from the Load Event *****  Not sure if all lines are necessary
        Try
            Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try
        'Try
        '    Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        'Catch ex As Exception
        '    MessageBox.Show(ex.StackTrace)
        'End Try

        'dtApplicants = Me.SYEP2007DataSet.Applicants
        'dtJobs = Me.SYEP2007DataSet.JobTitles
        '*****End  Copied from the Load Event *****  Not sure if all lines are necessary

        ''#####  Start  Update Positions Available, Positions Taken and Total in DataGridView  Note !!!  PositionsAvailable and txtPositions are labeled backwards !!!
        'Try
        'Me.JobTitlesTableAdapter.spUpdate_JobTitles_PositionsAvailable(Me.SYEP2007DataSet.JobTitles)
        'Me.JobTitlesTableAdapter.spUpdate_JobTitles_PositionsTaken(Me.SYEP2007DataSet.JobTitles)
        'Catch ex As Exception
        '    System.Windows.Forms.MessageBox.Show(ex.Message)
        'End Try
        ''#####  End    Update Positions Available, Positions Taken and Total in DataGridView  Note !!!  PositionsAvailable and txtPositions are labeled backwards !!!
        '*****Start  Copied from the btn2008 Event to repopulate the Applicants GridView after a btnRefresh - Must be last *****  
        Try
            Me.ApplicantsTableAdapter.FillBy3(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        '*****End  Copied from the btn2008 Event to repopulate the Applicants GridView after a btnRefresh *****  

        ''get the job titles for the applicants '## Copied from Private Sub ApplicantsDataGridView_ColumnHeaderMouseClick right below so that when the 
        ''Refresh button is clicked it will update the JobTitles column because the Refresh button was clearing the column and I had to then click
        '' the ColumnHeader of the JobTitles column to get the job titles back.  This should do it automatically.
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next

    End Sub
    Private Sub ApplicantsDataGridView_ColumnHeaderMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles ApplicantsDataGridView.ColumnHeaderMouseClick
        ''get the job titles for the applicants
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
    End Sub
    Private Sub FillByToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByToolStripButton.Click
        Try
            Me.ApplicantsTableAdapter.FillBy(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub btnConsideredFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConsideredFilter.Click
        Try
            Me.ApplicantsTableAdapter.FillBy(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
        btnSYEPFilter.BackColor = Color.LightGray
        btnConsideredFilter.BackColor = Color.Violet
        btnFtLauderdaleFilter.BackColor = Color.LightGray
        btn2008.BackColor = Color.LightGray

    End Sub

    Private Sub FillBy1ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillBy1ToolStripButton.Click
        Try
            Me.ApplicantsTableAdapter.FillBy1(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub btnSYEPFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSYEPFilter.Click
        'For 2009 there are no STEPS so I made this filter on Status = '1' instead.  The button says "Status 1".
        Try
            Me.ApplicantsTableAdapter.FillBy1(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

        ''get the job titles for the applicants
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
        btnSYEPFilter.BackColor = Color.Violet
        btnConsideredFilter.BackColor = Color.LightGray
        btnFtLauderdaleFilter.BackColor = Color.LightGray
        btn2008.BackColor = Color.LightGray

    End Sub
    Private Sub btnFtLauderdaleFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFtLauderdaleFilter.Click
        Try
            Me.ApplicantsTableAdapter.spFilter_FtLauderdaleKids(Me.SYEP2007DataSet.Applicants) '.Fill Applicants Table Adapter with Fort Lauderdale Kids only
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        btnSYEPFilter.BackColor = Color.LightGray
        btnConsideredFilter.BackColor = Color.LightGray
        btnFtLauderdaleFilter.BackColor = Color.Violet
        btn2008.BackColor = Color.LightGray

    End Sub

    Private Sub FillBy2ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillBy2ToolStripButton.Click
        Try
            Me.ApplicantsTableAdapter.FillBy2(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub txtAllFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAllFilter.Click
        'All 07-09 Button
        Try
            Me.ApplicantsTableAdapter.FillBy2(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        ' Put Total of all Applicants from all years into Text Box.  Sp counts all applicants.
        Dim count As Integer = CType(Me.ApplicantsTableAdapter.spCount_AllApplicants, Integer)
        txtTotalApplicants.Text = String.Format("{0} Applicants", count)

    End Sub
    Private Sub test()
        'Just testing how to convert a string to Proper Case.  Names like McIntire are problematic.
        Dim myTest As String
        myTest = StrConv("test", VbStrConv.ProperCase)
    End Sub

    Private Sub btn2008_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2008.Click
        '2009 Button
        Try
            Me.ApplicantsTableAdapter.FillBy3(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

        ''get the job titles for the applicants '## Copied from Private Sub ApplicantsDataGridView_ColumnHeaderMouseClick right below so that when the 
        ''Refresh button is clicked it will update the JobTitles column because the Refresh button was clearing the column and I had to then click
        '' the ColumnHeader of the JobTitles column to get the job titles back.  This should do it automatically.
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
        ' Put total of 2009 Applicants into the text box. The sp counts 2009 Applicants
        Dim count As Integer = CType(Me.ApplicantsTableAdapter.spCount_Applicants2008Total, Integer)
        txtTotalApplicants.Text = String.Format("{0} Applicants", count)
        btnSYEPFilter.BackColor = Color.LightGray
        btnConsideredFilter.BackColor = Color.LightGray
        btnFtLauderdaleFilter.BackColor = Color.LightGray
        btn2008.BackColor = Color.Violet

    End Sub
    Private Sub btnWIA_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWIA.Click
        'Filter Applicants Grid on WIA in txtActivityType field
        'spFilter_WIA_Applicants
        Try
            Me.ApplicantsTableAdapter.spFilter_WIA_Applicants(Me.SYEP2007DataSet.Applicants) '.Fill Applicants Table Adapter with WIA 19-24 yo Kids only
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        btnSYEPFilter.BackColor = Color.LightGray
        btnConsideredFilter.BackColor = Color.LightGray
        btnFtLauderdaleFilter.BackColor = Color.LightGray
        btn2008.BackColor = Color.LightGray
        btnWIA.BackColor = Color.Violet
    End Sub

End Class