After searching in the datagridview, edit the selected row

Claude Larocque 516 Reputation points
2022-05-17T20:11:37.35+00:00

Hi everyone,
I have some code below to search many fields in my ProductsDGV datagridview, however, the selected row stays at ProductID 1 instead of moving automatically on the selected ID.

I also put the code that I have on my datagridview (SelectionChanged and CellClick and KeyUp) also the code on my TxtFilterTB_KeyUp (a textbox)

202828-frmeditproducts.jpg
Thanks for any help

Claude from Quebec Canada

    Private Sub TxtFilterTB_KeyUp(sender As Object, e As KeyEventArgs) Handles TxtFilterTB.KeyUp  
        Try  
            If TxtFilterTB.Text.Trim().Length() >= 3 Then  
                ProductsDGV.DataSource = Me.PopulateDataGridView()  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Function PopulateDataGridView() As DataTable  
        Dim query As String = "SELECT * FROM Warehouse.Products"  
        query &= " WHERE ProductID LIKE '%' + @SearchTerm + '%'"  
        query &= " OR ProductName LIKE '%' + @SearchTerm + '%'"  
        query &= " OR Category LIKE '%' + @SearchTerm + '%'"  
        query &= " OR SubCategory LIKE '%' + @SearchTerm + '%'"  
        query &= " OR Department LIKE '%' + @SearchTerm + '%'"  
        query &= " OR SeasonCode LIKE '%' + @SearchTerm + '%'"  
        query &= " OR @SearchTerm = ''"  
        Dim constr As String = SQL.DBCon.ConnectionString  
        Using DBCon As New SqlConnection(constr)  
            Using DBCmd As New SqlCommand(query, DBCon)  
                DBCmd.Parameters.AddWithValue("@SearchTerm", TxtFilterTB.Text.Trim())  
                Using DBDA As New SqlDataAdapter(DBCmd)  
                    Dim DBDT As New DataTable()  
                    DBDA.Fill(DBDT)  
                    If Me.ProductMaintenanceTC.SelectedIndex = 0 Then  
                        Me.RecordsCountTB.Text = DBDT.Rows.Count.ToString  
                    End If  
                    Return DBDT  
                End Using  
            End Using  
        End Using  
    End Function  
  
    Public Sub ProductsDGV_SelectionChanged(sender As Object, e As EventArgs) Handles ProductsDGV.SelectionChanged  
        Try  
            Dim dgv As DataGridView = ProductsDGV  
            If Me.ProductsDGV.CurrentRow IsNot Nothing Then  
                ProductIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(0).Value)  
                ProductNameTB.Text = Convert.ToString(dgv.CurrentRow.Cells(1).Value)  
                CategoryCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(2).Value)  
                CategoryIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(3).Value)  
                SubCategoryCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(4).Value)  
                SubCategoryIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(5).Value)  
                DepartmentCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(6).Value)  
                DepartmentIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(7).Value)  
                SeasonCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(8).Value)  
                SeasonIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(9).Value)  
                LanguageDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(10).Value)  
                LanguageIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(11).Value)  
                ActiveCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(12).Value)  
                ConsignmentCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(13).Value)  
                ManageInventoryCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(14).Value)  
                SellingPrice1TB.Text = Convert.ToString(dgv.CurrentRow.Cells(15).Value)  
                SellingPrice2TB.Text = Convert.ToString(dgv.CurrentRow.Cells(16).Value)  
                SellingPrice3TB.Text = Convert.ToString(dgv.CurrentRow.Cells(17).Value)  
                SellingPrice4TB.Text = Convert.ToString(dgv.CurrentRow.Cells(18).Value)  
                SellingPrice5TB.Text = Convert.ToString(dgv.CurrentRow.Cells(19).Value)  
                SuggestedRetailPriceTB.Text = Convert.ToString(dgv.CurrentRow.Cells(20).Value)  
                StandardCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(21).Value)  
                LandingCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(22).Value)  
                LastCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(23).Value)  
                AverageCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(24).Value)  
                FreightCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(25).Value)  
                CreatedDateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(26).Value)  
                LastModifiedOnTB.Text = Convert.ToString(dgv.CurrentRow.Cells(27).Value)  
                UseProductTaxesCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(28).Value)  
                UseBusinessTaxesCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(29).Value)  
                NotTaxableCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(30).Value)  
                FinalTaxCodeTB.Text = Convert.ToString(dgv.CurrentRow.Cells(31).Value)  
                ImageDirectoryPathMasterTB.Text = Convert.ToString(dgv.CurrentRow.Cells(32).Value)  
                ImagePathTB.Text = Convert.ToString(dgv.CurrentRow.Cells(33).Value)  
                FileNameTB.Text = Convert.ToString(dgv.CurrentRow.Cells(34).Value)  
                FileTypeTB.Text = Convert.ToString(dgv.CurrentRow.Cells(35).Value)  
                FileSizeTB.Text = Convert.ToString(dgv.CurrentRow.Cells(36).Value)  
                FileCreatedOnTB.Text = Convert.ToString(dgv.CurrentRow.Cells(37).Value)  
                FileModifiedOnTB.Text = Convert.ToString(dgv.CurrentRow.Cells(38).Value)  
                UseInHouseBarcodeCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(39).Value)  
                UseEAN13BarcodeCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(40).Value)  
                UseQRCodeCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(41).Value)  
                BarcodeTB.Text = Convert.ToString(dgv.CurrentRow.Cells(42).Value)  
                BarcodeEAN13TB.Text = Convert.ToString(dgv.CurrentRow.Cells(43).Value)  
                QRCodePathTB.Text = Convert.ToString(dgv.CurrentRow.Cells(44).Value)  
                NotesTB.Text = Convert.ToString(dgv.CurrentRow.Cells(45).Value)  
                ConversionFactorTB.Text = Convert.ToString(dgv.CurrentRow.Cells(46).Value)  
                Choice1CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(47).Value)  
                Choice2CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(48).Value)  
                Choice3CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(49).Value)  
                Choice4CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(50).Value)  
                Choice5CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(51).Value)  
                ApplyDiscountCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(52).Value)  
                DiscountRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(53).Value)  
                DiscountAmountTB.Text = Convert.ToString(dgv.CurrentRow.Cells(54).Value)  
                ValidFromTB.Text = Convert.ToString(dgv.CurrentRow.Cells(55).Value)  
                ValidToTB.Text = Convert.ToString(dgv.CurrentRow.Cells(56).Value)  
                LastEditedByTB.Text = Convert.ToString(dgv.CurrentRow.Cells(57).Value)  
                MarketingCommentsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(58).Value)  
                InternalCommentsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(59).Value)  
                CustomFieldTB.Text = Convert.ToString(dgv.CurrentRow.Cells(60).Value)  
                InternetTagsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(61).Value)  
                SearchDetailsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(62).Value)  
                BrandCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(63).Value)  
                BrandIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(64).Value)  
                SizesTB.Text = Convert.ToString(dgv.CurrentRow.Cells(65).Value)  
                DimensionsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(66).Value)  
                PayCommissionOnCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(67).Value)  
                CommissionBasedOnProfitCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(68).Value)  
                CommissionBasedOnSaleCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(69).Value)  
                PercentageCommissionOnProfitsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(70).Value)  
                PercentageCommissionOnSalesTB.Text = Convert.ToString(dgv.CurrentRow.Cells(71).Value)  
                HasTransactionsCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(72).Value)  
                SendToCatalogCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(73).Value)  
                PrintBarcodeCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(74).Value)  
                PrintOnKitchenCouponCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(75).Value)  
                PrintOnBeveragesCouponCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(76).Value)  
                GiftCertificateCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(77).Value)  
                UseTax1CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(78).Value)  
                TaxNumber1TB.Text = Convert.ToString(dgv.CurrentRow.Cells(79).Value)  
                TaxeRate1DescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(80).Value)  
                TaxRate1TB.Text = Convert.ToString(dgv.CurrentRow.Cells(81).Value)  
                DivideTax1CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(82).Value)  
                DivideTax1aNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(83).Value)  
                DivideTax1aDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(84).Value)  
                DivideTax1aRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(85).Value)  
                DivideTax1bNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(86).Value)  
                DivideTax1bDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(87).Value)  
                DivideTax1bRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(88).Value)  
                DivideTax1cNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(89).Value)  
                DivideTax1cDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(90).Value)  
                DivideTax1cRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(91).Value)  
                UseTax2CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(92).Value)  
                TaxNumber2TB.Text = Convert.ToString(dgv.CurrentRow.Cells(93).Value)  
                TaxeRate2DescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(94).Value)  
                TaxRate2TB.Text = Convert.ToString(dgv.CurrentRow.Cells(95).Value)  
                DivideTax2CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(96).Value)  
                DivideTax2aNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(97).Value)  
                DivideTax2aDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(98).Value)  
                DivideTax2aRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(99).Value)  
                DivideTax2bNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(100).Value)  
                DivideTax2bDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(101).Value)  
                DivideTax2bRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(102).Value)  
                DivideTax2cNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(103).Value)  
                DivideTax2cDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(104).Value)  
                DivideTax2cRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(105).Value)  
                UseTax3CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(106).Value)  
                TaxNumber3TB.Text = Convert.ToString(dgv.CurrentRow.Cells(107).Value)  
                TaxeRate3DescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(108).Value)  
                TaxRate3TB.Text = Convert.ToString(dgv.CurrentRow.Cells(109).Value)  
                DivideTax3CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(110).Value)  
                DivideTax3aNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(111).Value)  
                DivideTax3aDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(112).Value)  
                DivideTax3aRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(113).Value)  
                DivideTax3bNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(114).Value)  
                DivideTax3bDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(115).Value)  
                DivideTax3bRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(116).Value)  
                DivideTax3cNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(117).Value)  
                DivideTax3cDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(118).Value)  
                DivideTax3cRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(119).Value)  
                ForceToChangeCustomerCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(120).Value)  
                ForceToChangeSalespersonCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(121).Value)  
                If Me.SendToCatalogCB.Checked = False Then  
                    BtnExportToCatalog.Enabled = False  
                Else  
                    BtnExportToCatalog.Enabled = True  
                End If  
                Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString  
                Me.RecordPositionCurrent.Text = BindingNavigatorPositionItem.ToString  
                'Me.ProductsDGV.DataSource = Me.ProductsBN.BindingSource  
                Me.SaveCB.Checked = False  
                Dim imgFilePath As String  
                imgFilePath = ImagePathTB.Text  
                If File.Exists(imgFilePath) Then  
                    PBPhoto.Image = Image.FromFile(imgFilePath)  
                    'Else  
                    'MsgBox("Image does not exist")  
                End If  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub ProductsDGV_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles ProductsDGV.CellClick  
        Dim dgv As DataGridView = ProductsDGV  
        Try  
            If e.RowIndex <> -1 Then  
                ProductIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(0).Value)  
                ProductNameTB.Text = Convert.ToString(dgv.CurrentRow.Cells(1).Value)  
                CategoryCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(2).Value)  
                CategoryIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(3).Value)  
                SubCategoryCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(4).Value)  
                SubCategoryIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(5).Value)  
                DepartmentCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(6).Value)  
                DepartmentIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(7).Value)  
                SeasonCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(8).Value)  
                SeasonIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(9).Value)  
                LanguageDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(10).Value)  
                LanguageIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(11).Value)  
                ActiveCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(12).Value)  
                ConsignmentCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(13).Value)  
                ManageInventoryCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(14).Value)  
                SellingPrice1TB.Text = Convert.ToString(dgv.CurrentRow.Cells(15).Value)  
                SellingPrice2TB.Text = Convert.ToString(dgv.CurrentRow.Cells(16).Value)  
                SellingPrice3TB.Text = Convert.ToString(dgv.CurrentRow.Cells(17).Value)  
                SellingPrice4TB.Text = Convert.ToString(dgv.CurrentRow.Cells(18).Value)  
                SellingPrice5TB.Text = Convert.ToString(dgv.CurrentRow.Cells(19).Value)  
                SuggestedRetailPriceTB.Text = Convert.ToString(dgv.CurrentRow.Cells(20).Value)  
                StandardCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(21).Value)  
                LandingCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(22).Value)  
                LastCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(23).Value)  
                AverageCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(24).Value)  
                FreightCostTB.Text = Convert.ToString(dgv.CurrentRow.Cells(25).Value)  
                CreatedDateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(26).Value)  
                LastModifiedOnTB.Text = Convert.ToString(dgv.CurrentRow.Cells(27).Value)  
                UseProductTaxesCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(28).Value)  
                UseBusinessTaxesCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(29).Value)  
                NotTaxableCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(30).Value)  
                FinalTaxCodeTB.Text = Convert.ToString(dgv.CurrentRow.Cells(31).Value)  
                ImageDirectoryPathMasterTB.Text = Convert.ToString(dgv.CurrentRow.Cells(32).Value)  
                ImagePathTB.Text = Convert.ToString(dgv.CurrentRow.Cells(33).Value)  
                FileNameTB.Text = Convert.ToString(dgv.CurrentRow.Cells(34).Value)  
                FileTypeTB.Text = Convert.ToString(dgv.CurrentRow.Cells(35).Value)  
                FileSizeTB.Text = Convert.ToString(dgv.CurrentRow.Cells(36).Value)  
                FileCreatedOnTB.Text = Convert.ToString(dgv.CurrentRow.Cells(37).Value)  
                FileModifiedOnTB.Text = Convert.ToString(dgv.CurrentRow.Cells(38).Value)  
                UseInHouseBarcodeCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(39).Value)  
                UseEAN13BarcodeCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(40).Value)  
                UseQRCodeCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(41).Value)  
                BarcodeTB.Text = Convert.ToString(dgv.CurrentRow.Cells(42).Value)  
                BarcodeEAN13TB.Text = Convert.ToString(dgv.CurrentRow.Cells(43).Value)  
                QRCodePathTB.Text = Convert.ToString(dgv.CurrentRow.Cells(44).Value)  
                NotesTB.Text = Convert.ToString(dgv.CurrentRow.Cells(45).Value)  
                ConversionFactorTB.Text = Convert.ToString(dgv.CurrentRow.Cells(46).Value)  
                Choice1CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(47).Value)  
                Choice2CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(48).Value)  
                Choice3CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(49).Value)  
                Choice4CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(50).Value)  
                Choice5CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(51).Value)  
                ApplyDiscountCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(52).Value)  
                DiscountRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(53).Value)  
                DiscountAmountTB.Text = Convert.ToString(dgv.CurrentRow.Cells(54).Value)  
                ValidFromTB.Text = Convert.ToString(dgv.CurrentRow.Cells(55).Value)  
                ValidToTB.Text = Convert.ToString(dgv.CurrentRow.Cells(56).Value)  
                LastEditedByTB.Text = Convert.ToString(dgv.CurrentRow.Cells(57).Value)  
                MarketingCommentsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(58).Value)  
                InternalCommentsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(59).Value)  
                CustomFieldTB.Text = Convert.ToString(dgv.CurrentRow.Cells(60).Value)  
                InternetTagsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(61).Value)  
                SearchDetailsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(62).Value)  
                BrandCBX.Text = Convert.ToString(dgv.CurrentRow.Cells(63).Value)  
                BrandIDTB.Text = Convert.ToString(dgv.CurrentRow.Cells(64).Value)  
                SizesTB.Text = Convert.ToString(dgv.CurrentRow.Cells(65).Value)  
                DimensionsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(66).Value)  
                PayCommissionOnCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(67).Value)  
                CommissionBasedOnProfitCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(68).Value)  
                CommissionBasedOnSaleCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(69).Value)  
                PercentageCommissionOnProfitsTB.Text = Convert.ToString(dgv.CurrentRow.Cells(70).Value)  
                PercentageCommissionOnSalesTB.Text = Convert.ToString(dgv.CurrentRow.Cells(71).Value)  
                HasTransactionsCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(72).Value)  
                SendToCatalogCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(73).Value)  
                PrintBarcodeCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(74).Value)  
                PrintOnKitchenCouponCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(75).Value)  
                PrintOnBeveragesCouponCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(76).Value)  
                GiftCertificateCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(77).Value)  
                UseTax1CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(78).Value)  
                TaxNumber1TB.Text = Convert.ToString(dgv.CurrentRow.Cells(79).Value)  
                TaxeRate1DescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(80).Value)  
                TaxRate1TB.Text = Convert.ToString(dgv.CurrentRow.Cells(81).Value)  
                DivideTax1CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(82).Value)  
                DivideTax1aNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(83).Value)  
                DivideTax1aDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(84).Value)  
                DivideTax1aRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(85).Value)  
                DivideTax1bNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(86).Value)  
                DivideTax1bDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(87).Value)  
                DivideTax1bRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(88).Value)  
                DivideTax1cNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(89).Value)  
                DivideTax1cDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(90).Value)  
                DivideTax1cRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(91).Value)  
                UseTax2CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(92).Value)  
                TaxNumber2TB.Text = Convert.ToString(dgv.CurrentRow.Cells(93).Value)  
                TaxeRate2DescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(94).Value)  
                TaxRate2TB.Text = Convert.ToString(dgv.CurrentRow.Cells(95).Value)  
                DivideTax2CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(96).Value)  
                DivideTax2aNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(97).Value)  
                DivideTax2aDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(98).Value)  
                DivideTax2aRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(99).Value)  
                DivideTax2bNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(100).Value)  
                DivideTax2bDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(101).Value)  
                DivideTax2bRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(102).Value)  
                DivideTax2cNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(103).Value)  
                DivideTax2cDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(104).Value)  
                DivideTax2cRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(105).Value)  
                UseTax3CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(106).Value)  
                TaxNumber3TB.Text = Convert.ToString(dgv.CurrentRow.Cells(107).Value)  
                TaxeRate3DescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(108).Value)  
                TaxRate3TB.Text = Convert.ToString(dgv.CurrentRow.Cells(109).Value)  
                DivideTax3CB.Checked = GetBoolValue(dgv.CurrentRow.Cells(110).Value)  
                DivideTax3aNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(111).Value)  
                DivideTax3aDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(112).Value)  
                DivideTax3aRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(113).Value)  
                DivideTax3bNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(114).Value)  
                DivideTax3bDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(115).Value)  
                DivideTax3bRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(116).Value)  
                DivideTax3cNumberTB.Text = Convert.ToString(dgv.CurrentRow.Cells(117).Value)  
                DivideTax3cDescTB.Text = Convert.ToString(dgv.CurrentRow.Cells(118).Value)  
                DivideTax3cRateTB.Text = Convert.ToString(dgv.CurrentRow.Cells(119).Value)  
                ForceToChangeCustomerCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(120).Value)  
                ForceToChangeSalespersonCB.Checked = GetBoolValue(dgv.CurrentRow.Cells(121).Value)  
                If Me.SendToCatalogCB.Checked = False Then  
                    BtnExportToCatalog.Enabled = False  
                Else  
                    BtnExportToCatalog.Enabled = True  
                End If  
                Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString  
                Me.RecordPositionCurrent.Text = BindingNavigatorPositionItem.ToString  
                'Me.ProductsDGV.DataSource = Me.ProductsBN.BindingSource  
                Me.SaveCB.Checked = False  
                Dim imgFilePath As String  
                imgFilePath = ImagePathTB.Text  
                If File.Exists(imgFilePath) Then  
                    PBPhoto.Image = Image.FromFile(imgFilePath)  
                    'Else  
                    'MsgBox("Image does not exist")  
                End If  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Public Sub ReloadData()  
        Try  
            ProductsDGV.DataSource = ProductsBN.BindingSource  
            ProductsDGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells  
            ProductsDGV.Sort(ProductsDGV.Columns(0), ListSortDirection.Ascending)  
            ProductsDGV.Font = New Font(Font.Name, Font.Size, FontStyle.Regular)  
            Me.CountRecords.Text = BindingNavigatorCountItem.ToString  
            Me.RecordsCountTB.Text = BindingNavigatorCountItem.ToString  
            LoadCategories()  
            LoadSubCategories()  
            LoadDepartments()  
            LoadSeasonsCBX()  
            LoadBrandsCBX()  
            LblCreatingNewProduct.Visible = False  
            If String.IsNullOrEmpty(DiscountAmountTB.Text.Trim()) Then  
                Me.DiscountAmountTB.Text = 0.0000  
            End If  
            If Me.SendToCatalogCB.Checked = False Then  
                BtnExportToCatalog.Enabled = False  
            Else  
                BtnExportToCatalog.Enabled = True  
            End If  
  
            ProductNameTB.Select()  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\AutoCashRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
