Click here to Skip to main content
14,698,679 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i want to calculate total amt and total tax according qty and price in datagrid view by selecting item code and entering qty in qty cell of datagrid i used below code it shows item name and price but could not calculate total price and total tax

please help me?

i have table name item
itemcode itemname qty price   tax   total amt    total tax
1         led tv   2   12000  12%

Private Sub DataGridView1_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
    If flag = False Then
        getneame(e.RowIndex, e.ColumnIndex, DataGridView1)
    End If   
End Sub

Private Sub getneame(ByVal intRow As Integer, ByVal intCol As Integer, ByRef dgvDetails As DataGridView)

    Try
        If intCol = 1 Then
            'ProductID combo box change
            Dim itemcod As String = CStr(dgvDetails.Rows(intRow).Cells(1).Value)

            Dim decPrice As Decimal
            Dim intRowCtr As Integer
            Dim rowProd As DataRow
            Dim strName As String = Nothing
           
            'Search the DataTable for the ProductID and update UnitPrice
            With ds.Tables(0)
                For intRowCtr = 0 To .Rows.Count - 1
                    rowProd = .Rows(intRowCtr)
                    If CStr(rowProd.Item(0)) = itemcod Then
                        a = CStr(rowProd.Item(1))
                        b = CDec(rowProd.Item(4))
                        decPrice = CDec(rowProd.Item(3))

Dim x, y As Decimal
                        x = CDec(rowProd.Item(5))
                        Dim z As Integer = CInt(dgvDetails1.Rows(intRow1).Cells(3).Value)
                        decPrice = CDec(rowProd.Item(3))

                        y = x * decPrice * z
                                                                                  
                        'Change UnitPrice value
                        With DataGridView1
                            .Rows(intRow).Cells(2).Value = a
                            .Rows(intRow).Cells(4).Value = decPrice
                            .Rows(intRow1).Cells(5).Value = y
                            Exit For
                        End With
                    End If
                Next intRowCtr
            End With
        End If
    Catch exc As Exception
        'MsgBox(exc.Message + exc.StackTrace, , exc.Source)
    End Try

End Sub
Posted
Updated 4-Nov-15 22:07pm
v2
Comments
Michael_Davies 5-Nov-15 3:08am
   
Please use names with meanings, someone else may have to read your code and it will help you to read it as well if variables are properly named so as to explain their function; a,b,x,y and z are all meaningless.

Presume flag is a global boolean and being used to prevent multiple cellvalueschanged entry because getneame will alter the value of cells and thus fire the cellvaluechanged event again whilst in a cellvaluechanged event, you do not set flag at any point so what is its purpose?

Where are intRow1 and ds declared, again proper names to describe them.

1 solution

Assuming that DataGridView.DataSource[^] is DataTable[^], i'd suggest to calculate tax and total amt using Linq To Dataset[^]:

Dim dt As DataTable = DataGridView1.DataSource
Dim id = dgvDetails.Rows(e.RowIndex).Cells(1).Value
Dim totaltax = dt.AsEnumerable() _
        .Where(Function(item) item.Field(Of Integer)("itemcode")=id) _
        .Select(Function(item) item.Field(Of Integer)("qty") * _
            item.Field(Of Decimal)("price") * _
            item.Field(Of Decimal)("tax")).SingleOrDefault()

Dim totalamt = dt.AsEnumerable() _
        .Where(Function(item) item.Field(Of Integer)("itemcode")=id) _
        .Select(Function(item) item.Field(Of Decimal)("price") + totaltax).SingleOrDefault()

With DataGridView1
    .Rows(e.RowIndex).Cells(6).Value = totalamt
    .Rows(e.RowIndex).Cells(7).Value = totaltax
End With


For further information, please see:
LINQ to DataSet Examples[^]
Querying DataSets (LINQ to DataSet)[^]
Queries in LINQ to DataSet[^]

Complete example:
Dim dt As DataTable = New DataTable()
dt.Columns.Add(New DataColumn("itemcode", GetType(Integer)))
dt.Columns.Add(New DataColumn("itemname", GetType(String)))
dt.Columns.Add(New DataColumn("qty", GetType(Integer)))
dt.Columns.Add(New DataColumn("price", GetType(Decimal)))
dt.Columns.Add(New DataColumn("tax", GetType(Decimal)))
dt.Columns.Add(New DataColumn("total amt", GetType(Decimal)))
dt.Columns.Add(New DataColumn("total tax", GetType(Decimal)))

dt.Rows.Add(New Object(){1, "led tv", 2, 12000, 0.12, DBNull.Value, DBNull.Value})

Dim id = 1
Dim totaltax = dt.AsEnumerable() _
        .Where(Function(item) item.Field(Of Integer)("itemcode")=id) _
        .Select(Function(item) item.Field(Of Integer)("qty") * _
            item.Field(Of Decimal)("price") * _
            item.Field(Of Decimal)("tax")).SingleOrDefault()

Dim totalamt = dt.AsEnumerable() _
        .Where(Function(item) item.Field(Of Integer)("itemcode")=id) _
        .Select(Function(item) item.Field(Of Decimal)("price") + totaltax).SingleOrDefault()

Console.WriteLine("tax: {0} total: {1}", totaltax, totalamt)

Returns:
tax: 2880.00 total: 14880.00
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900