Share via

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,873 questions
0 comments
Accepted answer
  1. Karen Payne MVP 35,386 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

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  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