This is my code for populating the datagridview:
Public Sub generate_list_ftMK(ByVal clbBranch As CheckedListBox, ByVal dtpSDate As DateTimePicker, ByVal dtpEDate As DateTimePicker, ByVal dvList As DataGridView)
Dim ds As DataSet = New DataSet()
Dim da As MySqlDataAdapter
Dim strQ As String = String.Empty
Dim strQ1 As String = String.Empty
Dim colItemCode As String = String.Empty
Dim colReg_Pri As String = String.Empty
Dim colXL_Pri As String = String.Empty
Dim colDel_Date As String = String.Empty
Dim colLabel As String = String.Empty
Dim colDays As String = String.Empty
Dim PCT As String = String.Empty
Try
If con.State = ConnectionState.Open Then con.Close()
con.Open()
'clear the dataset
ds.Tables.Clear()
'loop for all branches
For i = 0 To clbBranch.CheckedItems.Count - 1
Application.DoEvents()
'get the Branch Code
chkBranch = clbBranch.CheckedItems(i).ToString
'select item code and price where JO_TYPE is JO
strQ = "select " & _
"CONCAT(a.STYLE_CODE, '-', LPAD((a.JO_NO), 4, '0'), '-', d.LINE_NO) as Item_Code, " & _
"DATE_FORMAT(c.DEL_DATE, '%Y-%m-%d') as DEL_DATE, " & _
"d.REG_PRI as Reg_Price, " & _
"d.XL_PRI as XL_Price, " & _
"a.LABEL_CODE, " & _
"a.STYLE_CODE, " & _
"LPAD((a.JO_NO), 4, '0')," & _
"d.LINE_NO, " & _
"DATEDIFF('" & Format(Date.Now, "yyyy-MM-dd") & "',DATE_FORMAT(c.DEL_DATE, '%Y-%m-%d')) as Days " & _
"from " & _
"jo_hdr as a " & _
"inner join " & _
"branch as b ON a.BRNCH_CODE = b.BRNCH_CODE " & _
"inner join " & _
"dr_hdr as c ON c.TRAN_REF = a.TRAN_NO " & _
"inner join " & _
"dr_det as d ON d.DR_NO = c.DR_NO " & _
"where " & _
"c.DEL_DATE BETWEEN '" & dtpSDate.Text & "' AND '" & dtpEDate.Text & "' " & _
"and a.LABEL_CODE = '" & FtMK_Code & "' " & _
"and a.BRNCH_CODE = '" & chkBranch & "' " & _
"and SUBSTRING(d.REG_PRI,LENGTH(d.REG_PRI) - 1,2) = 75 " & _
"and SUBSTRING(d.REG_PRI,LENGTH(d.XL_PRI) - 1,2) = 75 "
cmd = New MySqlCommand(strQ, con)
da = New MySqlDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "tblJO")
For r = 0 To ds.Tables("tblJO").Rows.Count - 1
Application.DoEvents()
colItemCode = ds.Tables("tblJO").Rows(r)(0).ToString
colDel_Date = ds.Tables("tblJO").Rows(r)(1).ToString
colReg_pri = ds.Tables("tblJO").Rows(r)(2).ToString
colXL_pri = ds.Tables("tblJO").Rows(r)(3).ToString
colLabel = ds.Tables("tblJO").Rows(r)(4).ToString
sumStyle = ds.Tables("tblJO").Rows(r)(5).ToString
sumJO = ds.Tables("tblJO").Rows(r)(6).ToString
sumNo = ds.Tables("tblJO").Rows(r)(7).ToString
colDays = ds.Tables("tblJO").Rows(r)(8).ToString
'for the number to decimal
colReg_pri = FormatNumber(colReg_pri, 2)
colXL_pri = FormatNumber(colXL_pri, 2)
'get the total quantity and the branch quantity
get_TotalQty_and_BranchQty()
'if the branch quantity is zero
If branchQty = 0 Then
PCT = "N\A"
Else
'compute the percent sold
PCT = totalQty
End If
dvList.Rows.Add(False, colItemCode, PCT, colDel_Date, Format(Date.Now, "yyyy-MM-dd"), colDays, _
colReg_Pri, colXL_Pri, colLabel, "JO", dtpSDate.Text, dtpEDate.Text, _
clbBranch.CheckedItems(i).ToString, mkType)
Next
Next
Catch ex As Exception
MsgBox(ex.Message)
Finally
con.Close()
End Try
End Sub
I have use indexes in my tables, and also add application.doevents. Is there anything I could do to make the populating in the datagridview faster with this code? I'm trying to search about making the datagridview doublebuffered because I read it online that I will make the datagridview faster but I have no idea how to use it.
Any help will be appreciated, Thank you.
