locked
hello i want to ask a question about calculatin this is the problem RRS feed

  • Question

  •    I want to get the total of ROWS IN A TEXTBOX I MEAN LIKE THIS

    quantity           price                  subtotal

      10          *            10                  100
      15          *             1                     15
       8           *            10                    80                      this is a exam.     so the total  OF   ( 100+ 15+ 80  = 195)   To be shown 

    in the texTbox named" TOTAL "  and so .with the other rows  till unlimited if is possible 

     

     Dim dt As DataTable = New DataTable
            Dim BarkodiColumn As DataColumn = New DataColumn
            With BarkodiColumn
                .DataType = GetType(System.Decimal)
                .ColumnName = "Barkodi"
            End With
            Dim EmriiartikullitColumn As DataColumn = New DataColumn
            With EmriiartikullitColumn
                .DataType = GetType(System.String)
                .ColumnName = "Emri i artikullit "
            End With
            Dim SasiaColumn As DataColumn = New DataColumn
            With SasiaColumn
                .DataType = GetType(System.Decimal)
                .ColumnName = "Sasia"
            End With
            Dim CmimiColumn As DataColumn = New DataColumn
            With CmimiColumn
                .DataType = GetType(System.Decimal)
                .ColumnName = "Cmimi"
            End With
            Dim VleraColumn As DataColumn = New DataColumn
            With VleraColumn
                .DataType = GetType(System.Decimal)
                .ColumnName = "Vlera"
                .Expression = "Sasia * Cmimi "

    i have used the above code to calcualte the columns of Sasia *and CMIMI                    

     

     


    • Edited by amit00 Tuesday, January 3, 2012 10:57 PM
    Tuesday, January 3, 2012 10:56 PM

Answers

  • Perhaps I should note that the vast majority of the code I posted simply sets up an example so that you can copy-paste-run and see how it works...

    The part you are concerned with really lies here:

    Dim sumTotal As Decimal
    sumTotal = CDec(table.Compute("SUM([Subtotal])", ""))
    outputTextBox.Text = sumTotal.ToString("c")
    
    

    Where "table" is the name of your table being viewed in the DataGridView and "Subtotal" is the name of the column in the table that you want to sum.

    "outputTextBox" is the name of whatever textbox you want to display this information in.

    Now, if you are concerned about when this code executes (that is, you want it to happen "automatically" when the rows change), then you'll need to call it from the appropriate event handler, such as perhaps CurrentChanged and/or ListChanged on your binding source.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    • Marked as answer by amit00 Friday, January 6, 2012 3:52 PM
    Thursday, January 5, 2012 12:13 AM
  • @ Amit,

    I thought that you was wanting to use the textbox as the UI for this and that it was an assignment for your exam. The lack of the word "the" made me think that.

    However, because of all the trouble with replies not intended to your question I've made a very short sample which shows exactly what you show in your question if you click on the button. You need to drag for this a DataGridView, a Button and a Textbox on a form.

    Than you only have to paste in this code and click on the button. It shows exactly like you have written.

     

    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn With {.DataType = GetType(System.Decimal), .ColumnName = "Barkodi"})
            dt.Columns.Add(New DataColumn With {.DataType = GetType(System.String), .ColumnName = "EmriIArtikullit"})
            dt.Columns.Add(New DataColumn With {.DataType = GetType(System.Decimal), .ColumnName = "Cmimi"})
            dt.Columns.Add(New DataColumn With {.DataType = GetType(System.Decimal), .ColumnName = "Sasiamimi", .Expression = "Barkodi * Cmimi"})
            For i = 0 To 2
                dt.Rows.Add(dt.NewRow)
            Next
            dt.Rows(0).ItemArray = New Object() {10, "*", 10}
            dt.Rows(1).ItemArray = New Object() {15, "*", 1}
            dt.Rows(2).ItemArray = New Object() {8, "*", 10}
            TextBox1.Text = CStr(dt.Compute("Sum(Sasiamimi)", ""))
            DataGridView1.DataSource = dt
        End Sub
    End Class
    

     Be aware that you should not set spaces inside the columnames (or then use it with brackets [] around it)

     


    Success
    Cor
    • Edited by Cor Ligthert Thursday, January 5, 2012 10:14 PM
    • Marked as answer by amit00 Friday, January 6, 2012 3:57 PM
    Thursday, January 5, 2012 10:11 PM
  • amit00,

    The only reason I can see for you not to accept Reed's code is that you are not sure how to incorporate it with retrieving values from your database. 

    I have attempted to use what I believe are your column names with the help of Google Translate to build an Access DB similar to what I think you are using.  In any case it should be close enough for you to figure out.

    I'm lazier than Reed and ask that you start with a new WinForm project and drag a DataGridView and TextBox to the form.  Then copy this code to Form1.vb.  You will need to supply your connection string and Access table name where indicated.

    This code connections to your database and fills a new DataTable based on the command text.  It then appends a new DataColumn to compute the "Quantity * Price" value.  It then displays the resulting table on a DataGridView.  The dt.Compute command is a nice feature of the DataTable class that allows for performing computations on the table contents; in this case we are summing a column.

     

     

    Public Class Form1
       Dim dt As DataTable
       Dim bs As New BindingSource
       Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

          Dim conn As New OleDb.OleDbConnection
          'Substitute your connection string here
             conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\My Documents\temp\Play.mdb'"

          Dim cmd As New OleDb.OleDbCommand
          'Substitute your Table Name for 'For_amit00'
             cmd.CommandText = "Select [Emri i artikullit], [Sasia], [Cmimi] From For_amit00"
             cmd.Connection = conn
          Dim da As New OleDb.OleDbDataAdapter(cmd)
          'Fill dt based on cmd.CommandText
             dt = New DataTable
             da.Fill(dt)
          'Add New Colum to compute extended cost
             dt.Columns.Add(New DataColumn() With {.DataType = GetType(System.Decimal), .ColumnName = "Vlera", .Expression = "Sasia * Cmimi "})
          'Attach dt to binding source if modifying Access DB is necessary
             bs.DataSource = dt
             DataGridView1.DataSource = bs
          'Compute sum of 'extended costs'
          TextBox1.Text = CType(dt.Compute("Sum([Vlera])", "True"), Decimal).ToString
       End Sub
    End Class

    If you have any questions, feel free to ask.


    • Marked as answer by amit00 Friday, January 6, 2012 3:57 PM
    • Edited by TnTinMN Friday, January 6, 2012 4:30 PM formatting error
    Friday, January 6, 2012 2:00 AM

