Solved

Total sum (discount formula) needed vb 2005

Posted on 2009-05-18
27
748 Views
Last Modified: 2013-11-27
Hello experts i have a problem.i have an order form and i have calculate the total sum  of each order.what  i want to do is i must add the textbox discount to the  datagrdiview and insert the formula for the total sum.If exist a discount to minus the discount if not nothing happend

 above is the code of the total sum   and row  and the code of adding  the textboxes value to the datagridview..

Regards
demoniumz
Private Sub TotalSum()
        Dim sum As Decimal = 0
        For Each row As DataGridViewRow In OrdersDetailsDataGridView.Rows
            row.Cells(5).Value = row.Cells(4).Value * row.Cells(3).Value
            sum += row.Cells(5).Value
 
        Next
        Me.Textbox1.Text = sum
        Me.TextBox1.Text = sum.ToString("$0.00")
        OrdersDetailsDataGridView.Columns(5).DefaultCellStyle.Format = ("$0.00")
 
    End Sub

Open in new window

0
Comment
Question by:demoniumz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 9
27 Comments
 
LVL 1

Author Comment

by:demoniumz
ID: 24419503
anyone to help me please
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24449423
just need the formula nothing more to calculate the total sum include the discount if exist  what is so  big problem to help me?

The code below is calculate the total sum  how to add discount inside in  order when exist to make the discount
Private Sub TotalSum()
        Dim sum As Decimal = 0
        For Each row As DataGridViewRow In OrdersDetailsDataGridView.Rows
            row.Cells(5).Value = row.Cells(4).Value * row.Cells(3).Value
            sum += row.Cells(5).Value
 
        Next
        Me.Textbox1.Text = sum
        Me.TextBox1.Text = sum.ToString("$0.00")
        OrdersDetailsDataGridView.Columns(5).DefaultCellStyle.Format = ("$0.00")
 
    End Sub
 
 
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        OrdersDetailsDataGridView.Rows.Remove(OrdersDetailsDataGridView.CurrentRow)
    End Sub
 
    
    Private Sub email_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles email.Click
        Nemail.Show()
    End Sub
 
    Private Sub callendar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles callendar.Click
        Ncalendar.Show()
    End Sub
 
    Private Sub touch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles touch.Click
        Process.Start("osk.exe")
    End Sub
 
    Private Sub info_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles info.Click
        NicolaouInfos.Show()
    End Sub
 
    Private Sub TabPage1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabPage1.Click
 
    End Sub
End Class
 

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24457471
Hi,
According to my understanding, you are using the TotalSum function to calculate the totalamount of the order. You need to apply the discount on this total. I think the discount would be in percentage so here is the new function.

Private Sub TotalSum()
        Dim sum As Decimal = 0
        For Each row As DataGridViewRow In OrdersDetailsDataGridView.Rows
            row.Cells(5).Value = row.Cells(4).Value * row.Cells(3).Value
            sum += row.Cells(5).Value
 
        Next
        if txtDiscount.text.length > 0 then
           Dim Discount as decimal = (sum * integer.parse(txtDiscount.text)) / 100
           sum = sum - discount
        end if
        Me.Textbox1.Text = sum
        Me.TextBox1.Text = sum.ToString("$0.00")
        OrdersDetailsDataGridView.Columns(5).DefaultCellStyle.Format = ("$0.00")
 
End Sub

Open in new window

0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 1

Author Comment

