How to make function work together with Control.CheckForIllegalCrossThreadCalls

Question

When I comment Control.CheckForIllegalCrossThreadCalls = False my function works fine but when uncommented it gives an error

I am using a BackgroundWorker to call a function that inserts values into the database and there is a requirement for me to set Control.CheckForIllegalCrossThreadCalls = False in order for the code to work properly. Now when I set it to false one of the functions does not work as expected. Here is the error

Conversion from string "-0-1-22" to type 'Date' is not valid"

yet when that function works fine

Private Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click

    If Not BackgroundWorker1.IsBusy = True Then
        BackgroundWorker1.RunWorkerAsync()
    End If

End Sub

I expect the function to come up with "2019-06-11" as date to be inserted to the database of which works fine without Control.CheckForIllegalCrossThreadCalls = False

Here is the code

Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
    count_rows()
End Sub

Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
    MsgBox("Products imported successfully!", MsgBoxStyle.Information, "Import")
    Me.Close()
End Sub
Public Sub count_rows()
    import_attendance_sheet(1054)
End Sub
Private Sub import_attendance_sheet(ByVal id As Integer)
    ProgressBar1.Minimum = 0
    ProgressBar1.Maximum = id
    ProgressBar1.Value = 0
    Dim path As String = txtPath.Text
    Dim excel_connection As OleDbConnection
    Dim dt As DataTable
    Dim cmd As OleDbDataAdapter
    'Dim sql As String
    'Dim result As Boolean
    Dim emp_type_id As String = ""
    Dim branch_id As String = ""
    Dim bank_id As String = ""
    'Dim sheet_dates As New List(Of String)
    'excel_connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + ";Extended Properties=Excel 12.0 Xml; HDR=Yes;")
    excel_connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1;';")
    cmd = New OleDbDataAdapter("SELECT * FROM [sheet$]", excel_connection)
    dt = New DataTable
    cmd.Fill(dt)
    'initialize symbol row
    Dim count As Integer = 6
    'Loop through dates(column/header)
    For Each column As DataColumn In dt.Columns
        Dim colum_name As String = dt.Rows(0)(column.ColumnName).ToString()
        'check if column cell is empty
        If colum_name = "" Then
            'Empty do nothing
        Else
            'increment symbol row by 1
            count = count + 1
            'MsgBox(count)
            'Loop through rows of a particular date/column/header
            For Each r As DataRow In dt.Rows
                'check row(empNo) cell is not empty & does not have a string
                If r(5).ToString() = "" Or r(5).ToString() = "COY #" Then
                    'Empty do nothing
                Else
                    'show date | Emp No | Name | symbol index
                    'MsgBox(colum_name & " " & r(5).ToString() & " " & r(6).ToString() & " " & r(count).ToString())
                    'do the calculation
                    Dim employ_id As String = get_employee_id(r(5).ToString)
                    Dim basic_salary As Decimal = get_employee_basic_salary(r(5).ToString)
                    Dim staff_type_id As String = get_employee_type_id(r(5).ToString)
                    Dim days_per_month As Integer = get_employee_days_per_month(staff_type_id)
                    Dim hours_per_day As Double = get_employee_hours_per_day(staff_type_id)
                    Dim hourly_rate As Double = basic_salary / days_per_month / hours_per_day
                    Dim daily_rate As Double = basic_salary / days_per_month
                    Dim normal_working_hrs As String = get_normal_working_hrs()
                    Dim shift_duration As String = get_shift_duration()
                    'get symbol id and its rate
                    Dim symbol_id As String = get_attendance_symbol_id(r(count).ToString)
                    Dim symbol_rate As Double = get_attendance_symbol_rate(symbol_id)
                    Dim symbol_deduction_status As String = get_symbol_deduction_status(symbol_id)
                    Dim td_amount As Double = 0
                    If symbol_deduction_status = "DEDUCT" Then
                        td_amount = hourly_rate
                    Else
                        td_amount = 0
                    End If
                    Dim overtime As Double = shift_duration - normal_working_hrs
                    Dim ot_amount As Double = overtime * hourly_rate * symbol_rate
                    Dim chaka As String = Date.Now.ToString("yyyy")
                    Dim tsiku As String = Date.Now.ToString("dd")
                    Dim tsiku_mawu As String = Date.Now.ToString("dddd")
                    Dim mwezi As String = Date.Now.ToString("MMMM")
                    ' ''insert values into DB
                    Sql = "INSERT INTO tbl_attendance (employee_id,time_in,time_out,total_hours_worked,overtime,ot_amount,td_amount,attendance_code_id,attendance_code,attendance_date,comment,year,date,day,month,hourly_rate,bsalary,ot_status) VALUES ('" & employ_id & "','" & 0 & "','" & 0 & "','" & shift_duration & "','" & overtime & "','" & ot_amount & "','" & td_amount & "','" & symbol_id & "','" & r(count).ToString & "','" & calc_attendance_date(colum_name) & "','import','" & chaka & "','" & tsiku & "','" & tsiku_mawu & "','" & mwezi & "','" & hourly_rate & "','" & basic_salary & "','" & symbol_rate & "')"
                    result = save_to_db(Sql)
                    ProgressBar1.Value = ProgressBar1.Value + 1
                    'If result Then
                    '    Timer1.Start()
                    'End If
                End If
            Next
        End If
    Next
