Subtract the quantity of partsDGV <- bound from DGV3 <- unbound

[astonmartin]

Hello,

I have an Access database Parts.accdb with table “Parts”
In Form1 I have a PartsDatagridview which is bound with “PartsBindingSource”.
With a search I collect all parts that I need for a quotation
and transfer it to DGV3 “Unbound DGV”.
Save the collected parts of DGV3 in a .csv file because it can happen that I have to remove a part again
if the customer thinks we can postpone it until later.
Load the .csv file into DGV3 again and change the offer.
An invoice is only created once the new quotation has been confirmed.
into DGV3 again, and now the quantity per part from column “Quantity” in DGV3
from PartsDatagridview column “INGMG” and an update of the table “Parts” is to be carried out.

Table “Parts” ID = primary key, IDTeileNR = is indexed (without duplicates) as this must be unique.

Example:

PartsDatagridview column” INGMG ” stock is 8 pieces
DGV1 column “Quantity” 2 pieces
INGMG - quantity = 8 pieces

Should be possible with an sql update or am I wrong?
Can you help me, I just can't get it right.

Greetings Andi

 

[jmcilhinney]

Is this issue really resolved? If so, it is considered good form to provide your solution and mark it as the solution, so it might help others with similar issues. If not, you should click the 'Unresolved' button above your post, if it's visible for you. If that button is not there (not sure whether it is for new members) let me know and I'll do it for you.

 

[astonmartin]

Is this issue really resolved? If so, it is considered good form to provide your solution and mark it as the solution, so it might help others with similar issues. If not, you should click the 'Unresolved' button above your post, if it's visible for you. If that button is not there (not sure whether it is for new members) let me know and I'll do it for you.

Hi,

thanks for the hint, was a mistake is still unsolved
I have changed it.

Greetings Andi

 

[jdelano]

You might find it easier to put the "pre-quote" data in a table in Access instead of the csv file. Then you can create a relationship between the pre-quote table and the parts table to update the quantity and use an insert into statement to move the data from the pre-quote table to the table that holds actual quotes sent to the customer.

Also, uploading a sample database would be helpful to see your complete table definitions and how you're currently moving data around.

 

[Rythorian]

I wrote this up for you last night, I had to study some things a long the way but I trust it will get you in the right direction:

# Parts Management System

Imports System.Data.OleDb
Imports System.IO
Imports System.Text

Public Class Form1
    Private connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Parts.accdb;"
    Private partsDataSet As New DataSet()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadParts()
    End Sub

    Private Sub LoadParts()
        Using connection As New OleDbConnection(connectionString)
            Dim adapter As New OleDbDataAdapter("SELECT * FROM Parts", connection)
            adapter.Fill(partsDataSet, "Parts")
            PartsBindingSource.DataSource = partsDataSet.Tables("Parts")
            PartsDatagridview.DataSource = PartsBindingSource
        End Using
    End Sub

    Private Sub SaveToCSV()
        Dim sb As New StringBuilder()
        Dim headers = PartsDatagridview.Columns.Cast(Of DataGridViewColumn)().Select(Function(column) column.HeaderText)
        sb.AppendLine(String.Join(",", headers))

        For Each row As DataGridViewRow In DGV3.Rows
            Dim cells = row.Cells.Cast(Of DataGridViewCell)().Select(Function(cell) cell.Value.ToString())
            sb.AppendLine(String.Join(",", cells))
        Next

        File.WriteAllText("Parts.csv", sb.ToString())
    End Sub

    Private Sub LoadFromCSV()
        Dim lines = File.ReadAllLines("Parts.csv")
        DGV3.Rows.Clear()

        For Each line In lines.Skip(1)
            Dim values = line.Split(","c)
            DGV3.Rows.Add(values)
        Next
    End Sub

    Private Sub UpdateParts()
        Using connection As New OleDbConnection(connectionString)
            connection.Open()
            For Each row As DataGridViewRow In DGV3.Rows
                If Not row.IsNewRow Then
                    Dim id = row.Cells("ID").Value
                    Dim quantity = row.Cells("Quantity").Value
                    Dim command As New OleDbCommand("UPDATE Parts SET INGMG = @quantity WHERE ID = @id", connection)
                    command.Parameters.AddWithValue("@quantity", quantity)
                    command.Parameters.AddWithValue("@id", id)
                    command.ExecuteNonQuery()
                End If
            Next
        End Using
    End Sub
End Class