by:demoniumz
ID: 24459428
Yea is absolute working but can  i  have a textbox that how mucj total  discount they make in the  customer and the total is the Net total
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24460982
?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24461835
Yes offcourse. Try this
Private Sub TotalSum()
        Dim sum As Decimal = 0
        For Each row As DataGridViewRow In OrdersDetailsDataGridView.Rows
            row.Cells(5).Value = row.Cells(4).Value * row.Cells(3).Value
            sum += row.Cells(5).Value
 
        Next
        if txtDiscount.text.length > 0 then
           Dim Discount as decimal = (sum * integer.parse(txtDiscount.text)) / 100
           sum = sum - discount
           txtAppliedDiscount.text = discount.tostring
        end if
        Me.Textbox1.Text = sum
        Me.TextBox1.Text = sum.ToString("$0.00")
        OrdersDetailsDataGridView.Columns(5).DefaultCellStyle.Format = ("$0.00")
 
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:demoniumz
ID: 24461996
what is changed from  the previous code  you  give me?
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24462157
txtAppliedDiscount.text = discount.tostring ok  i try it after  thnks i must go now thnks
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24465144
is working my last  problem is can i  have  insert 10% in the datagridview how can  i insert thr symbol % because now is not acccepted


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
 
        Dim bsource As BindingSource = Me.OrdersDetailsDataGridView.DataSource
        Dim ds As DataSet = DirectCast(bsource.DataSource, DataSet)
        Dim orderdetailstable As New DataTable
        Dim dr As DataRow = ds.Tables("OrdersDetails").NewRow
        dr("OrderID") = OrderIDTextBox.Text
        dr("ProductID") = ProductIDComboBox.SelectedValue
        dr("UnitPrice") = UnitPriceTextBox.Text
        dr("Quantity") = QuantityTextBox.Text
        dr("Proname") = ProductNameTextBox1.Text
        dr("Discount") = DiscountTextBox.Text
        dr("Total records") = 0
        dr("Total") = Convert.ToDecimal(UnitPriceTextBox.Text.ToString()) * Convert.ToDecimal(QuantityTextBox.Text.ToString())
 
        ds.Tables("OrdersDetails").Rows.Add(dr)
 
        TotalSum()
 
    End Sub
 
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        OrdersDetailsDataGridView.Rows.Remove(OrdersDetailsDataGridView.CurrentRow)
 
        TotalSum()
    End Sub
 
 
 
    Private Sub TotalSum()
        Dim sum As Decimal = 0
        For Each row As DataGridViewRow In OrdersDetailsDataGridView.Rows
            row.Cells(6).Value = row.Cells(4).Value * row.Cells(3).Value
            sum += row.Cells(6).Value
 
        Next
        If DiscountTextBox.Text.Length > 0 Then
            Dim Discount As Decimal = (sum * Integer.Parse(DiscountTextBox.Text)) / 100
            sum = sum - Discount
            txtAppliedDiscount.text = Discount.ToString
        End If
        Me.Textbox1.Text = sum
        Me.TextBox1.Text = sum.ToString("¬0.00")
        OrdersDetailsDataGridView.Columns(6).DefaultCellStyle.Format = ("¬0.00")
 
    End Sub

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24467299
You can modify this line

dr("Discount") = DiscountTextBox.Text

to this
dr("Discount") = DiscountTextBox.Text & "%"

If it gives error, then it means the datatype of the column is integer so its not accepting the symbol.
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24468176
is not accepting in  the table orders in the database  in vb is System.decimal and in the ms access 2003 database  is in single type what i m,ust change in order to save it with  symbol %
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24468881
You have to change the type to varchar/text.
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24469317
i change to the database  to ms access 2003 to text but i get error

Input string was not in a correct format.Couldn't store <1%> in Discount Column.  Expected type is Single.

in the database in vb 2005


0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24470832
As the error message itself says, the column type still is Single. Do you create the columns for datatable yourself?
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24470878
behind the Vb 2005 is a ms access database 2003
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24470892
behind the Vb 2005 is a ms access database 2003 in the picture database discount details i save the field as Number.

in the VB2005 the database is linked so the data that takes and save is related.In the database i set  data type  System decimal.

is wrong is ok  why cant save the data with the % inside the datagridview  from the textbox Discount
untitled.PNG
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24470917
>why cant save the data with the % inside the datagridview
Its because you set the values in datagridview using the datatable and the type of the column in datatable is double reflecting the db datatype and double can not store string literals like %. So the solution is to either use numbers only and add the % to the column header or change the db type to text instead of double.
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24470929
if i  change in  text  in ms access  what data type i must add in the  vb 2005
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24470983
and if  i use only numbers  how i can add the % in the column header
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24471150
Can you post your full code here please otherwise i have no idea how you populate the datatable.
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24471447
here is the code thnks  for helping me


