Private Sub | The Blog Pros

October 7, 2020

VB Net Background Worker freeze when updating the GridView

I have some problem with my code, i can fetching data from database perfectly fine without any freezing, but after the fetching finish and i want to add the fetched data into grid view, the program will freeze even though i already input the code inside background worker.

Here is the code for table declaration, i use DevExpress Grid Data if you wondering about it.

Dim Table1 As DataTable 
Dim ItemCode As String = ""
Dim ItemName As String = ""
Dim BrandName As String = ""
Dim FamilyName As String = ""
Dim SubFamilyName As String = ""

Sub loadtabel()
    Table1 = New DataTable("myTable")

    Table1.Columns.Add(CreateColumn("ItemCode", "str"))
    Table1.Columns.Add(CreateColumn("ItemName", "str"))
    Table1.Columns.Add(CreateColumn("BrandName", "str"))
    Table1.Columns.Add(CreateColumn("Quantity", "int"))
    Table1.Columns.Add(CreateColumn("UOM", "str"))
    Table1.Columns.Add(CreateColumn("FamilyName", "str"))
    Table1.Columns.Add(CreateColumn("SubFamilyName", "str"))

    dgvTabel.DataSource = Table1


End Sub

Here is my function to starting my BackgroundWorker

Sub LoadData(ByVal varItemCode As String, ByVal varItemName As String, ByVal varBrandName As String, ByVal varFamilyName As String, ByVal varSubFamilyName As String)
        loadtabel() 

        If BackgroundWorker1.IsBusy Then
            Exit Sub
        End If

        ItemCode = ""
        ItemName = ""
        BrandName = ""
        FamilyName = ""
        SubFamilyName = ""

        If varItemCode <> "" Then ItemCode = varItemCode
        If varItemName <> "" Then ItemName = varItemName
        If varBrandName <> "" Then BrandName = varBrandName
        If varFamilyName <> "" Then FamilyName = varFamilyName
        If varSubFamilyName <> "" Then SubFamilyName = varSubFamilyName

        pbLoading.Visible = True
        lblLoading.Text = "Fetching Data, Please Wait..."

        BackgroundWorker1.RunWorkerAsync()

End Sub

And here is my BackgroundWorker query, in here i also do the fetching data. fetching data from database itself takes up to 11 Second. About more than 20.000 Data. but since i use Background Worker it fetching smoothly without freezing.

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


        Dim request As HttpWebRequest
            Dim reader As StreamReader
            Dim postData As String = ""

            Dim UrlPrefix = "http://" & My.Settings.SharedConnection & ":" & My.Settings.SharedURLPort
            request = DirectCast(WebRequest.Create(UrlPrefix & "/hotraxpos/item_list.php"), HttpWebRequest)

        postData = "itemcode=" & ItemCode &
                               "&itemname=" & ItemName &
                               "&brandname=" & BrandName &
                               "&familyname=" & FamilyName &
                               "&subfamilyname=" & SubFamilyName &
                               "&device_data=HC ALL IN ONE"

        request.Method = "POST"

            Dim byteArray As Byte() = Encoding.UTF8.GetBytes(postData)

            request.ContentType = "application/x-www-form-urlencoded"
            request.ContentLength = byteArray.Length

            Dim dataStream As Stream = request.GetRequestStream()
            dataStream.Write(byteArray, 0, byteArray.Length)
        dataStream.Close()

           Dim response As WebResponse = request.GetResponse()
            reader = New StreamReader(response.GetResponseStream())

            Dim rawresp As String
            rawresp = reader.ReadToEnd()

            Dim array As JArray = JArray.Parse(rawresp)

       'IF I EXCLUDE CODE FROM HERE, THE BACKGROUND WORKER WORK NORMALLY WITHOUT FREEZING
        Me.Invoke(Sub()

                      For Each item As JObject In array
                          Dim status As String = If(item("Status") Is Nothing, "", item("Status").ToString())
                          Dim Keterangan As String = If(item("Keterangan") Is Nothing, "", item("Keterangan").ToString())

                          Console.WriteLine("Keterangan: " & Keterangan)

                          If status = "SUCCESS" Then
                              Dim varItemCode = If(item("ItemCode") Is Nothing, 0, item("ItemCode").ToString)
                              Dim varItemName = If(item("ItemName") Is Nothing, 0, item("ItemName").ToString)
                              Dim varBrandName = If(item("BrandName") Is Nothing, 0, item("BrandName").ToString)
                              Dim varQuantity = If(item("Quantity") Is Nothing, 0, Val(item("Quantity").ToString))
                              Dim varUOM = If(item("UOM") Is Nothing, 0, item("UOM").ToString)
                              Dim varFamilyName = If(item("FamilyName") Is Nothing, 0, item("FamilyName").ToString)
                              Dim varSubFamilyName = If(item("SubFamilyName") Is Nothing, 0, item("SubFamilyName").ToString)

                              Dim Row1 As DataRow
                              Row1 = Table1.NewRow()

                              Row1.Item("ItemCode") = varItemCode
                              Row1.Item("ItemName") = varItemName
                              Row1.Item("BrandName") = varBrandName
                              Row1.Item("Quantity") = varQuantity
                              Row1.Item("UOM") = varUOM
                              Row1.Item("FamilyName") = varFamilyName
                              Row1.Item("SubFamilyName") = varSubFamilyName

                              Table1.Rows.Add(Row1)
                          End If
                      Next
                      dgvTabel.DataSource = Table1
                  End Sub)


    End Sub

