Update a products table only for products showing in a filtered datagridview

Claude Larocque 666 Reputation points
2021-01-25T17:08:04.727+00:00

I have a form named FrmDiscount the OnLoad show the first product of the table with my LoadFirstRecord function. I have a textbox named TxtFilterTB.Text and on KeyUp I filter the datagridview as I wish. At this step everything works perfectly.
I want to be able to update only the product showing after filtering the datagridview, with my code so far only the first product is updated.

60257-frmdiscount.jpg
Imports System.Data
Imports System.Text
Imports System.Runtime
Imports System.IO
Imports System.Windows.Forms
Imports System.Security.Cryptography
Imports System.Data.SqlClient
Public Class FrmDiscount
Private SQL As New SQLControl
Public Property SearchPass As String
Private Sub LoadGrid(Optional Query As String = "")
Try
If Query = "" Then
SQL.ExecQuery("SELECT ProductID,ProductName,Barcode,Category,SellingPrice1,DiscountRate,DiscountAmount,ValidFrom,ValidTo,ApplyDiscount FROM Warehouse.ProductsActivated")
Else
SQL.ExecQuery(Query)
End If
'If Data Is RETURNED Then POPULATE GRID & BUILD UPDATE COMMAND
If SQL.RecordCount > 0 Then
DiscountProductsDGV.DataSource = SQL.DBDT
DiscountProductsDGV.Rows(0).Selected = True
DiscountProductsDGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
Me.RecordsCountTB.Text = SQL.DBDT.Rows.Count.ToString
End If
'ERROR HANDLING
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
System.IO.File.AppendAllText("C:\ACRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)
End Try
End Sub
Private Sub BtnChangeDiscountRate_Click(sender As Object, e As EventArgs) Handles BtnChangeDiscountRate.Click
Dim FormACCalculator As New FrmACCalculator
FormACCalculator.Show()
FormACCalculator.BtnEqual.Text = "CHANGE DISCOUNT RATE"
End Sub

    Private Sub BtnChangeDiscountAmount_Click(sender As Object, e As EventArgs) Handles BtnChangeDiscountAmount.Click  
        Dim FormACCalculator As New FrmACCalculator  
        FormACCalculator.Show()  
        FormACCalculator.BtnEqual.Text = "CHANGE DISCOUNT AMOUNT"  
    End Sub  
    Public Sub LoadFirstRecord()  
        Try  
            'CLEAR EXISTING RECORD  
            If SQL.DBDS IsNot Nothing Then  
                SQL.DBDS.Clear()  
            End If  
            SQL.RunQuery("SELECT * FROM Warehouse.ProductsActivated Where ProductID = 1")  
            If SQL.DBDS.Tables(0).Rows(0).Item(0) >= 1 Then  
                Me.ProductIDTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ProductID")  
                Me.ProductNameTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ProductName")  
                Me.BarcodeTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("Barcode")  
                Me.CategoryTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("Category")  
                Me.SellingPrice1TB.Text = SQL.DBDS.Tables(0).Rows(0).Item("SellingPrice1")  
                Me.ValidFromTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ValidFrom")  
                Me.ValidToTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ValidTo")  
                Me.DiscountRateTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("DiscountRate")  
                Me.DiscountAmountTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("DiscountAmount")  
                Me.ApplyToAllCB.Checked = SQL.DBDS.Tables(0).Rows(0).Item("ApplyDiscount")  
            Else  
                MsgBox("There is no Product 1, please ask the system administrator!", MsgBoxStyle.Critical, "NO RECORD FOUND")  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
  
        End Try  
    End Sub  
  
    Private Sub FrmDiscount_Load(sender As Object, e As EventArgs) Handles MyBase.Load  
        'TODO: cette ligne de code charge les données dans la table 'ACDataDS.ProductsActivated'. Vous pouvez la déplacer ou la supprimer selon les besoins.  
        LoadGrid()  
        LoadFirstRecord()  
        DiscountProductsDGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect  
        Me.ProgressBar1.Visible = False  
        Me.TxtFilterTB.Select()  
    End Sub  
    Private Function PopulateDataGridView() As DataTable  
        Dim query As String = "SELECT ProductID,ProductName,Barcode,Category,SellingPrice1,DiscountRate,DiscountAmount,ValidFrom,ValidTo,ApplyDiscount FROM Warehouse.ProductsActivated"  
        query &= " WHERE ProductID LIKE '%' + @SearchTerm + '%'"  
        query &= " OR ProductName LIKE '%' + @SearchTerm + '%'"  
        query &= " OR Barcode LIKE '%' + @SearchTerm + '%'"  
        query &= " OR Category LIKE '%' + @SearchTerm + '%'"  
        query &= " OR SellingPrice1 LIKE '%' + @SearchTerm + '%'"  
        Dim constr As String = "Server=S2-\192.168.0.133,1433;database=ACData;User=Admin;Pwd=mANONbENOIT1954;"  
        Using DBCon As SqlConnection = New SqlConnection(constr)  
            Using DBCmd As SqlCommand = New SqlCommand(query, DBCon)  
                DBCmd.Parameters.AddWithValue("@SearchTerm", TxtFilterTB.Text.Trim())  
                Using DBDA As SqlDataAdapter = New SqlDataAdapter(DBCmd)  
                    Dim DBDT As DataTable = New DataTable()  
                    DBDA.Fill(DBDT)  
                    DiscountProductsDGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells  
                    Me.RecordsCountTB.Text = DBDT.Rows.Count.ToString  
                    Return DBDT  
                End Using  
            End Using  
        End Using  
    End Function  
  
    Private Sub TxtFilterTB_KeyUp(sender As Object, e As KeyEventArgs) Handles TxtFilterTB.KeyUp  
        DiscountProductsDGV.DataSource = Me.PopulateDataGridView()  
    End Sub  
  
    Private Sub BtnClose_Click(sender As Object, e As EventArgs) Handles BtnClose.Click  
        Me.Close()  
    End Sub  
  
    Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles BtnUpdate.Click  
        Try  
            If SQL.DBDS IsNot Nothing Then  
                SQL.DBDS.Clear()  
            End If  
            If ApplyToAllCB.Checked = True Then  
                Me.ProgressBar1.Visible = True  
                SQL.RunQuery("SELECT ProductID, ProductName, Barcode, Category, SellingPrice1, DiscountRate, DiscountAmount, ValidFrom, ValidTo, ApplyDiscount FROM Warehouse.Products")  
                ProgressBar1.Maximum = SQL.DBDS.Tables(0).Rows.Count  
                For i As Integer = 0 To SQL.DBDS.Tables(0).Rows.Count - 1  
                    SQL.AddParam("@ProductID", ProductIDTB.Text)  
                    SQL.AddParam("@ProductName", ProductNameTB.Text)  
                    SQL.AddParam("@Barcode", BarcodeTB.Text)  
                    SQL.AddParam("@Category", CategoryTB.Text)  
                    SQL.AddParam("@SellingPrice1", SellingPrice1TB.Text)  
                    SQL.AddParam("@DiscountRate", DiscountRateTB.Text)  
                    SQL.AddParam("@DiscountAmount", DiscountAmountTB.Text)  
                    SQL.AddParam("@ValidFrom", ValidFromTB.Text)  
                    SQL.AddParam("@ValidTo", ValidToTB.Text)  
                    SQL.AddParam("@ApplyDiscount", True)  
                    SQL.ExecQuery("UPDATE Warehouse.Products " &  
                          "SET ProductName=@ProductName,Barcode=@Barcode,Category=@Category,SellingPrice1=@SellingPrice1,DiscountRate=@DiscountRate, " &  
                          "DiscountAmount=@DiscountAmount,ValidFrom=@ValidFrom,ValidTo=@ValidTo,ApplyDiscount=@ApplyDiscount WHERE ProductID=@ProductID;")  
                    ProgressBar1.Value = i + 1  
                Next  
                Me.BtnRefresh.PerformClick()  
            End If  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\ACRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
  
    Private Sub BtnRefresh_Click(sender As Object, e As EventArgs) Handles BtnRefresh.Click  
        Try  
            Me.Controls.Clear()  
            InitializeComponent()  
            FrmDiscount_Load(e, e)  
            If SQL.HasException(True) Then Exit Sub  
        Catch ex As Exception  
            MsgBox(ex.Message)  
            System.IO.File.AppendAllText("C:\ACRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)  
        End Try  
    End Sub  
End Class  
Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,854 questions
0 comments No comments
{count} votes

Accepted answer
  1. Karen Payne MVP 35,201 Reputation points
    2021-01-25T22:17:46.743+00:00

    Hello,

    The first thing to check is what is being returned by the following. I don't know the method but when performing updates say from a command object or with Entity Framework SaveChanges, both return rows affected. So if you are using under the covers ExecuteNonQuery, check the return value.

    60356-11111111111.png

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Claude Larocque 666 Reputation points
    2021-02-01T12:38:59.033+00:00

    Good morning,
    Here is the corrected code and it works perfectly, thanks Karen to put me in the right direction. I put the code to help someone in the future.

    Imports System.Data
    Imports System.Data.DataTable
    Imports System.Text
    Imports System.Runtime
    Imports System.IO
    Imports System.Windows.Forms
    Imports System.Security.Cryptography
    Imports System.Data.SqlClient
    Public Class FrmDiscount
    Private SQL As New SQLControl
    Public Property SearchPass As String
    Private Sub LoadGrid(Optional Query As String = "")
    Try
    If Query = "" Then
    SQL.ExecQuery("SELECT ProductID,ProductName,Barcode,Category,SellingPrice1,DiscountRate,DiscountAmount,ValidFrom,ValidTo,ApplyDiscount FROM Warehouse.ProductsActivated")
    Else
    SQL.ExecQuery(Query)
    End If
    'If Data Is RETURNED Then POPULATE GRID & BUILD UPDATE COMMAND
    If SQL.RecordCount > 0 Then
    DiscountProductsDGV.DataSource = SQL.DBDT
    DiscountProductsDGV.Rows(0).Selected = True
    DiscountProductsDGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
    Me.RecordsCountTB.Text = SQL.DBDT.Rows.Count.ToString
    DiscountProductsDGV.AllowUserToAddRows = False
    End If
    'ERROR HANDLING
    If SQL.HasException(True) Then Exit Sub
    Catch ex As Exception
    MsgBox(ex.Message)
    System.IO.File.AppendAllText("C:\ACRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)
    End Try
    End Sub
    Private Sub BtnChangeDiscountRate_Click(sender As Object, e As EventArgs) Handles BtnChangeDiscountRate.Click
    Dim FormACCalculator As New FrmACCalculator
    FormACCalculator.Show()
    FormACCalculator.BtnEqual.Text = "CHANGE DISCOUNT RATE"
    End Sub

    Private Sub BtnChangeDiscountAmount_Click(sender As Object, e As EventArgs) Handles BtnChangeDiscountAmount.Click
        Dim FormACCalculator As New FrmACCalculator
        FormACCalculator.Show()
        FormACCalculator.BtnEqual.Text = "CHANGE DISCOUNT AMOUNT"
    End Sub
    
    Private Sub FrmDiscount_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: cette ligne de code charge les données dans la table 'ACDataDS.ProductsActivated'. Vous pouvez la déplacer ou la supprimer selon les besoins.
        LoadGrid()
        Me.DiscountRateTB.Text = 0
        Me.DiscountAmountTB.Text = 0
        Me.ApplyToAllCB.Checked = True
        DiscountProductsDGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        Me.TxtFilterTB.Select()
    End Sub
    Private Function PopulateDataGridView() As DataTable
        Dim query As String = "SELECT ProductID,ProductName,Barcode,Category,SellingPrice1,DiscountRate,DiscountAmount,ValidFrom,ValidTo,ApplyDiscount FROM Warehouse.ProductsActivated"
        query &= " WHERE ProductID LIKE '%' + @SearchTerm + '%'"
        query &= " OR ProductName LIKE '%' + @SearchTerm + '%'"
        query &= " OR Barcode LIKE '%' + @SearchTerm + '%'"
        query &= " OR Category LIKE '%' + @SearchTerm + '%'"
        query &= " OR SellingPrice1 LIKE '%' + @SearchTerm + '%'"
        Dim constr As String = "Server=S2-\192.168.0.133,1433;database=ACData;Integrated Security=SSPI;"
        Using DBCon As SqlConnection = New SqlConnection(constr)
            Using DBCmd As SqlCommand = New SqlCommand(query, DBCon)
                DBCmd.Parameters.AddWithValue("@SearchTerm", TxtFilterTB.Text.Trim())
                Using DBDA As SqlDataAdapter = New SqlDataAdapter(DBCmd)
                    Dim DBDT As DataTable = New DataTable()
                    DBDA.Fill(DBDT)
                    DiscountProductsDGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                    Me.RecordsCountTB.Text = DBDT.Rows.Count.ToString
                    Return DBDT
                End Using
            End Using
        End Using
    End Function
    
    Private Sub TxtFilterTB_KeyUp(sender As Object, e As KeyEventArgs) Handles TxtFilterTB.KeyUp
        DiscountProductsDGV.DataSource = Me.PopulateDataGridView()
    End Sub
    
    Private Sub BtnClose_Click(sender As Object, e As EventArgs) Handles BtnClose.Click
        Me.Close()
    End Sub
    
    Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles BtnUpdate.Click
        Try
            If Me.CheckAllCB.Checked = False Then
                MessageBox.Show("To activate the UPDATE button you must check the box (1.)!" & vbCrLf & "You must also enter a percentage or a dollar amount in the appropriate fields!",
            "Auto Cash Register Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                Exit Sub
            End If
            If Me.CheckAllCB.Checked = True And Me.DiscountRateTB.Text = 0 And Me.DiscountAmountTB.Text = 0 Then
                MessageBox.Show("You must also enter a percentage or a dollar amount in the appropriate fields!!" & vbCrLf & "Please do so!",
            "Auto Cash Register Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                Exit Sub
            End If
            If Me.ValidFromTB.Text > Me.ValidToTB.Text Then
                MessageBox.Show("Valid To MUST BE HIGHER than Valid From!!" & vbCrLf & "Please correct!",
            "Auto Cash Register Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                Exit Sub
            End If
    
            BtnSaveApplyDiscount.PerformClick()
            Dim Connection As New SqlConnection("Server=S2-\192.168.0.133,1433;database=ACData;Integrated Security=SSPI;")
            Dim Command As New SqlCommand With {
                .Connection = Connection,
                .CommandType = CommandType.StoredProcedure,
                .CommandText = "Warehouse.UpdateDiscountData"}
            Connection.Open()
            Command.ExecuteNonQuery()
            Connection.Close()
            BtnRefresh.PerformClick()
            ShowFrmMsgSmallerSaved()
            'ERROR HANDLING
            If SQL.HasException(True) Then Exit Sub
        Catch ex As Exception
            MsgBox(ex.Message)
            System.IO.File.AppendAllText("C:\ACRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)
        End Try
    End Sub
    
    Private Sub BtnRefresh_Click(sender As Object, e As EventArgs) Handles BtnRefresh.Click
        Try
            Me.Controls.Clear()
            InitializeComponent()
            FrmDiscount_Load(e, e)
            If SQL.HasException(True) Then Exit Sub
        Catch ex As Exception
            MsgBox(ex.Message)
            System.IO.File.AppendAllText("C:\ACRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)
        End Try
    End Sub
    
    Private Sub BtnSaveApplyDiscount_Click(sender As Object, e As EventArgs) Handles BtnSaveApplyDiscount.Click
        SQL.RunQuery("DELETE FROM Warehouse.ProductsDiscountTemp")
        Try
            For Each row As DataGridViewRow In DiscountProductsDGV.Rows
                Dim constring As String = "Server=S2-\192.168.0.133,1433;database=ACData;Integrated Security=SSPI;"
                Using DBCon As New SqlConnection(constring)
                    Using DBCmd As New SqlCommand("INSERT INTO Warehouse.ProductsDiscountTemp VALUES(@ProductID,@ProductName,@Barcode,@Category,@SellingPrice1,@ApplyDiscount,@DiscountRate,@DiscountAmount,@ValidFrom,@ValidTo)", DBCon)
                        DBCmd.Parameters.AddWithValue("@ProductID", row.Cells("ProductID").Value)
                        DBCmd.Parameters.AddWithValue("@ProductName", row.Cells("ProductName").Value)
                        DBCmd.Parameters.AddWithValue("@Barcode", row.Cells("Barcode").Value)
                        DBCmd.Parameters.AddWithValue("@Category", row.Cells("Category").Value)
                        DBCmd.Parameters.AddWithValue("@SellingPrice1", row.Cells("SellingPrice1").Value)
                        DBCmd.Parameters.AddWithValue("@ApplyDiscount", CheckAllCB.Checked)
                        DBCmd.Parameters.AddWithValue("@DiscountRate", DiscountRateTB.Text)
                        DBCmd.Parameters.AddWithValue("@DiscountAmount", DiscountAmountTB.Text)
                        DBCmd.Parameters.AddWithValue("@ValidFrom", ValidFromTB.Text)
                        DBCmd.Parameters.AddWithValue("@ValidTo", ValidToTB.Text)
                        DBCon.Open()
                        DBCmd.ExecuteNonQuery()
                    End Using
                End Using
            Next
            If SQL.HasException(True) Then Exit Sub
        Catch ex As Exception
            MsgBox(ex.Message)
            System.IO.File.AppendAllText("C:\ACRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)
        End Try
    End Sub
    Private Sub ShowFrmMsgSmallerSaved()
        Try
            Dim SmallerMsg As New FrmMsgSmaller
            FrmMsgSmaller.Close()
            FrmMsgSmaller.TextBox1.Text = ""
            FrmMsgSmaller.TextBox1.Text = "YOUR CHOICES HAVE BEEN UPDATED IN THE PRODUCTS TABLE!"
            FrmMsgSmaller.Show()
            Dim SW2 As New Stopwatch
            SW2.Start()
            Do
                Application.DoEvents()
            Loop Until SW2.ElapsedMilliseconds >= 2000
            FrmMsgSmaller.Hide()
            FrmMsgSmaller.Close()
            If SQL.HasException(True) Then Exit Sub
        Catch ex As Exception
            MsgBox(ex.Message)
            System.IO.File.AppendAllText("C:\ACRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)
        End Try
    End Sub
    
    Private Sub BtnCheckAll_Click(sender As Object, e As EventArgs)
        For Each row As DataGridViewRow In Me.DiscountProductsDGV.Rows
            row.Cells(9).Value = True
        Next row
    End Sub
    
    Private Sub BtnResetAllDiscount_Click(sender As Object, e As EventArgs) Handles BtnResetAllDiscount.Click
        Try
            If MsgBox("This will delete all previous programmed discount(s) configuration, Are you sure?", MsgBoxStyle.YesNo, "Reset all products discount?") = vbYes Then
                Dim Connection As New SqlConnection("Server=S2-\192.168.0.133,1433;database=ACData;Integrated Security=SSPI;")
                Dim Command As New SqlCommand With {
                .Connection = Connection,
                .CommandType = CommandType.StoredProcedure,
                .CommandText = "Warehouse.ResetAllDiscount"}
                Connection.Open()
                Command.ExecuteNonQuery()
                Connection.Close()
                BtnRefresh.PerformClick()
            End If
            'ERROR HANDLING
            If SQL.HasException(True) Then Exit Sub
        Catch ex As Exception
            MsgBox(ex.Message)
            System.IO.File.AppendAllText("C:\ACRegister\Documents\log.txt", ex.ToString & vbNewLine & vbNewLine)
        End Try
    End Sub
    
    Private Sub DiscountProductsDGV_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DiscountProductsDGV.CellClick
        If e.ColumnIndex = 9 Then
            CheckBoxesCheckedTB.Text = ((e.RowIndex + 1).ToString())
        End If
    End Sub
    
    Private Sub ProductsDiscountSP_Click(sender As Object, e As EventArgs) Handles ProductsDiscountSP.Click
        Dim David
        David = CreateObject("SAPI.spvoice")
        David.Voice = David.GetVoices.Item(2)
        David.Rate = 0
        David.Volume = 100
        'Pass the content of the Rich Text Box to voice.speak so the hidden text box goes off and Speaks
        David.Speak(DiscountRTB.Text)
    End Sub
    

    End Class

    0 comments No comments