Public Class Norders
    Private im As Object
 
    Private Sub Norders_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    End Sub
 
 
 
 
 
    Private Sub OrdersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OrdersBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.OrdersBindingSource.EndEdit()
        Me.OrdersTableAdapter.Update(Me.NicolaouDBDataSet.Orders)
    End Sub
 
    Private Sub movenext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles movenext.Click
        OrdersBindingSource.MoveNext()
    End Sub
 
    Private Sub moveprevius_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles moveprevius.Click
        OrdersBindingSource.MovePrevious()
    End Sub
 
    Private Sub movelast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles movelast.Click
        OrdersBindingSource.MoveLast()
    End Sub
 
    Private Sub movefirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles movefirst.Click
        OrdersBindingSource.MoveFirst()
    End Sub
 
    Private Sub addrec_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addrec.Click
        Me.NicolaouDBDataSet.OrdersDetails.Clear()
        TextBox1.Text = String.Empty
        OrdersBindingSource.AddNew()
 
 
 
    End Sub
 
    Private Sub delrec_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles delrec.Click
        OrdersBindingSource.RemoveCurrent()
    End Sub
 
    Private Sub OrdersDetailsBindingSourceBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OrdersBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.OrdersDetailsBindingSource.EndEdit()
        Me.OrdersDetailsTableAdapter.Update(Me.NicolaouDBDataSet.OrdersDetails)
        Me.OrdersDetailsDataGridView.Refresh()
        TotalSum()
        
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
 
        Dim bsource As BindingSource = Me.OrdersDetailsDataGridView.DataSource
        Dim ds As DataSet = DirectCast(bsource.DataSource, DataSet)
        Dim orderdetailstable As New DataTable
        Dim dr As DataRow = ds.Tables("OrdersDetails").NewRow
        dr("OrderID") = OrderIDTextBox.Text
        dr("ProductID") = ProductIDComboBox.SelectedValue
        dr("UnitPrice") = UnitPriceTextBox.Text
        dr("Quantity") = QuantityTextBox.Text
        dr("Proname") = ProductNameTextBox1.Text
        dr("Discount") = DiscountTextBox.Text
        dr("Total records") = 0
        dr("Total") = Convert.ToDecimal(UnitPriceTextBox.Text.ToString()) * Convert.ToDecimal(QuantityTextBox.Text.ToString())
 
        ds.Tables("OrdersDetails").Rows.Add(dr)
 
        TotalSum()
 
    End Sub
 
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        OrdersDetailsDataGridView.Rows.Remove(OrdersDetailsDataGridView.CurrentRow)
 
        TotalSum()
    End Sub
 
 
 
    Private Sub TotalSum()
        Dim sum As Decimal = 0
        For Each row As DataGridViewRow In OrdersDetailsDataGridView.Rows
            row.Cells(6).Value = row.Cells(4).Value * row.Cells(3).Value
            sum += row.Cells(6).Value
 
        Next
        If DiscountTextBox.Text.Length > 0 Then
            Dim Discount As Decimal = (sum * Integer.Parse(DiscountTextBox.Text)) / 100
            sum = sum - Discount
 
            txtAppliedDiscount.Text = Discount.ToString
        End If
        Me.TextBox1.Text = sum
        Me.TextBox1.Text = sum.ToString("¬0.00")
        OrdersDetailsDataGridView.Columns(6).DefaultCellStyle.Format = ("¬0.00")
 
 
    End Sub
 
 
 
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        OrdersDetailsDataGridView.Rows.Remove(OrdersDetailsDataGridView.CurrentRow)
    End Sub
 
 
    Private Sub email_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles email.Click
        Nemail.Show()
    End Sub
 
    Private Sub callendar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles callendar.Click
        Ncalendar.Show()
    End Sub
 
    Private Sub touch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles touch.Click
        Process.Start("osk.exe")
    End Sub
 
    Private Sub info_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles info.Click
        NicolaouInfos.Show()
    End Sub
 
    
   
   
    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        'TODO: This line of code loads data into the 'NicolaouDBDataSet.Purchase' table. You can move, or remove it, as needed.
        Me.PurchaseTableAdapter.Fill(Me.NicolaouDBDataSet.Purchase)
        'TODO: This line of code loads data into the 'NicolaouDBDataSet.purchasedetails' table. You can move, or remove it, as needed.
        Me.PurchasedetailsTableAdapter.Fill(Me.NicolaouDBDataSet.Purchasedetails)
        'TODO: This line of code loads data into the 'NicolaouDBDataSet.OrdersDetails' table. You can move, or remove it, as needed.
        Me.OrdersDetailsTableAdapter.Fill(Me.NicolaouDBDataSet.OrdersDetails)
        'TODO: This line of code loads data into the 'NicolaouDBDataSet.Customers' table. You can move, or remove it, as needed.
        Me.CustomersTableAdapter.Fill(Me.NicolaouDBDataSet.Customers)
        'TODO: This line of code loads data into the 'NicolaouDBDataSet.Employees' table. You can move, or remove it, as needed.
        Me.EmployeesTableAdapter.Fill(Me.NicolaouDBDataSet.Employees)
 
        'TODO: This line of code loads data into the 'NicolaouDBDataSet.Orders' table. You can move, or remove it, as needed.
        Me.OrdersTableAdapter.Fill(Me.NicolaouDBDataSet.Orders)
        'TODO: This line of code loads data into the 'NicolaouDBDataSet.OrdersDetails' table. You can move, or remove it, as needed.
        Me.OrdersDetailsTableAdapter.Fill(Me.NicolaouDBDataSet.OrdersDetails)
        'TODO: This line of code loads data into the 'NicolaouDBDataSet.Products' table. You can move, or remove it, as needed.
        Me.ProductsTableAdapter.Fill(Me.NicolaouDBDataSet.Products)
        'TODO: This line of code loads data into the 'NicolaouDBDataSet.Suppliers' table. You can move, or remove it, as needed.
        Me.SuppliersTableAdapter.Fill(Me.NicolaouDBDataSet.Suppliers)
 
        TotalSum()
    End Sub
 
    Private Sub TabControl1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
 
    End Sub
 
    Private Sub TabPage1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabPage1.Click
 
    End Sub
End Class

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24471575
You need to change the database type for discount field to Text.
0
 
LVL 1

Author Comment

by:demoniumz
ID: 24471922
ok
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 24473125
Are you going to accept the answer now as the original question has been answered.
0
 
LVL 1

Author Closing Comment

by:demoniumz
ID: 31582511
excelent
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question