1,843 questions
No comments
{count} votes

Accepted answer
  1. LesHay 5,776 Reputation points
    2022-05-17T20:57:20.903+00:00

    Hi

    A lot of repetitive code to look through there. The only thing I kept thinking while trying to undestand what you were doing was 'why not use a filter' = you hav datatables, bindingsources all eager to assist in what I believe you are doing.
    Anyway, I may way off the mark, but here is a stand alone text project that filters the rows with any matches from user input into a textbox in real time. It generates some pecular and random data just to try it out, and as someting is entered into the textbox, all rows containing the search term in any column are displayed. No data is 'lost', and just clearing the search input textbox will display the original whole data. As I say, just ignor this if it is off track.

    Option Strict Off
    Option Explicit On
    ' DGV DT DYNAMIC ROW FILTER
    
    ' This project attempts to allow User
    ' to Filter all columns in real time
    
    ' This example needs a Form1 with
    ' a blank DataGridView1 and
    ' TextBox1 
    
    Public Class Form1
      Dim Label1 As New Label
      Dim rand As New Random
      Dim myTable As New DataTable("Freddy")
      Dim BS As New BindingSource
      Dim Path As String = Application.StartupPath & "\Data\Data.xml"
    
    
      '"C:\Users\lesha\Documents\Projects\!!!STORE\DataTable DGV and filters\WindowsApplication2\bin\Debug\Data\Data.xml"
      Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        Dim parent As String = My.Computer.FileSystem.GetParentPath(Path)
        ' if the Folder doesn't exist, create it
        If Not IO.Directory.Exists(parent) Then
          IO.Directory.CreateDirectory(parent)
        End If
        ' save data file when exit the application
        myTable.WriteXml(Path)
      End Sub
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Size = New Size(940, 250)
    
    
        ' no Data file, so create dummy data
        ' this will be saved on app exit
        With myTable
            ' add some random columns
            .Columns.Add("Integer", GetType(Integer))
            .Columns.Add("Boolean", GetType(String))
            .Columns.Add("Double", GetType(Double))
          .Columns.Add("Firstname", GetType(String))
          .Columns.Add("Lastname", GetType(String))
          .Columns.Add("Gender", GetType(String))
            .Columns.Add("DateTime", GetType(String))
            .Columns.Add("Decimal", GetType(Decimal))
            .Columns.Add("Sum 0 2 7", GetType(Decimal), "Integer + Double + Decimal")
    
            ' just some test data
            Dim r1() As String = {"Freddy", "Mary", "Brian", "Led", "Smith", "Brown", "MacHine", "Elizabedh", "Jones", "Tom"}
                Dim r2() As String = {"Male", "Female", "Undeclared", "T-Shirt Male", "T-Shirt Female", "T-Shirt Child", "T-Shirt Formal", "T-Shirt XXXL"}
                Dim r3() As Boolean = {True, False}
    
                Dim md As Double = Double.MaxValue
            .Rows.Add(11, True, 12, "Freddy", "MacHine", "Male", Now.AddDays(11).Date.ToString("ddd, dd MMM"), 13)
            .Rows.Add(3334, False, 13.122345678901271, "Mary", "Smith", "Female", Now.AddDays(12).Date.ToString("ddd, dd MMM"), 1.25)
                .Rows.Add(3335, True, 129.12234567890124, "George", "Mathews", "undeclared", Now.AddDays(13).Date.ToString("ddd, dd MMM"), 1.255)
    
                For i As Integer = 0 To 99
                    .Rows.Add(i, If(rand.Next(0, 2) = 1, "False", "True"), rand.NextDouble() * 10000, r1(rand.Next(r1.Length)), r1(rand.Next(r1.Length)), r2(rand.Next(r2.Length)), Now.Date.ToString("ddd, dd MMM"), rand.NextDouble() * 100)
                Next
            End With
    
        BS.DataSource = myTable
    
        ' just some formatting of DGV
        With DataGridView1
          .DataSource = BS
          .MultiSelect = False
                .RowHeadersWidth = 24
    
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                .Columns("Sum 0 2 7").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
    
          For Each c As DataGridViewColumn In .Columns
            c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
          Next
          .DefaultCellStyle.BackColor = Color.PaleGoldenrod
          .DefaultCellStyle.Padding = New Padding(8, 2, 2, 8)
          .Columns("DateTime").DefaultCellStyle.Format = "dd MMM yyyy"
          With .Columns("Sum 0 2 7").DefaultCellStyle
            .BackColor = Color.PaleGreen
            .Format = "0.000"
            .Font = New Font("Arial", 14, FontStyle.Bold)
          End With
          .Columns("Sum 0 2 7").ReadOnly = True
          .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
        End With
        With Label1
          .Location = New Point(TextBox1.Right + 8, TextBox1.Top)
          .Font = New Font(.Font.FontFamily, 14, FontStyle.Bold)
          .BorderStyle = BorderStyle.FixedSingle
          .BackColor = Color.LightGreen
          .ForeColor = Color.Black
          .Text = " Found = 0 "
          .AutoSize = True
          .Anchor = AnchorStyles.Left Or AnchorStyles.Bottom
        End With
        Controls.Add(Label1)
        TextBox1.Select()
      End Sub
      'Private Sub DataGridView1_CellValidated(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
      '  Label3.Text = myTable.Compute("Sum(Integer)", Nothing)
      '  Label4.Text = myTable.Compute("Sum(Double)", Nothing)
      '  Label5.Text = myTable.Compute("Sum(Decimal)", Nothing)
      'End Sub
      Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        DoFilter(Trim(TextBox1.Text))
      End Sub
      Sub DoFilter(t As String)
        myTable.DefaultView.RowFilter = String.Empty
        If t.Length < 1 Then Exit Sub
    
        Dim fstring As String = Nothing
    
        If IsNumeric(t) Then
          For Each c As DataColumn In myTable.Columns
            If c.DataType Is GetType(Integer) Or c.DataType Is GetType(Double) Or c.DataType Is GetType(Decimal) Then
              fstring &= "[" & c.ColumnName & "] = " & t & " Or "
            End If
          Next
        Else
          For Each c As DataColumn In myTable.Columns
            If Not (c.DataType Is GetType(Integer) Or c.DataType Is GetType(Double) Or c.DataType Is GetType(Decimal)) Then
              fstring &= "[" & c.ColumnName & "] Like '" & t & "*' or "
            End If
          Next
        End If
        fstring = fstring.Substring(0, fstring.Length - 3)
        myTable.DefaultView.RowFilter = fstring
    
        If myTable.DefaultView.Count < 1 Then
          With Label1
            .BackColor = Color.Pink
            .Text = " No Matches "
          End With
          Exit Sub
        End If
    
        With Label1
          .BackColor = Color.PaleGreen
          .Text = " Found = " & myTable.DefaultView.Count.ToString & " "
        End With
      End Sub
    End Class
    
    No comments

0 additional answers

Sort by: Most helpful