Results 1 to 35 of 35

Thread: Subtract quantity of Datagridview1 from partDatgridview

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Subtract quantity of Datagridview1 from partDatgridview

    Hello,

    I have a PartsDatagridview where the table "Parts" is loaded" are 7.000" parts. I select a row and add it to Datagridvie1 and so on until I have all parts together for a customer. I save the collected parts in a .csv file. Later I load the .csv back into Datagridview1.
    Now I want an update to take place that subtracts the quantity per part from PartsDatagridview.
    Example:
    There are 10 pieces in the "Quantity" column in PartDatagridview.
    In Datgridview1 I change the quantity to 1 piece for the customer, now after the update in PartsDatagridview there should only be 9 pieces.
    Is that possible?
    Last edited by andi-aston-martin; Aug 10th, 2024 at 01:02 PM.

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Need more information.

    Where does the data in PartsDataGridView come from, how is it loaded.
    Is the data bound to PartsDataGridView.

    Subtracting one value from another is simple math, what are you having trouble with?
    You should post the relevant code.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi,
    PartDatagridview is bound with PartBindingSourche.
    Datagridview1 is an Unbound Datagridview .

    Code:
    Public Class Form1
    
    
    
        Private Sub TEILEBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles TEILEBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.TEILEBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TeileDataSet)
    
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            'TODO: Diese Codezeile lädt Daten in die Tabelle "TeileDataSet.TEILE". Sie können sie bei Bedarf verschieben oder entfernen.
            Me.TEILETableAdapter.Fill(Me.TeileDataSet.TEILE)
    
        End Sub
    
    
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'SaveFileDialog1.Filter = "CSV Files (*.csv*)|*.csv"
            SaveFileDialog1.FileName = TextBox1.Text
            SaveFileDialog1.Filter = "CSV Files (*.csv*)|*.csv"
    
            If SaveFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
    
                ' The DataGridView1.ClipboardCopyMode line ensures that the data stays in the correct column and row for saving without headers
    
                DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText
    
                DataGridView1.SelectAll()
    
                Try
                    IO.File.WriteAllText(SaveFileDialog1.FileName, DataGridView1.GetClipboardContent().GetText(TextDataFormat.CommaSeparatedValue))
                Catch ex As Exception
                    MessageBox.Show("This file is open in another application. Either change the filename or close the application using the file you're trying to update.")
                End Try
    
                DataGridView1.ClearSelection()
                DataGridView1.Rows.Clear()
            End If
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            OpenFileDialog1.Filter = "CSV Files (*.csv*)|*.csv"
            OpenFileDialog1.FileName = "*.*"
    
            If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
    
                DataGridView1.Rows.Clear()
    
                Dim TextFieldParser1 As New Microsoft.VisualBasic.FileIO.TextFieldParser(OpenFileDialog1.FileName)
    
                TextFieldParser1.Delimiters = New String() {","}
    
                While Not TextFieldParser1.EndOfData
                    Dim Row1 As String() = TextFieldParser1.ReadFields()
    
                    If DataGridView1.Columns.Count = 0 AndAlso Row1.Count > 0 Then
                        Dim i As Integer
    
                        For i = 0 To Row1.Count - 1
                            DataGridView1.Columns.Add("Column" & i + 1, "Column" & i + 1)
                        Next
                    End If
    
                    DataGridView1.Rows.Add(Row1)
                End While
    
            End If
        End Sub
    
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    
            'Ausgewählte Zeilen aus Datagridview1 nach Datagridview2 Kopieren
            DataGridView1.ColumnCount = 5 'Anzahl Spalten
            For Each dgvRow In TEILEDataGridView.SelectedRows
    
    
                ''''MsgBox(dgvRow.Cells(7).Value) ' Shows value form first column of each selected row
                Dim row3() As String = {dgvRow.Cells(1).Value.ToString(), dgvRow.Cells(8).Value.ToString(), dgvRow.Cells(3).Value.ToString(), dgvRow.Cells(7).Value.ToString() & " €", dgvRow.Cells(7).Value.ToString() * dgvRow.Cells(8).Value.ToString() & " €"}
                TextBox1.Text = dgvRow.Cells(7).Value.ToString() * dgvRow.Cells(6).Value.ToString()
    
    
                DataGridView1.Rows.Add(row3)
    
    
            Next
    
    
            If Me.TEILEDataGridView.CurrentCell IsNot Nothing Then
                Dim currentRow As Integer = Me.TEILEDataGridView.CurrentCell.RowIndex
    
                TEILEDataGridView.CurrentRow.Cells(6).Value = TEILEDataGridView.CurrentRow.Cells(6).Value - TEILEDataGridView.CurrentRow.Cells(8).Value
            End If
    
            If TEILEDataGridView.CurrentRow.Cells(6).Value = "1" Then
                TEILEDataGridView.CurrentRow.Cells(6).Style.BackColor = Color.Yellow
            Else
                If TEILEDataGridView.CurrentRow.Cells(6).Value = "0" Then
                    TEILEDataGridView.CurrentRow.Cells(6).Style.BackColor = Color.Red
                    TEILEDataGridView.CurrentRow.Cells(6).Style.ForeColor = Color.Yellow
    
                End If
            End If
    
    
        End Sub
    End Class

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    If Me.TelileDataSet.Teile has a PrimaryKey which is also in the DataGridView1 row you can use the Me.TelileDataSet.Teile.Rows.Find method to locate the matching record and then just do the math. https://learn.microsoft.com/en-us/do...d?view=net-8.0

    If there isn't a matching PrimaryKey but you do have a unique identifier then you can use the Me.TelileDataSet.Teile.Rows.Select method. https://www.vbforums.com/showthread....om-a-DataTable

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi,
    thanks for your answer, unfortunately I am still a beginner
    would you have an example code for me that I could try?

    Greetings Andi

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    I provided links for that. What don't you understand? What have you tried? Post your current code and I'll check it out.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi,
    As far as I understand it, your two links only refer to a datatable with the Find or Select method.
    The other is that these are only available in C# where I don't know anything about it, as I said I'm still a beginner, sorry.
    But thanks for your answer anyway.

    Here is a screenshot of the relationship
    Greetings AndiName:  Screenshot (2).jpg
Views: 150
Size:  18.6 KB
    Last edited by andi-aston-martin; Sep 7th, 2024 at 12:06 PM.

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,084

    Re: Subtract quantity of Datagridview1 from partDatgridview

    see if this helps, change to your needs for Parts Inventory

    Code:
    Option Strict On
    
    Public Class Form6
    
        Private Sub Form6_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            'Data
            With DataGridView1
                .Columns.Add("InvoiceId", "InvoiceId")
                .Columns.Add("Customer", "Customer")
                .Columns.Add("Invoice Amount", "Invoice Amount")
                .Columns.Add("Paid", "Paid")
                .Rows.Add(1, "A", 145.45, 0.0)
                .Rows.Add(2, "B", 132.16, 0.0)
                .Rows.Add(1, "A", 145.45, 5.25)
                .Rows.Add(3, "C", 123.51, 0.0)
                .Rows.Add(2, "B", 132.16, 4.45)
                .Rows.Add(3, "C", 123.78, 64.12)
                .Rows.Add(3, "C", 123.78, 8.78)
                .Rows.Add(1, "A", 145.45, 55.25)
            End With
            'for Payments:
            With DataGridView2
                .Columns.Add("InvoiceId", "InvoiceId")
                .Columns.Add("Customer", "Customer")
                .Columns.Add("Invoice Amount", "Invoice Amount")
                .Columns.Add("Paid", "Paid")
                .Columns.Add("Balance", "Balance")
            End With
        End Sub
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim nID = From row As DataGridViewRow In _
                  DataGridView1.Rows.Cast(Of DataGridViewRow)() _
                      Where Not row.IsNewRow _
                          Select (CInt(row.Cells(0).Value)) Distinct
    
            For Each n In nID
                Dim nCust As String = String.Empty
                Dim nAmount As Double = 0.0
                Dim nPaid As Double = 0.0
                Dim nBALANCE As Double = 0.0
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                    If CInt(DataGridView1.Rows(i).Cells(0).Value) = n Then
                        If DataGridView1.Rows(i).Cells(2).Value IsNot DBNull.Value Then
                            nCust = CStr(DataGridView1.Rows(i).Cells(1).Value)
                            nAmount = CDbl(DataGridView1.Rows(i).Cells(2).Value)
                            nPaid += CDbl(DataGridView1.Rows(i).Cells(3).Value)
                        End If
                    End If
                    nBALANCE = nAmount - nPaid
                Next
                DataGridView2.Rows.Add(n, nCust.ToString, nAmount.ToString("c"), nPaid.ToString("c"), nBALANCE.ToString("c"))
            Next
        End Sub
    End Class
    the results in DGV 2
    Name:  DGV.jpg
Views: 131
Size:  58.9 KB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    As far as I understand it, your two links only refer to a datatable with the Find or Select method.
    The other is that these are only available in C#
    I don't know why you think that, the links are .Net. The second link gives .Net examples.

    It look like you have a PrimaryKey so you can use the Find method. For Typed DataSets like your using it's actually the "FindBythePrimaryKeyFieldName"

    Here is another example, The PrimaryKey field is "LotId" so it uses the FindByLotId method.

    Code:
    Public Class Form6
        Private Sub LotsBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles LotsBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.LotsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.WaterDataSet)
    
        End Sub
    
        Private Sub Form6_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'WaterDataSet.Lots' table. You can move, or remove it, as needed.
            Me.LotsTableAdapter.Fill(Me.WaterDataSet.Lots)
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            'The PrimaryKey for this table is the LotId field
            Dim row = Me.WaterDataSet.Lots.FindBylotid("AD100")
            MessageBox.Show(row.Item("GrossAcres").ToString)
    
            'This is how to use the Select method, it return an array of datarows
            Dim rowarr = Me.WaterDataSet.Lots.Select("ownerid='9BOG10'")
            For i = 0 To rowarr.Count - 1
                MessageBox.Show(row.Item("ownerid").ToString)
            Next
        End Sub
    End Class

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi ChrisE,

    a great calculation that you have sent me :-)
    Question :
    What is the function of the c “ nAmount.ToString(”c") ?
    How can I then update the
    database parts so that it gives me the stocks of
    Datagridview2 ?

    Greetings Andi

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,084

    Re: Subtract quantity of Datagridview1 from partDatgridview

    well the "c" (Currency) is explained in the Dokumentation
    a sample I found
    Code:
      Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Dim cost As Double = 1632.54
            MessageBox.Show(cost.ToString("C", New System.Globalization.CultureInfo("en-US")))
            MessageBox.Show(cost.ToString("C", New System.Globalization.CultureInfo("de-DE")))
        End Sub
    change the Culture and look.

    You have all the Data for Update(s) in a Loop, might not be the best choice if multiple Users are involved and want to update
    at the same time
    Code:
     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim nID = From row As DataGridViewRow In _
                  DataGridView1.Rows.Cast(Of DataGridViewRow)() _
                      Where Not row.IsNewRow _
                          Select (CInt(row.Cells(0).Value)) Distinct
    
            For Each n In nID
                Dim nCust As String = String.Empty
                Dim nAmount As Double = 0.0
                Dim nPaid As Double = 0.0
                Dim nBALANCE As Double = 0.0
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                    If CInt(DataGridView1.Rows(i).Cells(0).Value) = n Then
                        If DataGridView1.Rows(i).Cells(2).Value IsNot DBNull.Value Then
                            nCust = CStr(DataGridView1.Rows(i).Cells(1).Value)
                            nAmount = CDbl(DataGridView1.Rows(i).Cells(2).Value)
                            nPaid += CDbl(DataGridView1.Rows(i).Cells(3).Value)
                        End If
                    End If
                    nBALANCE = nAmount - nPaid
                Next
    
                DataGridView2.Rows.Add(n, nCust.ToString, nAmount.ToString("c"), nPaid.ToString("c"), nBALANCE.ToString("c"))
              
                'a simple SQL
                Dim sql As String = "UPDATE yourTable SET Balance=" & nBALANCE & " WHERE ID =" & n
                Debug.WriteLine(sql)
            Next
        End Sub
    this will return 3 Update Querys...
    Code:
    UPDATE yourTable SET Balance=84,95 WHERE ID =1
    UPDATE yourTable SET Balance=127,71 WHERE ID =2
    UPDATE yourTable SET Balance=50,88 WHERE ID =3
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi ChrisE,

    Sorry, but it does not update ?

    Dim sql As String = “UPDATE Table1 SET Balance =” & nBALANCE & “ WHERE Id =” & n
    Debug.WriteLine(sql)

    Debug.WriteLine doesn't give me any lines either ??

    where is the error ?

    Greetings Andi

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    You need to show your current relevant code. Just saying "it does not update" is to vague. What happens? Does it error?

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi wes4dbt,

    I used the code from ChrisE for testing 1 to 1, I did not change anything.
    I have only created a table1 with the same field names
    and data types. When I start the test program everything works
    all calculations are executed and displayed in DGV2.
    displayed.
    Only after the calculation the sql update should start only
    nothing happens, there is also no error message as if the
    line code of sql Update does not exist.

    Greetings Andi

  15. #15
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Dim sql As String = “UPDATE Table1 SET Balance =” & nBALANCE & “ WHERE Id =” & n
    Debug.WriteLine(sql)

    Debug.WriteLine doesn't give me any lines either ??
    The "Update" is never executed so nothing would change in Table1.

    The fact there is not line from the Debug command makes me believe the code isn't being executed. Have you debugged your code? Use the debugger to step through your code to see if that code is even being executed.

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi wes4dbt,

    how does this work with debugger code, I have never done this before.

    Greetings Andi

  17. #17
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Quote Originally Posted by andi-aston-martin View Post
    Hi wes4dbt,

    how does this work with debugger code, I have never done this before.

    Greetings Andi
    Then you need to learn. It will save you time, it's a very important tool.

    Just Google ".net how to use debugger"

    I would suggest setting a breakpoint at this line and then stepping through the code,
    Code:
            Dim nID = From row As DataGridViewRow In _
                  DataGridView1.Rows.Cast(Of DataGridViewRow)() _
                      Where Not row.IsNewRow _
                          Select (CInt(row.Cells(0).Value)) Distinct
    At each step you can use the mouse pointer to hover over the current line and se what the object values are.

  18. #18
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    btw - You need to post your code. Can't help without seeing current code.

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi wes4dbt,

    First of all, thank you very much for your help.
    I did the debugging, first with the breakpoint
    you suggested, no errors.
    Then breakpoint at sql update and Debug.WriteLine all
    without errors, even in the output window the data is all
    displayed correctly even with sql Update.
    Here is the code, as I said I copied it 1 to 1.

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim nID = From row As DataGridViewRow In _
    DataGridView1.Rows.Cast(Of DataGridViewRow)() _
    Where Not row.IsNewRow _
    Select (CInt(row.Cells(0).Value)) Distinct

    For Each n In nID
    Dim nCust As String = String.Empty
    Dim nAmount As Double = 0.0
    Dim nPaid As Double = 0.0
    Dim nBALANCE As Double = 0.0
    For i As Integer = 0 To DataGridView1.Rows.Count - 1
    If CInt(DataGridView1.Rows(i).Cells(0).Value) = n Then
    If DataGridView1.Rows(i).Cells(2).Value IsNot DBNull.Value Then
    nCust = CStr(DataGridView1.Rows(i).Cells(1).Value)
    nAmount = CDbl(DataGridView1.Rows(i).Cells(2).Value)
    nPaid += CDbl(DataGridView1.Rows(i).Cells(3).Value)
    End If
    End If
    nBALANCE = nAmount - nPaid
    Next

    DataGridView2.Rows.Add(n, nCust.ToString, nAmount.ToString("c"), nPaid.ToString("c"), nBALANCE.ToString("c"))

    'a simple SQL
    Dim sql As String = "UPDATE Table1 SET Balance=" & nBALANCE & " WHERE ID =" & n
    Debug.WriteLine(sql)
    Next
    End Sub
    Gruß Andi

  20. #20
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    If the Output Window is displaying displaying as predicted and your not getting any errors. Then what's the problem? That's all that code is designed to do.

    You should always post code using the code tags, not quote tags. Use the "#" tag, then then code will retain it's formatting. Much easier to read.

  21. #21

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi,

    ok, but how can I then carry out the update so that
    the data in table1 is updated ?
    Sorry I'm still a beginner when it comes to this.

    Greetings Andi

  22. #22
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    jmc has a code bank thread on that subject. https://www.vbforums.com/showthread....ses&highlight=

    But you are using a TableAdapter to retrieve the Parts data and your table seems to have a PrimaryKey(I think the image is very small) so there is no need to manually create an Update command. You can use the TableAdapter Update method.

    I'll put together an example of using the TableAdapter.Update and post back a little later. You should checkout the code back thread by jmc, you'll need to learn that.

  23. #23
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Code:
    Public Class Form6
        Private Sub LotsBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles LotsBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.LotsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.WaterDataSet)
    
        End Sub
    
        Private Sub Form6_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'WaterDataSet.Lots' table. You can move, or remove it, as needed.
            Me.LotsTableAdapter.Fill(Me.WaterDataSet.Lots)
    
            'Data
            With DataGridView1
                .Columns.Add("LotId", "Lot Id")
                .Columns.Add("Acres", "Acres")
    
                '.Columns.Add("Paid", "Paid")
                .Rows.Add("AC010", 1)
                .Rows.Add("AD200", 2)
                .Rows.Add("AD100", 3)
    
            End With
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
    
            Dim row As waterDataSet.LotsRow
    
            For i = 0 To Me.DataGridView1.Rows.Count - 1
                Dim dgvrow As DataGridViewRow = Me.DataGridView1.Rows(i)
                If (dgvrow IsNot Nothing) AndAlso (Not dgvrow.IsNewRow) Then
                    'The PrimaryKey for this table is the LotId field
                    row = WaterDataSet.Lots.FindBylotid(dgvrow.Cells(0).Value.ToString)
                    If row IsNot Nothing Then
                        row("netacres") = CDec(row("netacres")) - CDec(dgvrow.Cells(1).Value)
                    End If
                End If
    
            Next
    
        End Sub
    End Class
    In your case, you would need to change
    WaterDataSet to TeileDataSet
    Lots to your table name (my guess is TEILE)
    LotId to your primarykey field name
    netacres to your parts field name

    Then the database table should be updated when you click the save button on the binding navigator
    Last edited by wes4dbt; Sep 12th, 2024 at 03:33 PM.

  24. #24

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi wes4dbt,

    I have to thank you very much, it worked great.
    I made the changes as you said.
    I will do a few more test runs with about 40 parts “Rows”
    but it looks really great. A big thank you from me again :-)

    Greetings Andi

  25. #25

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi wes4dbt,

    question, I have a problem when I preassign a cell with the value “0” in the DGV, the error message appears:
    “ System.NullReferenceException: “The object reference was not set to an object instance.”

    Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(...) has returned Nothing. “ .

    If I enter the “0” manually, no error message appears.
    I don't know what the reason is, do you have an idea?

    Greetings Andi

  26. #26
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    I have no idea. Since you didn't provide the relevant code and indicate which line of the code is causing the error.

  27. #27

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi wes4dbt,

    the error message occurs in the following cell “Cells8” have it
    highlighted in bold.

    Code:
    Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click
    
    DataGridView3.ColumnCount = 10 'Number of columns
    For Each dgvRow In TEILEDataGridView.SelectedRows
    
     -> Dim row3() As String = {dgvRow.Cells(0).Value.ToString(), dgvRow.Cells(1).Value.ToString(), dgvRow.Cells(3).Value.ToString(), dgvRow.Cells(8).Value.ToString(), dgvRow.Cells(7).Value.ToString() & " €", dgvRow.Cells(7).Value.ToString() * dgvRow.Cells(8).Value.ToString() & " €", dgvRow.Cells(9).Value.ToString()}
    
    DataGridView3.Rows.Add(row3)
    Greetings Andi

  28. #28
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    This works fine for me (Option Strict set OFF, which you shouldn't do)

    Code:
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Me.DataGridView2.ColumnCount = 2
            For Each dgvRow In DataGridView1.SelectedRows
                Me.DataGridView2.Rows.Add({dgvRow.Cells(0).value.ToString, dgvRow.Cells(1).value.ToString})
            Next
        End Sub
    My guess is your referencing a cell that doesn't exist. When it errors, take the mouse and hover over each cell object in that line of code, one of them probably has a value of nothing. Probably the last one listed. What is the columncout of TEILEDataGridView?

    You sHould turn Option Strict On

    Then add this to your For Loop "For Each dgvRow As DataGridViewRow"

  29. #29
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Oh, I see the problem. Just remove the "ToString" if there is a chance of the cell being Nothing.

    Code:
    Me.DataGridView2.Rows.Add({dgvRow.Cells(0).Value, dgvRow.Cells(1).Value})
    Edit: a better way would be to just clone the rows. That way the properties of each cell would also be copied.

    Code:
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Me.DataGridView2.ColumnCount = 2
            For Each dgvRow As DataGridViewRow In DataGridView1.SelectedRows
                Dim row As DataGridViewRow = DirectCast(dgvRow.Clone, DataGridViewRow)
                For i = 0 To dgvRow.Cells.Count - 1
                    row.Cells(i).Value = dgvRow.Cells(i).Value
                Next
                Me.DataGridView2.Rows.Add(row)
            Next
        End Sub
    Last edited by wes4dbt; Sep 14th, 2024 at 04:22 PM.

  30. #30

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi wes4dbt,

    worked, removed ToString from Cells(8)
    and it works, for whatever reason.
    Thank you very much...

    Greetings Andi

  31. #31
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Quote Originally Posted by andi-aston-martin View Post
    Hi wes4dbt,

    worked, removed ToString from Cells(8)
    and it works, for whatever reason.
    Thank you very much...

    Greetings Andi
    Because if an object is Nothing, then you can't convert it to a string.

  32. #32

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi,
    yes, I understand that, but I have these Cells(8)
    with a 0 so that when starting DGV
    all are displayed with 0.
    But somehow the program doesn't recognize this 0, it's strange.

    Greetings Andi

  33. #33

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi,

    Once again I have a problem that I don't understand,
    I have in the DGV where I collect all parts 3 lines
    at the end before I export it to Word:

    Total 16.188,00 € “created in DGV with format C2”
    VAT 3.075,72 € “ ”
    Total 19.263,72 € “ ”

    In the DGV everything is calculated and displayed correctly with
    thousands, decimal places and €.

    Only when I export it to Word is this added:

    Total 16188 <- missing decimal place and thousands and €
    VAT 3075,72 <- missing thousands and €
    Total 19263,72 <- missing thousands and €

    I found the following code on the net but it doesn't seem to be correct.

    Code:
    Dim sum As Decimal = 0
    Dim gesamt As Decimal = 0
    Dim MwSt As Decimal = 0
    For i = 0 To Datagridview2.Rows.Count - 1
    If Datagridview2.Rows(i).Cells(4).Value IsNot DBNull.Value Then
    sum += Datagridview2.Rows(i).Cells(4).Value
    End If
    Next
    
    
    MwSt = Format(sum / 100 * 19%, "0.00" & "€")
    
    Datagridview2.Rows.Add("", "", "", "Summe" & " ", sum).ToString("0,00" & " €")
    Datagridview2.Rows.Add("", "", "", "MwSt" & " ", MwSt).ToString("0,00" & " €")
    Datagridview2.Rows.Add("", "", "", "Gesamt" & " ", sum + MwSt).ToString("0,00“ & " €")
    I have also tried different number formats without success.

    Where is the error?

    Greetings Andi

  34. #34
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,261

    Re: Subtract quantity of Datagridview1 from partDatgridview

    This is a different problem, you should start a new thread with a relevant title.

    It sounds like your saying the numeric values loose their formatting when you export them to Word. But the code you posted has nothing to do with exporting the data to word. So make sure to provide a full explanation and all relevant code.

  35. #35

    Thread Starter
    Junior Member
    Join Date
    Jul 2024
    Posts
    23

    Re: Subtract quantity of Datagridview1 from partDatgridview

    Hi wes4dbt,

    as already mentioned, I select the lines in Partial Datagridview
    and transfer them to Datagridview2 until I have them all together.
    In Datagridview2 I have added 3 lines at the end:
    Total, VAT and Total
    Total = Price * Quantity per part
    VAT = is clear
    Total = sum +VAT

    In Datagridview2 everything is displayed correctly only when exporting
    to Word.

    Here is the code from part datagridview to datagridview2:

    Code:
    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
    
    
        'Ausgewählte Zeilen aus Datagridview1 nach Datagridview2 Kopieren
        Datagridview2.ColumnCount = 5 'Anzahl Spalten
        For Each dgvRow In DataGridView1.SelectedRows
    
            Dim row3() As String = {dgvRow.Cells(1).Value.ToString(), dgvRow.Cells(6).Value.ToString(), dgvRow.Cells(3).Value.ToString(), dgvRow.Cells(7).Value.ToString() & " €", dgvRow.Cells(7).Value.ToString() * dgvRow.Cells(6).Value.ToString() & " €"}
            vWert.Text = dgvRow.Cells(7).Value.ToString() * dgvRow.Cells(6).Value.ToString()
    
    
            Datagridview2.Rows.Add(row3)
    
            Me.Datagridview2.Rows(Me.Datagridview2.Rows.Count - 1).Cells(0).Style.Alignment = DataGridViewContentAlignment.MiddleLeft
            Me.Datagridview2.Rows(Me.Datagridview2.Rows.Count - 1).Cells(1).Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            Me.Datagridview2.Rows(Me.Datagridview2.Rows.Count - 1).Cells(2).Style.Alignment = DataGridViewContentAlignment.MiddleLeft
            Me.Datagridview2.Rows(Me.Datagridview2.Rows.Count - 1).Cells(3).Style.Alignment = DataGridViewContentAlignment.MiddleRight
            Me.Datagridview2.Rows(Me.Datagridview2.Rows.Count - 1).Cells(4).Style.Alignment = DataGridViewContentAlignment.MiddleRight
        Next
    End Sub
    And here is the code for exporting to Word:

    Code:
        Private Sub Button7_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
            
            Try
    
                Dim sum As Decimal = 0
                Dim gesamt As Decimal = 0
                Dim MwSt As Decimal = 0
                For i = 0 To Datagridview2.Rows.Count - 1
                    If Datagridview2.Rows(i).Cells(4).Value IsNot DBNull.Value Then
                        sum += Datagridview2.Rows(i).Cells(4).Value
                    End If
                Next
    
    
                MwSt = Format(sum / 100 * 19%, "0.00" & "€")
    
                Datagridview2.Rows.Add("", "", "", "Summe" & " ", sum).ToString("0,00" & " €")
                Datagridview2.Rows.Add("", "", "", "MwSt" & " ", MwSt).ToString("0,00" & " €")
                Datagridview2.Rows.Add("", "", "", "Gesamt" & " ", sum + MwSt).ToString("0,00“ & " €")
    
    
                Dim objWord As Word.Application
            Dim objDoc As Word.Document
    objWord = CreateObject("Word.Application")
            objWord.Visible = True
    
            objDoc = objWord.Documents.Open(FileName:="C:\Users\andi-\Desktop\Angebot-leer.docx") ''''''Add
    
    
            objWord.ActiveDocument.Bookmarks("Name").Range.Text = KD_Name.Text
    
    'Insert a paragraph at the beginning of the document.
    Dim oPara1 As Word.Paragraph, oPara2 As Word.Paragraph
    Dim oPara3 As Word.Paragraph, oPara4 As Word.Paragraph
    Dim oPara5 As Word.Paragraph, oPara6 As Word.Paragraph
    Dim oPara7 As Word.Paragraph, oPara8 As Word.Paragraph
    
    
            oPara1 = objDoc.Content.Paragraphs.Add
    oPara1.Range.Text = KD_Anrede.Text ''''ANSPRECHTextBox.Text '"Angebot:"
    oPara1.Range.Font.Bold = False
    oPara1.Range.Font.Size = 12
    oPara1.Range.Font.Underline = False
    oPara1.Range.Font.Color = Word.WdColor.wdColorBlack
    oPara1.Format.SpaceAfter = 2    '24 pt spacing after paragraph.
    oPara1.Range.InsertParagraphAfter()
    
    
    'Insert a paragraph at the beginning of the document.
            oPara2 = objDoc.Content.Paragraphs.Add
    oPara2.Range.Text = KD_Name.Text & "  " & KD_Vorname.Text ''''Name und Vorname"
    oPara2.Range.Font.Bold = False
    oPara2.Range.Font.Size = 12
    oPara2.Range.Font.Underline = False
    oPara2.Range.Font.Color = Word.WdColor.wdColorBlack
    oPara2.Format.SpaceAfter = 2  '24 pt spacing after paragraph.
    oPara2.Range.InsertParagraphAfter()
    
    
    'Insert a paragraph at the beginning of the document.
    oPara3 = objDoc.Content.Paragraphs.Add
            oPara3.Range.Text = KD_Plz.Text & "  " & KD_Ort.Text ''''Postleitzahl und Ort"
    oPara3.Range.Font.Bold = False
    oPara3.Range.Font.Size = 12
    oPara3.Range.Font.Underline = False
    oPara3.Range.Font.Color = Word.WdColor.wdColorBlack
    oPara3.Format.SpaceAfter = 5    '24 pt spacing after paragraph.
    oPara3.Range.InsertParagraphAfter()
    
    
    'Insert a paragraph at the beginning of the document.
    oPara4 = objDoc.Content.Paragraphs.Add
    oPara4.Range.Text = KD_Strasse.Text ''''Strase"
            oPara4.Range.Font.Bold = False
            oPara4.Range.Font.Size = 12
    oPara4.Range.Font.Underline = False
    oPara4.Range.Font.Color = Word.WdColor.wdColorBlack
    oPara4.Format.SpaceAfter = 45  '24 pt spacing after paragraph.
    oPara4.Range.InsertParagraphAfter()
    
    
    'Insert a paragraph at the beginning of the document.
    oPara5 = objDoc.Content.Paragraphs.Add
    oPara5.Range.Text = "Hiermit können wir ihnen folgendes Angebot unterbreiten:" & "                          " & "Datum" & " " & Date.Today
            oPara5.Range.Font.Bold = True
    oPara5.Range.Font.Size = 13
    oPara5.Range.Font.Underline = False
    oPara5.Range.Font.Color = Word.WdColor.wdColorBlack
    oPara5.Format.SpaceAfter = 25    '24 pt spacing after paragraph.
    oPara5.Range.InsertParagraphAfter()
    
    
    'Insert a paragraph at the beginning of the document.
    oPara7 = objDoc.Content.Paragraphs.Add
    oPara7.Range.Text = "Angebots-Nr.; ???" ''''ANSPRECHTextBox.Text '"Angebot:"
    oPara7.Range.Font.Bold = True
    oPara7.Range.Font.Size = 13
    oPara7.Range.Font.Underline = False
            oPara7.Range.Font.Color = Word.WdColor.wdColorBlack
    oPara7.Format.SpaceAfter = 25    '24 pt spacing after paragraph.
    oPara7.Range.InsertParagraphAfter()
    
    
    
    'Insert a paragraph at the end of the document.
    '** \endofdoc is a predefined bookmark.
    oPara3 = objDoc.Content.Paragraphs.Add(objDoc.Bookmarks.Item("\endofdoc").Range)
    oPara3.Range.Font.Size = 13
    oPara2.Range.Font.Underline = False
                oPara3.Range.Text = "Artikel-Nr." & " " & "Menge" & "                   " & "Artikel-Bezeichnung" & "                                   " & "E-Preise" & "       " & "Gesamt" ''''ANSPRECHTextBox.Text
    oPara3.Range.Font.Bold = True
    oPara3.Format.SpaceAfter = 6
    oPara3.Range.Font.Color = Word.WdColor.wdColorBlack
    oPara3.Range.InsertParagraphAfter()
    
                Dim r As Integer, c As Integer
                Dim oTable As Word.Table = objDoc.Tables.Add(objDoc.Bookmarks.Item("\endofdoc").Range,
                Datagridview2.RowCount, Datagridview2.ColumnCount, Datagridview2.ColumnHeadersVisible = True)
    
    
    
                For r = 0 To Datagridview2.RowCount - 2
                    For c = 0 To Datagridview2.Columns.Count - 1
    
                        objDoc.Tables(1).Cell(r + 1, c + 1).Range.Text = Datagridview2(c, r).Value.ToString()
    
    
                    Next
                Next
                objDoc.Tables(1).Cell(r, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter
                objDoc.Tables(1).Cell(r, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft
                objDoc.Tables(1).Cell(r, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight
                objDoc.Tables(1).Cell(r, 5).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight
    
    
                objDoc.Tables(1).Columns(1).Width = 20
                        objDoc.Tables(1).Columns(2).Width = 36
                        objDoc.Tables(1).Columns(3).Width = 280
                        objDoc.Tables(1).Columns(4).Width = 70
                        objDoc.Tables(1).Columns(5).Width = 70
    
                Dim oCell As Cell
                oTable = objDoc.Tables(1)
                For Each oCell In oTable.Range.Cells
    
                    If oCell.RowIndex > 0 Then
    
    
                        Select Case oCell.ColumnIndex
                            Case 1
                                oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter
                                oCell.Range.Font.Color = WdColor.wdColorBrown
                                oCell.Range.Font.Bold = False
                                oCell.Range.Columns.Width = 65
    
    
                            Case 2
                                oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter
                                oCell.Range.Font.Color = WdColor.wdColorDarkYellow
                                oCell.Range.Font.Bold = False
    
                            Case 3
                                oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft
                                oCell.Range.Font.Color = WdColor.wdColorDarkTeal
                                oCell.Range.Font.Bold = False
    
    
    
                            Case 4
                                oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight
                                oCell.Range.Font.Color = WdColor.wdColorBrown
                                oCell.Range.Font.Bold = False
    
                            Case 5
                                oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight
                                oCell.Range.Font.Color = WdColor.wdColorRed
                                oCell.Range.Font.Bold = False
    
    
                        End Select
                    End If
                Next
    
    
    
                'neuen Absatz einfügen
                oPara6 = objDoc.Content.Paragraphs.Add(objDoc.Bookmarks.Item("\endofdoc").Range)
    
    
                'Insert another paragraph.
                oPara6 = objDoc.Content.Paragraphs.Add(objDoc.Bookmarks.Item("\endofdoc").Range)
                oPara6.Range.Font.Bold = False
                oPara6.Range.Text = "Das Angebot berücksichtigt den Austausch aller Verschleißteile, sollten die
    vorhandenen Bauteile (nach Absprache) noch Verwendbar sein, reduzieren sich
    selbstverständlich die Kosten für das Material.
    
    Die Ersatzteilpreise können je nach Wechselkurs oder Preisänderungen
    vom Lieferanten abweichen.
    Auf Grund des Brexits und der Zollformalitäten ergeben sich zzgl. Zoll und
    Bearbeitungsgebühren.
    
    Beschaffungs-/ Versicherungs- und Transportkosten sind bereits in den
    Preisen beinhaltet.
    
    Sollten im Rahmen der Demontage bzw. Kontrolle der einzelnen Baugruppen
    vorher nicht erkannte gravierende Mängel auftreten wird Ihnen ein
    gesonderter Kostenvoranschlag erstellt.
    
    An dieses freibleibende Angebot können wir uns 14 Tage gebunden halten.
    
    Wir hoffen Ihnen mit diesem Angebot zu entsprechen und würden uns freuen
    einen bestätigten Werkstattauftrag zu erhalten.
    
    
    
    
    
    Mit freundlichen Grüßen
    
    Norbert Hieber
    
    
    
    Verbindlichen Werkstattauftrag lt. beiliegendem Angebot 202306H erteilt:
    
    
    
    .....................   den….............       ................................
    Ort                   Datum                   Unterschrift"
    
    
    
    
            Catch ex As Exception
            End Try
    
        End Sub
    Greetings Andi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width