Subtract quantity of Datagridview1 from partDatgridview

[andi-aston-martin]

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?

[wes4dbt]

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.

[andi-aston-martin]

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

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

[wes4dbt]

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

[andi-aston-martin]

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

[wes4dbt]

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

[andi-aston-martin]

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 Andi

[ChrisE]

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

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

[wes4dbt]

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.

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

[andi-aston-martin]

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

[ChrisE]

well the "c" (Currency) is explained in the Dokumentation
a sample I found

  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

 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...

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

[andi-aston-martin]

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

[wes4dbt]

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

[andi-aston-martin]

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

[wes4dbt]

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.

[andi-aston-martin]

Hi wes4dbt,

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

Greetings Andi

[wes4dbt]

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,

        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.

[wes4dbt]

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

[andi-aston-martin]

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

[wes4dbt]

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.

[andi-aston-martin]

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

[wes4dbt]

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.

[wes4dbt]

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

[andi-aston-martin]

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

[andi-aston-martin]

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

[wes4dbt]

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

[andi-aston-martin]

Hi wes4dbt,

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

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

[wes4dbt]

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

    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"

[wes4dbt]

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

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.

    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

[andi-aston-martin]

Hi wes4dbt,

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

Greetings Andi

[wes4dbt]

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.

[andi-aston-martin]

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

[andi-aston-martin]

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.

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

[wes4dbt]

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.

[andi-aston-martin]

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:

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:

    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