Private Sub bgwSuburbs_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted 
    pbLoading.Visible = False

    lblLoading.Text = "Done"
End Sub

Just like i mention above, if i remove code at Me.Invoke(Sub() inside of Background Worker DoWork it work perfectly without freezing, but also without any data showing too...
please help, i just need to know how to fill the datagridview too without freezing with background worker. how do i do this ?

April 22, 2019

VB.net Check datatable for an existing account number

Hi group,

I'm attempting to write code to check to see if an existing account number exists. The user is to input a 10 digit account number. The code then is to query the database to see if that number exists. If it does, the message box displays the message that the number exists. However the code I've written isn't working as I wish. Can you offer some suggestions as to how to do this correctly?

Here's what I've attempted:

Private Sub tbxAccountNo_TextChanged(sender As Object, e As EventArgs) Handles tbxAccountNo.TextChanged
    If GlobalVariables.custpnl1 = 2 And tbxAccountNo.Text.Length = 10 And IsNumeric(tbxAccountNo.Text) = True Then
        Dim dt As New DataTable()
        Dim rowIndex As Integer = 0
        Dim searchID As Int64
        Dim strQ As String = String.Empty
        Dim conStr As String

        Dim msgAcctNo As String

        strQ = "SELECT CUST_ACCT_NO
                FROM CUSTREC 
                WHERE CUST_ACCT_NO = " & searchID
        conStr = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True"

        Dim dataAdapter As New SqlDataAdapter(strQ, conStr)
        dataAdapter.Fill(dt)
        dataAdapter.Dispose()

        For i As Integer = 0 To (dt.Rows.Count - 1)
            rowIndex = i
            If IsDBNull(dt.Rows(rowIndex)("CUST_ACCT_NO")) Then
                acctNoExists = False
            Else
                msgAcctNo = CStr(dt.Rows(rowIndex)("CUST_ACCT_NO"))
                acctNoExists = True
                MessageBox.Show("This account number exists.  Please enter a unique 10 digit account number.", "", MessageBoxButtons.OK)
                Exit Sub
            End If
        Next
    End If
End Sub

The "Text_Changed" event is used to fire this off - when there are 10 numeric characters in the textbox. That part seems to be working correctly as I've toggled it to stop at "For i As Integer = 0 To (dt.Rows.Count - 1)". But at this point, that's the only thing that seems to be working.

If you can teach me what I'm doing wrong, please feel free to do so.

As always, thanks for your help.

Don

April 18, 2019

Why is the ListView box showing same headings twice?

Group,

I've written some code to populate a listview using 3 fields from a database. When creating the listview, I named the individual columns for appearance purposes, (Account No, Company Name and Name). Those columns are being populated appropriately as expected. However data column names are being shown in column 4 5 and 6, but with no data. What in my code is causing this? Any suggestions on how to fix this?

My code is as follows:

Private Sub tbxCompanyName_Leave(sender As Object, e As EventArgs) Handles tbxCompanyName.Leave
    lvwSearchCustomer.Visible = True
    Me.lvwSearchCustomer.View = View.Details
    Me.lvwSearchCustomer.GridLines = True
    Dim strQ As String = String.Empty
    Dim datasource As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True"
    conn = New SqlConnection(datasource)
    Dim searchID As String = tbxCompanyName.Text

    strQ = "SELECT CUST_ACCT_NO, 
            CUST_COMPANY_NAME,
            CONCAT(CUST_FIRST_NAME,' ',CUST_MIDDLE_INITIAL,' ',CUST_LAST_NAME) as MailingName    
            FROM CUSTREC 
            WHERE CUST_COMPANY_NAME LIKE '" & searchID & "%' OR CUST_LAST_NAME LIKE '" & searchID & "%'"

    cmd = New SqlCommand(strQ, conn)
    da = New SqlDataAdapter(cmd)
    ds = New DataSet
    da.Fill(ds, "Tables")
    Dim i As Integer = 0
    Dim j As Integer = 0
    ' adding the columns in ListView
    For i = 0 To ds.Tables(0).Columns.Count - 1
        Me.lvwSearchCustomer.Columns.Add(ds.Tables(0).Columns(i).ColumnName.ToString())
    Next
    'Now adding the Items in Listview
    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 0 To ds.Tables(0).Columns.Count - 1
            itemcoll(j) = ds.Tables(0).Rows(i)(j).ToString()
        Next
        Dim lvi As New ListViewItem(itemcoll)
        Me.lvwSearchCustomer.Items.Add(lvi)
    Next

End Sub

In advance, thanks for your assistance.

Don