End Sub
'******* Function which shows the error ****************
Public Function calc_attendance_date(ByVal value As String)
    Dim at_date As String = ""
    Dim current_month As String = frmMain.cmbMonth.Text
    Dim current_year As String = frmMain.cmbYear.Text
    Dim mwezi As String
    Dim chaka As String
    Dim format_day As String = ""
    Dim format_month As String = ""
    'Date.Now.ToString("yyyy-MM-dd")
    '****  find previous month
    'when its january
    If current_month = "January" And value >= 22 And value <= 31 Then
        mwezi = "12"
        chaka = Convert.ToInt32(current_year) - 1
        at_date = chaka & "-" & mwezi & "-" & value
    ElseIf current_month <> "January" And value >= 22 And value <= 31 Then
        mwezi = IntMonth() - 1
        'day
        If value < 10 Then
            format_day = "0" & value
        ElseIf value >= 10 Then
            format_day = value
        End If
        'format mwezi
        If mwezi < 10 Then
            format_month = "0" & mwezi
        ElseIf mwezi >= 10 Then
            format_month = mwezi
        End If
        chaka = current_year
        at_date = chaka & "-" & format_month & "-" & format_day
    End If
    '****  find current month
    If current_month = "January" And value >= 1 And value <= 21 Then
        mwezi = IntMonth()
        chaka = current_year
        'day
        If value < 10 Then
            format_day = "0" & value
        ElseIf value >= 10 Then
            format_day = value
        End If
        'format mwezi
        If mwezi < 10 Then
            format_month = "0" & mwezi
        ElseIf mwezi >= 10 Then
            format_month = mwezi
        End If
        at_date = chaka & "-" & format_month & "-" & format_day
    ElseIf current_month <> "January" And value >= 1 And value <= 21 Then
        mwezi = IntMonth()
        chaka = current_year
        'day
        If value < 10 Then
            format_day = "0" & value
        ElseIf value >= 10 Then
            format_day = value
        End If
        'format mwezi
        If mwezi < 10 Then
            format_month = "0" & mwezi
        ElseIf mwezi >= 10 Then
            format_month = mwezi
        End If
        at_date = chaka & "-" & format_month & "-" & format_day
    End If
    Return at_date
End Function

What is the code which raises the exception? Most likely it's some interaction with the UI which you could wrap inside the following: `Me.Invoke(Sub() *yourcodehere* )` to ensure it is run on the UI thread. Without seeing the actual offending code (the code run in the BackgroundWorker), it's impossible to help more. — djv, Jun 11 '19 at 20:03
Whomever suggested you to set `Control.CheckForIllegalCrossThreadCalls = False` is an evil person. — Jimi, Jun 11 '19 at 20:11
It's kind of like the `DoEvents` of our generation. But not really :) — djv, Jun 11 '19 at 20:14
All the methods exception for `BackgroundWorker1_RunWorkerCompleted` cannot interact with UI elements, however you have violated this rule a couple of times. — djv, Jun 11 '19 at 20:25
So help me with a solution to my problem,how can i do it in the right way — Robins Harawa, Jun 11 '19 at 20:33

score 0 · Answer 1 · answered Jun 11 '19 at 20:39

You shouldn't try to interact with UI controls from a non-UI thread. You can however have code which interacts with those controls on a UI thread by using Control.Invoke. There are a few places where you do it.

Inside import_attendance_sheet

ProgressBar1.Minimum = 0
ProgressBar1.Maximum = id
ProgressBar1.Value = 0
Dim path As String = txtPath.Text
...
ProgressBar1.Value = ProgressBar1.Value + 1

Instead:

Dim path As String
Me.Invoke(
    Sub()
        ProgressBar1.Minimum = 0
        ProgressBar1.Maximum = id
        ProgressBar1.Value = 0
        path = txtPath.Text
    End Sub)
...
Me.Invoke(Sub() ProgressBar1.Value += 1)

All those functions like get_employee_id, get_employee_basic_salary, get_employee_type_id may also interact with the UI, but you don't provide them. If they do (i.e. return a value from a textbox or something) then you need to invoke inside them as well.

Inside calc_attendance_date

Dim current_month As String = frmMain.cmbMonth.Text
Dim current_year As String = frmMain.cmbYear.Text

Instead:

Dim current_month As String 
Dim current_year As String 
Me.Invoke(
    Sub()
        current_month = frmMain.cmbMonth.Text
        current_year = frmMain.cmbYear.Text
    End Sub)

The function IntMonth() is unknown to us too. Again, if it reads from the UI you should invoke the code which does the UI interaction.

The correct way to do this is only invoke when absolutely necessary, i.e. when Control.InvokeRequired. See this example which is very close to yours in that they wanted to use Me.CheckForIllegalCrossThreadCalls = False (BAD!)

Even better, you can write extension methods to automate the Control.InvokeRequired pattern

How to make function work together with Control.CheckForIllegalCrossThreadCalls

1 Answers1