All replies

  • I don't know who gave you this assignment, but it is stuppid. 

    If somebody ask you who told that, than tell a Microsoft VB MVP.

    You normally should not use the UI to calculate the result but the data.

     


    Success
    Cor
    Tuesday, January 3, 2012 11:48 PM
  • Something like this perhaps:

    Public Class Form1
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Dim table As New DataTable
            table.Columns.Add("Quantity", GetType(Integer))
            table.Columns.Add("Price", GetType(Decimal))
            table.Columns.Add("Subtotal", GetType(Decimal), "[Quantity] * [Price]")
    
            Dim randomNumbers As New Random
            For i As Integer = 0 To 9
                table.Rows.Add(randomNumbers.Next(1, 10), CDec(randomNumbers.Next(1, 20) + randomNumbers.NextDouble))
            Next
    
            Dim inputGridView As New DataGridView
            Dim inputBindingSource As New BindingSource
    
            inputBindingSource.DataSource = table
            inputGridView.AutoGenerateColumns = True
            inputGridView.DataSource = inputBindingSource
            inputGridView.Dock = DockStyle.Top
            Controls.Add(inputGridView)
    
            Dim sumTotal As Decimal
            sumTotal = CDec(table.Compute("SUM([Subtotal])", ""))
    
            Dim outputTextBox As New TextBox
            outputTextBox.Text = sumTotal.ToString("c")
            outputTextBox.Top = inputGridView.Height + 4
            Controls.Add(outputTextBox)
        End Sub
    End Class
    


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    • Proposed as answer by Dig-Boy Wednesday, January 4, 2012 9:00 PM
    Wednesday, January 4, 2012 1:48 AM
  • I don't know who gave you this assignment, but it is stuppid. 

    Please do not refer to questions posted here as 'stupid'.

    The UI is not being used for the calculation.  The calculation is being done using the table, as is evident from the code that OP has posted.  The UI is used to display the total (195 in this case) which is quite appropriate.

    Wednesday, January 4, 2012 3:00 AM
  • why you think that is stupid can you explain me sir please ????
    Wednesday, January 4, 2012 8:10 PM
  • why you think that is stupid can you explain me sir please ????


    It's not stupid.  Cor's suggesting that the total sum calculation should be done at the source of the table's data (i.e. the database itself as in a stored procedure) instead of through the UI layer.  This argument makes sense in *some* situations but since your question is a homework assignment there is no need to consider this in the context of a real world scenario...  and even in those scenarios there are plenty of good reasons why calculation can be done in the UI layer (i.e. behind forms or even within controls themselves).  Each of those reasons is specific to technological choices and constraints.  It's not even worth exploring the idea here since it is irrelevant to your question.  You've been given an assignment and that's really all that matters. 

    I really like Reed's solution - it is very easy to understand and takes advantage of existing functionality built into the DataTable class...  though it may have been your instructor's intention to have you do the summing yourself ;)

    Wednesday, January 4, 2012 9:00 PM
  • Anyone will help me with this code i m stuck here i really will appreciate any help     because above code is not exactly i wanted

    so i have a database ,in access  , and connected the datagirdview1 to the database and  now i want the sum of rows like i explained above   first row '140" second row " 140 "  third row "10"   and the total of this three rows  to be calculated  in output textbox anyone knows or what???? 


    thnx in advance

    i really will appreciate

    Wednesday, January 4, 2012 11:44 PM
  • Have you tried the code that Reed posted?
    Wednesday, January 4, 2012 11:53 PM
  • Anyone will help me with this code i m stuck here i really will appreciate any help     because above code is not exactly i wanted

    so i have a database ,in access  , and connected the datagirdview1 to the database and  now i want the sum of rows like i explained above   first row '140" second row " 140 "  third row "10"   and the total of this three rows  to be calculated  in output textbox anyone knows or what???? 


    Actually, from what I can see Reed's code IS exactly what you asked for.  Have you tried it?  You have a column for each row that has a total for the row but you need to calculate the sum of the totals column in a textbox.  Reed's code does exactly that.  Or have we all misunderstood you?  If yes, then please tell us in detail what you need differently.
    Wednesday, January 4, 2012 11:56 PM
  • Perhaps I should note that the vast majority of the code I posted simply sets up an example so that you can copy-paste-run and see how it works...

    The part you are concerned with really lies here:

    Dim sumTotal As Decimal
    sumTotal = CDec(table.Compute("SUM([Subtotal])", ""))
    outputTextBox.Text = sumTotal.ToString("c")
    
    

    Where "table" is the name of your table being viewed in the DataGridView and "Subtotal" is the name of the column in the table that you want to sum.

    "outputTextBox" is the name of whatever textbox you want to display this information in.

    Now, if you are concerned about when this code executes (that is, you want it to happen "automatically" when the rows change), then you'll need to call it from the appropriate event handler, such as perhaps CurrentChanged and/or ListChanged on your binding source.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    • Marked as answer by amit00 Friday, January 6, 2012 3:52 PM
    Thursday, January 5, 2012 12:13 AM
  • You've been given an assignment and that's really all that matters. 

    Why on earth are you making this assertion.  You should be aware that this claim will affect the responses that OP can expect.  There is no basis for asserting that OP's task is an assignment.

    Thursday, January 5, 2012 1:23 AM
  • You've been given an assignment and that's really all that matters. 

    Why on earth are you making this assertion.  You should be aware that this claim will affect the responses that OP can expect.  There is no basis for asserting that OP's task is an assignment.


    Well you made me go back and look.  When I first read the original post I was sure I saw the OP say something about it being on a test.  That's why I wrote it so confidently.  Looking again I can see it was this line in the grid example that gave me the impression:  this is a exam

    In retrospect it seems a bit out of context but, like I said, it lent to an impression.  But I'm still not sure.  @amit00:  Is this for school or something else?

    As for effecting the responses, you might be right.  I wasn't considering that at all.  I was more focused on validating the OP's question against Cor's assertion than thinking about t.he future of the thread.  I apologize to the OP if this had a negative effect.

     

    Thursday, January 5, 2012 1:35 AM
  • You have been misled by an earlier post that was based on a misreading and has not been corrected.  The comment about the exam refers to the table, not the question, and it is short for example.
    Thursday, January 5, 2012 2:08 AM
  • Oh.  Sorry for the confusion.
    Thursday, January 5, 2012 2:18 AM
  • Actually in the original post - "this is a exam. so the total OF ( 100+ 15+ 80 = 195) To be shown in the texTbox named" TOTAL " and so .with the other rows till unlimited if is possible"

    He also stated - " I want to get the total of ROWS IN A TEXTBOX I MEAN LIKE THIS"

    I think he actually wants column totals, in which case the code posted by Reed K. is spot on.

     

     

     

    Thursday, January 5, 2012 2:38 AM
  • I don't know who gave you this assignment, but it is stuppid. 

    Please do not refer to questions posted here as 'stupid'.

    The UI is not being used for the calculation.  The calculation is being done using the table, as is evident from the code that OP has posted.  The UI is used to display the total (195 in this case) which is quite appropriate.


    @Acamar

    You are very good in putting the words of other contributors in an other intention than it was meant. We don't have all the same mind as you.

    The OP gave a reply, and I am well enough to give a good answer.

    I did not mean like is told in this thread that I did mean that there should be used a seperated class, that there should be used a seperated layer.

    I did understand that the the OP wanted the Total of Rows in a textbox (so that is the UI, I'm talking about).

    After rereading it, and after what he wrote, he meant probably that he wanted the total of the rows in a textbox, so the simple missing of the word "the" makes why I did read it as that the OP wanted to use a Textbox as his UI.

    Because that this thread has only become a thread about what I should have meant, but did not mean at all. Inititated by you, I've made a simple sample for the OP how he can get his result.

     

     

     


    Success
    Cor
    Thursday, January 5, 2012 10:08 PM
  • @ Amit,

    I thought that you was wanting to use the textbox as the UI for this and that it was an assignment for your exam. The lack of the word "the" made me think that.

    However, because of all the trouble with replies not intended to your question I've made a very short sample which shows exactly what you show in your question if you click on the button. You need to drag for this a DataGridView, a Button and a Textbox on a form.

    Than you only have to paste in this code and click on the button. It shows exactly like you have written.

     

    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn With {.DataType = GetType(System.Decimal), .ColumnName = "Barkodi"})
            dt.Columns.Add(New DataColumn With {.DataType = GetType(System.String), .ColumnName = "EmriIArtikullit"})
            dt.Columns.Add(New DataColumn With {.DataType = GetType(System.Decimal), .ColumnName = "Cmimi"})
            dt.Columns.Add(New DataColumn With {.DataType = GetType(System.Decimal), .ColumnName = "Sasiamimi", .Expression = "Barkodi * Cmimi"})
            For i = 0 To 2
                dt.Rows.Add(dt.NewRow)
            Next
            dt.Rows(0).ItemArray = New Object() {10, "*", 10}
            dt.Rows(1).ItemArray = New Object() {15, "*", 1}
            dt.Rows(2).ItemArray = New Object() {8, "*", 10}
            TextBox1.Text = CStr(dt.Compute("Sum(Sasiamimi)", ""))
            DataGridView1.DataSource = dt
        End Sub
    End Class
    

     Be aware that you should not set spaces inside the columnames (or then use it with brackets [] around it)

     


    Success
    Cor
    • Edited by Cor Ligthert Thursday, January 5, 2012 10:14 PM
    • Marked as answer by amit00 Friday, January 6, 2012 3:57 PM
    Thursday, January 5, 2012 10:11 PM
  • amit00,

    The only reason I can see for you not to accept Reed's code is that you are not sure how to incorporate it with retrieving values from your database. 

    I have attempted to use what I believe are your column names with the help of Google Translate to build an Access DB similar to what I think you are using.  In any case it should be close enough for you to figure out.

    I'm lazier than Reed and ask that you start with a new WinForm project and drag a DataGridView and TextBox to the form.  Then copy this code to Form1.vb.  You will need to supply your connection string and Access table name where indicated.

    This code connections to your database and fills a new DataTable based on the command text.  It then appends a new DataColumn to compute the "Quantity * Price" value.  It then displays the resulting table on a DataGridView.  The dt.Compute command is a nice feature of the DataTable class that allows for performing computations on the table contents; in this case we are summing a column.

     

     

    Public Class Form1
       Dim dt As DataTable
       Dim bs As New BindingSource
       Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

          Dim conn As New OleDb.OleDbConnection
          'Substitute your connection string here
             conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\My Documents\temp\Play.mdb'"

          Dim cmd As New OleDb.OleDbCommand
          'Substitute your Table Name for 'For_amit00'
             cmd.CommandText = "Select [Emri i artikullit], [Sasia], [Cmimi] From For_amit00"
             cmd.Connection = conn
          Dim da As New OleDb.OleDbDataAdapter(cmd)
          'Fill dt based on cmd.CommandText
             dt = New DataTable
             da.Fill(dt)
          'Add New Colum to compute extended cost
             dt.Columns.Add(New DataColumn() With {.DataType = GetType(System.Decimal), .ColumnName = "Vlera", .Expression = "Sasia * Cmimi "})
          'Attach dt to binding source if modifying Access DB is necessary
             bs.DataSource = dt
             DataGridView1.DataSource = bs
          'Compute sum of 'extended costs'
          TextBox1.Text = CType(dt.Compute("Sum([Vlera])", "True"), Decimal).ToString
       End Sub
    End Class

    If you have any questions, feel free to ask.


    • Marked as answer by amit00 Friday, January 6, 2012 3:57 PM
    • Edited by TnTinMN Friday, January 6, 2012 4:30 PM formatting error
    Friday, January 6, 2012 2:00 AM
  • thnx a lot reed kimble
    Friday, January 6, 2012 3:53 PM