How do I make background worker work in this?

Question

0.00/5 (No votes)

See more:

The following is the code on which I am working

VB

Imports System.ComponentModel
Imports System.Data.OleDb
Imports System.IO
Imports System.Text.RegularExpressions
Imports MySql
Imports MySql.Data
Imports MySql.Data.MySqlClient
Public Class frmBulkUpload
    Dim conn As OleDbConnection
    Dim dta As OleDbDataAdapter
    Dim dts As DataSet
    Dim excel As String
    Dim openfiledialog As New OpenFileDialog

    Private Sub frmBulkUpload_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Control.CheckForIllegalCrossThreadCalls = False
        btnSubmit.Enabled = False
        dgwUserMaster.DataSource = Nothing
        lblMaster.Text = "Upload Bulk Salary Payable Transactions"
        ProgressBar1.Visible = False
    End Sub
    Dim inc As Integer
    Dim validator As Boolean
    Dim line As String = ""
    Private Sub btnGetData_Click(sender As Object, e As EventArgs) Handles btnGetData.Click
        line = ""

        btnSubmit.Enabled = False

        validator = True
        Try
            openfiledialog.Filter = "Excel files (*.xlsx)|*.xlsx|XLS Files (*.xls)|*xls"
            If (openfiledialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
                Dim fi As New FileInfo(openfiledialog.FileName)
                Dim FileName As String = openfiledialog.FileName

                excel = fi.FullName


                conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
                dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
                dts = New DataSet
                dta.Fill(dts, "[Sheet1$]")

                dgwUserMaster.DataSource = dts
                dgwUserMaster.DataMember = "[Sheet1$]"
                dgwUserMaster.Columns(0).DefaultCellStyle.Format = "dd-MM-yyyy"
                conn.Close()
                If dts.Tables("[Sheet1$]").Columns.Count = 5 Then

                    If dgwUserMaster.Columns(0).HeaderText = "Date of Entry" And dgwUserMaster.Columns(1).HeaderText = "Employee Code" And dgwUserMaster.Columns(2).HeaderText = "Employee Name" And dgwUserMaster.Columns(3).HeaderText = "Amount" And dgwUserMaster.Columns(4).HeaderText = "Remark" Then
                        For x As Integer = 0 To dgwUserMaster.Rows.Count - 1
                            ProgressBar1.Visible = True
                            Dim pattern As String
                            'pattern = ""
                            'Dim regexAmt As New Regex(pattern)
                            'If regexAmt.IsMatch(dgwUserMaster.Rows(x).Cells(0).Value.ToString) Then
                            'Else
                            '    validator = False
                            '    line &= "The format of amount on line " & x.ToString & " is incorrect." & vbCrLf
                            'End If

                            Try
                                pattern = DateTime.Parse(dgwUserMaster.Rows(x).Cells(0).Value).ToString("dd-MM-yyyy")
                            Catch ex As Exception
                                validator = False
                                line &= "The format of date on line " & (x + 1).ToString & " is incorrect." & vbCrLf
                            End Try

                            'DateTime.Parse(dgwUserMaster.Rows(x).Cells(0).Value).ToString("dd-MM-yyyy")
                            pattern = "^\d*[0-9](|.\d*[0-9]|,\d*[0-9])?$"
                            Dim regexAmt As New Regex(pattern)
                            If regexAmt.IsMatch(dgwUserMaster.Rows(x).Cells(3).Value.ToString) Then
                            Else
                                validator = False
                                line &= "The format of amount on line " & (x + 1).ToString & " is incorrect." & vbCrLf
                            End If


                            'Dim regex As New Regex(pattern)
                            'If regex.IsMatch(dgwUserMaster.Rows(x).Cells(y).Value.ToString("dd/MM/yyyy")) Then

                            'Else
                            '    validator = False
                            'End If



                            'Dim pattern As String = "/^(0|[1-9]\d*)(\.\d+)?$/"
                            'Dim regex As New Regex(pattern)
                            'If regex.IsMatch(dgwUserMaster.Rows(x).Cells(y).Value) Then

                            'Else
                            '    validator = False
                            'End If

                        Next

                    Else
                        MsgBox("Data Format not supported!")
                    End If
                Else
                    MsgBox("Data Format not supported!")
                End If


            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


        Try

            Dim sqladapter As New MySqlDataAdapter("SELECT MAX(TransactionID)+1 from transactionmaster;", MDIParent1.MysqlConn)

            Dim dt As New DataTable
            sqladapter.Fill(dt)
            If (dt IsNot Nothing AndAlso dt.Rows.Count = 1) Then
                inc = dt.Rows(0)(0)
            Else
                inc = 1
            End If
            dts.Tables("[Sheet1$]").Columns.Add("EmployeeID", GetType(String))
            dts.Tables("[Sheet1$]").Columns.Add("Employee Name as per Database", GetType(String))
            dgwUserMaster.Columns("EmployeeID").Visible = False
            For x As Integer = 0 To dgwUserMaster.Rows.Count - 1
                Dim sqladapter2 As New MySqlDataAdapter("Select * from ledgermaster where EmployeeID='" & dgwUserMaster.Rows(x).Cells(1).Value & "'", MDIParent1.MysqlConn)
                Dim dt2 As New DataTable
                sqladapter2.Fill(dt2)
                If (dt2 IsNot Nothing AndAlso dt2.Rows.Count = 1) Then
                    dts.Tables("[Sheet1$]").Rows(x)("EmployeeID") = dt2.Rows(0)(0)
                    dts.Tables("[Sheet1$]").Rows(x)("Employee Name as per Database") = dt2.Rows(0)(1)
                Else
                    validator = False
                    line &= "The Employee ID on line " & (x + 1).ToString & " does not exist." & vbCrLf



                End If


            Next


        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        If dgwUserMaster.Rows.Count > 0 Then
            If validator = True Then
                btnSubmit.Enabled = True
            Else
                MsgBox(line)
            End If
        Else
            MsgBox("No data available!")
        End If
        ProgressBar1.Visible = False
    End Sub

    Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click


        Try
            Dim command As New MySqlCommand
            Dim query As String
            Dim query2 As String
            command.Connection = MDIParent1.MysqlConn
            command.CommandText = "begin;"
            command.CommandText &= "INSERT into transactionmaster (TransactionID, VoucherType,CreatedBy,CreatedOn,DateOfEntry) values"
            query2 = "INSERT into journalentrymaster (TransactionID, DrID, CrID, DrAmount, CrAmount, Remark) values "
            query = ""
            For x As Integer = 0 To dgwUserMaster.Rows.Count - 1

                query &= "('" & inc & "','Salary Payable', '" & MDIParent1.UserID & "', '" & Date.Now.ToString("yyyy-MM-dd HH:mm:ss") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Date of Entry").ToString & "')"
                query2 &= "('" & inc & "', '4','" & dts.Tables("[Sheet1$]").Rows(x)("EmployeeID") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Remark") & "')"
                inc = inc + 1
                If x = dgwUserMaster.Rows.Count - 1 Then
                    query &= ";"
                    query2 &= ";"
                Else
                    query &= ","
                    query2 &= ","
                End If
            Next
            command.CommandText &= query
            command.CommandText &= query2
            command.CommandText &= "commit;"
            command.ExecuteNonQuery()
            MsgBox(dgwUserMaster.Rows.Count.ToString & " entries have been inserted!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

I am unable to use it through background worker.
It has two elements involved
One is importing an excel file and the other one is inserting the data in mysql.
Everything works fine if the background worker is not present. I want to report progress here in the progressbar because when the app will work with large data, it will take time

What I have tried:

VB

Imports System.ComponentModel
Imports System.Data.OleDb
Imports System.IO
Imports System.Text.RegularExpressions
Imports MySql
Imports MySql.Data
Imports MySql.Data.MySqlClient
Public Class frmBulkUpload
    Dim conn As OleDbConnection
    Dim dta As OleDbDataAdapter
    Dim dts As DataSet
    Dim excel As String
    Dim openfiledialog As New OpenFileDialog
    Dim bckgrndconn As New MySqlConnection
    Private Sub frmBulkUpload_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        bckgrndconn = MDIParent1.MysqlConn
        Control.CheckForIllegalCrossThreadCalls = False
        btnSubmit.Enabled = False
        dgwUserMaster.DataSource = Nothing
        lblMaster.Text = "Upload Bulk Salary Payable Transactions"
        ProgressBar1.Visible = False
    End Sub
    Dim inc As Integer
    Dim validator As Boolean
    Dim line As String = ""
    Private Sub btnGetData_Click(sender As Object, e As EventArgs) Handles btnGetData.Click
        line = ""

        btnSubmit.Enabled = False

        validator = True
        BackgroundWorker1.RunWorkerAsync()
    End Sub

    Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click


        Try
            Dim command As New MySqlCommand
            Dim query As String
            Dim query2 As String
            command.Connection = MDIParent1.MysqlConn
            command.CommandText = "begin;"
            command.CommandText &= "INSERT into transactionmaster (TransactionID, VoucherType,CreatedBy,CreatedOn,DateOfEntry) values"
            query2 = "INSERT into journalentrymaster (TransactionID, DrID, CrID, DrAmount, CrAmount, Remark) values "
            query = ""
            For x As Integer = 0 To dgwUserMaster.Rows.Count - 1

                query &= "('" & inc & "','Salary Payable', '" & MDIParent1.UserID & "', '" & Date.Now.ToString("yyyy-MM-dd HH:mm:ss") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Date of Entry").ToString & "')"
                query2 &= "('" & inc & "', '4','" & dts.Tables("[Sheet1$]").Rows(x)("EmployeeID") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Remark") & "')"
                inc = inc + 1
                If x = dgwUserMaster.Rows.Count - 1 Then
                    query &= ";"
                    query2 &= ";"
                Else
                    query &= ","
                    query2 &= ","
                End If
            Next
            command.CommandText &= query
            command.CommandText &= query2
            command.CommandText &= "commit;"
            command.ExecuteNonQuery()
            MsgBox(dgwUserMaster.Rows.Count.ToString & " entries have been inserted!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmBulkUpload_Closed(sender As Object, e As EventArgs) Handles Me.Closed

    End Sub



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

        Try
            openfiledialog.Filter = "Excel files (*.xlsx)|*.xlsx|XLS Files (*.xls)|*xls"
            If (openfiledialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
                Dim fi As New FileInfo(openfiledialog.FileName)
                Dim FileName As String = openfiledialog.FileName

                excel = fi.FullName


                conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
                dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
                dts = New DataSet
                dta.Fill(dts, "[Sheet1$]")

                dgwUserMaster.DataSource = dts
                dgwUserMaster.DataMember = "[Sheet1$]"
                dgwUserMaster.Columns(0).DefaultCellStyle.Format = "dd-MM-yyyy"
                conn.Close()
                If dts.Tables("[Sheet1$]").Columns.Count = 5 Then

                    If dgwUserMaster.Columns(0).HeaderText = "Date of Entry" And dgwUserMaster.Columns(1).HeaderText = "Employee Code" And dgwUserMaster.Columns(2).HeaderText = "Employee Name" And dgwUserMaster.Columns(3).HeaderText = "Amount" And dgwUserMaster.Columns(4).HeaderText = "Remark" Then
                        For x As Integer = 0 To dgwUserMaster.Rows.Count - 1
                            ProgressBar1.Visible = True
                            Dim pattern As String
                            'pattern = ""
                            'Dim regexAmt As New Regex(pattern)
                            'If regexAmt.IsMatch(dgwUserMaster.Rows(x).Cells(0).Value.ToString) Then
                            'Else
                            '    validator = False
                            '    line &= "The format of amount on line " & x.ToString & " is incorrect." & vbCrLf
                            'End If

                            Try
                                pattern = DateTime.Parse(dgwUserMaster.Rows(x).Cells(0).Value).ToString("dd-MM-yyyy")
                            Catch ex As Exception
                                validator = False
                                line &= "The format of date on line " & (x + 1).ToString & " is incorrect." & vbCrLf
                            End Try
                            BackgroundWorker1.ReportProgress((x + 0.5) * 50 / dgwUserMaster.Rows.Count)
                            'DateTime.Parse(dgwUserMaster.Rows(x).Cells(0).Value).ToString("dd-MM-yyyy")
                            pattern = "^\d*[0-9](|.\d*[0-9]|,\d*[0-9])?$"
                            Dim regexAmt As New Regex(pattern)
                            If regexAmt.IsMatch(dgwUserMaster.Rows(x).Cells(3).Value.ToString) Then
                            Else
                                validator = False
                                line &= "The format of amount on line " & (x + 1).ToString & " is incorrect." & vbCrLf
                            End If
                            BackgroundWorker1.ReportProgress((x + 1) * 50 / dgwUserMaster.Rows.Count)

                            'Dim regex As New Regex(pattern)
                            'If regex.IsMatch(dgwUserMaster.Rows(x).Cells(y).Value.ToString("dd/MM/yyyy")) Then

                            'Else
                            '    validator = False
                            'End If



                            'Dim pattern As String = "/^(0|[1-9]\d*)(\.\d+)?$/"
                            'Dim regex As New Regex(pattern)
                            'If regex.IsMatch(dgwUserMaster.Rows(x).Cells(y).Value) Then

                            'Else
                            '    validator = False
                            'End If

                        Next

                    Else
                        MsgBox("Data Format not supported!")
                    End If
                Else
                    MsgBox("Data Format not supported!")
                End If


            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


        ' Try

        Dim sqladapter As New MySqlDataAdapter("SELECT MAX(TransactionID)+1 from transactionmaster;", bckgrndconn)

        Dim dt As New DataTable
            sqladapter.Fill(dt)
            If (dt IsNot Nothing AndAlso dt.Rows.Count = 1) Then
                inc = dt.Rows(0)(0)
            Else
                inc = 1
            End If
            dts.Tables("[Sheet1$]").Columns.Add("EmployeeID", GetType(String))
            dts.Tables("[Sheet1$]").Columns.Add("Employee Name as per Database", GetType(String))
            dgwUserMaster.Columns("EmployeeID").Visible = False
            For x As Integer = 0 To dgwUserMaster.Rows.Count - 1
            Dim sqladapter2 As New MySqlDataAdapter("Select * from ledgermaster where EmployeeID='" & dgwUserMaster.Rows(x).Cells(1).Value & "'", bckgrndconn)
            Dim dt2 As New DataTable
                sqladapter2.Fill(dt2)
                If (dt2 IsNot Nothing AndAlso dt2.Rows.Count = 1) Then
                    dts.Tables("[Sheet1$]").Rows(x)("EmployeeID") = dt2.Rows(0)(0)
                    dts.Tables("[Sheet1$]").Rows(x)("Employee Name as per Database") = dt2.Rows(0)(1)
                Else
                    validator = False
                    line &= "The Employee ID on line " & (x + 1).ToString & " does not exist." & vbCrLf



                End If
            BackgroundWorker1.ReportProgress(50 + ((x + 1) * 50 / dgwUserMaster.Rows.Count))
            Threading.Thread.Sleep(2000)
        Next


        ' Catch ex As Exception
        '   MsgBox(ex.ToString)
        ' End Try


    End Sub

    Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
        ProgressBar1.Value = e.ProgressPercentage
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted

        If dgwUserMaster.Rows.Count > 0 Then
            If validator = True Then
                btnSubmit.Enabled = True
            Else
                MsgBox(line)
            End If
        Else
            MsgBox("No data available!")
        End If
        ProgressBar1.Visible = False
    End Sub

    Private Sub BackgroundWorker2_DoWork(sender As Object, e As DoWorkEventArgs) Handles BackgroundWorker2.DoWork

    End Sub

    Private Sub BackgroundWorker2_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles BackgroundWorker2.ProgressChanged

    End Sub

    Private Sub BackgroundWorker2_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BackgroundWorker2.RunWorkerCompleted

    End Sub
End Class

Posted 10-Feb-19 2:16am

ankitpsaraogi

Updated 10-Feb-19 5:54am

Richard MacCutchan

v2

Add a Solution

Comments

Richard Deeming 12-Feb-19 11:27am

query &= "('" & inc & "','Salary Payable', '" & MDIParent1.UserID & "', '" & Date.Now.ToString("yyyy-MM-dd HH:mm:ss") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Date of Entry").ToString & "')"
query2 &= "('" & inc & "', '4','" & dts.Tables("[Sheet1$]").Rows(x)("EmployeeID") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Remark") & "')"

Don't do it like that! Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Answer 1 · 2019-02-10T05:54:00

Solution 1

You should see if your "on the fly percentage complete calculation" is actually calculating anything and / or if .ReportProgress() is actually being called.

All your other code adds nothing.

Posted 10-Feb-19 5:54am

Gerry Schmitz

Comments

ankitpsaraogi 11-Feb-19 0:52am

Yes. That is working. Surprisingly, if I put a breakpoint and go step by step, the entire process works. But the scroll bars of datagridview is not displayed properly.