Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Total sum (discount formula) needed vb 2005

Posted on 2009-05-18
Medium Priority
755 Views
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
``````
0
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
• 16
• 9

LVL 1

Author Comment

ID: 24419503
0

LVL 1

Author Comment

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

``````
0

LVL 83

Expert Comment

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
``````
0

LVL 1

Author Comment

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

ID: 24460982
?
0

LVL 83

Expert Comment

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
``````
0

LVL 1

Author Comment

ID: 24461996
what is changed from  the previous code  you  give me?
0

LVL 1

Author Comment

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

LVL 1

Author Comment

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())

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
``````
0

LVL 83

Expert Comment

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

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

ID: 24468881
You have to change the type to varchar/text.
0

LVL 1

Author Comment

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

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

ID: 24470878
behind the Vb 2005 is a ms access database 2003
0

LVL 1

Author Comment

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

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

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

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

LVL 83

Expert Comment

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

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()
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

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

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.
'TODO: This line of code loads data into the 'NicolaouDBDataSet.purchasedetails' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'NicolaouDBDataSet.OrdersDetails' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'NicolaouDBDataSet.Customers' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'NicolaouDBDataSet.Employees' table. You can move, or remove it, as needed.

'TODO: This line of code loads data into the 'NicolaouDBDataSet.Orders' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'NicolaouDBDataSet.OrdersDetails' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'NicolaouDBDataSet.Products' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'NicolaouDBDataSet.Suppliers' table. You can move, or remove it, as needed.

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
``````
0

LVL 83

Expert Comment

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

LVL 1

Author Comment

ID: 24471922
ok
0

LVL 83

Accepted Solution

CodeCruiser earned 2000 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

ID: 31582511
excelent
0

## Featured Post

Question has a verified solution.

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

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the Wâ€¦
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:â€¦
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â€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
###### Suggested Courses
Course of the Month11 days, 15 hours left to enroll