Datagridview Col1+Col2*Col3 = Textbox value

Software vb.net & sql server 2005......databound datagridview.

I am looking for sample code for the following:
Datagridview Col1+Col2*Col3 = Textbox value

Thanks!
dave_skyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
CodeCruiserConnect With a Mentor Commented:
And no matter what you use in place of Calcs in this line

TblPlumbingBidDetail.Columns.Add("Calcs", GetType(Double), "(FixtureUpgradePrice + TrimUpgradePrice) * Qty")


you still get error? Are you sure the names you are using do not exist in there already? Are you calling this code repeatedly? If yes then change above code to

       Dim TblPlumbingBidDetail As DataTable = Me.MyDataSet.tblPlumbingBidDetail
       If TblPlumbingBidDetail.Columns.Contains("Calcs") = False Then 
           TblPlumbingBidDetail.Columns.Add("Calcs", GetType(Double), "(FixtureUpgradePrice + TrimUpgradePrice) * Qty")
       End If

        Dim Calcs = TblPlumbingBidDetail.Compute("Sum(Calcs)", "")
        TextBoxEstFiMaterials.Text = Calcs

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
>>Col1+Col2*Col3 = Textbox value
what is that mean?
0
 
dave_skyAuthor Commented:
Databound Datagridview:

Col1   | Col2   | Col3  |
    2     |    3      |    2     |

(2 + 3) times 2 = Textbox1 value (10)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Meir RivkinFull stack Software EngineerCommented:
Is it always the 1st row?
Basically it is something like this:
txtbox.Value=(gridview.Rows[0][0]+ gridview.Rows[0][1])* gridview.Rows[0][2]

Open in new window


The 1st index after Rows is the row index.
So this code calculates the equation on 1st row data.
Replace square brackets with round brackets to have it vb.net
0
 
dave_skyAuthor Commented:
I should have been more clear - the calculations involve multiple rows...

Databound Datagridview:

Col1   | Col2   | Col3  |
    2     |    3      |    2     |
    2     |   3       |    2     |

Row 1 (2 + 3) times 2
Row 2 (2 + 3) times 2

Textbox1 value = (20)

Maybe, I have to use another 'extension' column...
0
 
Meir RivkinFull stack Software EngineerCommented:
ok, so u want to sum all rows by the formula you provided.

the simple way:
Dim sum As Long = 0
For Each drg As DataGridRow In Datagridview.Rows.Cast(Of DataGridRow)()
	sum += (drg(0) + drg(1)) * drg(2)
Next
Textbox1.Value = sum.ToString()

Open in new window


the linq way:
Dim rowsSum As [Long] = Datagridview.Rows.Cast(Of DataGridRow)().[Select](Function(n) ([Long].Parse(n(0).ToString()) + [Long].Parse(n(1).ToString())) * [Long].Parse(n(2).ToString()))
Textbox1.Value = rowsSum.Sum().ToString()

Open in new window

0
 
dave_skyAuthor Commented:
Is this how you had the sample code result planned?

 Dim sum As Long = 0
        For Each drg As DataGridViewRow In TblPlumbingBidDetailDataGridView.Rows.Cast(Of DataGridViewRow)()
            sum += (drg(7) + drg(11)) * drg(3)
        Next
        TextBoxEstFiMaterials.Value = sum.ToString()
0
 
Meir RivkinFull stack Software EngineerCommented:
yes.

make sure you got the column index correct, but its exactly right.

btw, i made a mistake in the linq example, here's the right one:

Textbox1.Value = Datagridview.Rows.Cast(Of DataGridRow)().[Select](Function(n) ([Long].Parse(n(0).ToString()) + [Long].Parse(n(1).ToString())) * [Long].Parse(n(2).ToString())).Sum().ToString()

Open in new window

0
 
CodeCruiserCommented:
Is the grid bound to a datatable? If yes then try

DataTable1.Columns.Add("Total", GetType(Double), "(Column1 + Column2) * Column3")

Dim Total = DataTable1.Compute("Sum(Total)"
0
 
dave_skyAuthor Commented:
Sedgwick, I couldn't get you code to work.

Codecruiser, do I rename all instances of "Total" in your code to my textbox name?
0
 
CodeCruiserCommented:
No. Just add this at the end

YourTextBoxName.Text = Total
0
 
dave_skyAuthor Commented:
This is the code I am trying:

Dim TblPlumbingBidDetail As DataTable = Me.MyDataSet.tblPlumbingBidDetail

 TblPlumbingBidDetail.Columns.Add("Total", GetType(Double), "(FixtureUpgradePrice + TrimUpgradePrice) * Qty")

        Dim Total = TblPlumbingBidDetail.Compute("Sum(TextBoxEstFiMaterials.text")

Has an error on the underlined...
0
 
Meir RivkinFull stack Software EngineerCommented:
Whats the error?
Did u try both suggestions?
0
 
CodeCruiserCommented:
I said no to "should i replace total with textbox name"

Dim Total = TblPlumbingBidDetail.Compute("Sum(Total)")

TextBoxEstFiMaterials.text = total
0
 
Meir RivkinFull stack Software EngineerCommented:
that should work:

Dim TblPlumbingBidDetail As DataTable = Me.MyDataSet.tblPlumbingBidDetail
Dim sum As Long = 0
For Each dr As DataRow In TblPlumbingBidDetail.Rows.Cast(Of DataRow)()
	sum += (dr.Field(Of Long)(0) + dr.Field(Of Long)(1)) * dr.Field(Of Long)(2)
Next
Textbox1.Value = sum.ToString()
                                            

Open in new window

0
 
dave_skyAuthor Commented:
Sedgwick:

Dim TblPlumbingBidDetail As DataTable = Me.MyDataSet.tblPlumbingBidDetail
        Dim sum As Long = 0
        For Each dr As DataRow In TblPlumbingBidDetail.Rows.cast(Of DataRow)()
            sum += (dr.Field(Of Long)(0) + dr.Field(Of Long)(1)) * dr.Field(Of Long)(2)
        Next
        Me.TextBoxEstFiMaterials.Text = sum.ToString()

Error on the underlined:
Error      16      'cast' is not a member of 'System.Data.DataRowCollection'.

I can't fix the error. The code must be for a newer vb.net........I am using 2005...
0
 
dave_skyAuthor Commented:
CodeCruiser:

 Dim TblPlumbingBidDetail As DataTable = Me.MyDataSet.tblPlumbingBidDetail
        TblPlumbingBidDetail.Columns.Add("Total", GetType(Double), "(FixtureUpgradePrice + TrimUpgradePrice) * Qty")

        Dim Total = TblPlumbingBidDetail.Compute("Sum(Total)")
        TextBoxEstFiMaterials.Text = Total

Error on the underlined:

Error      16      Argument not specified for parameter 'filter' of 'Public Function Compute(expression As String, filter As String) As Object'.
0
 
CodeCruiserCommented:
Change that line to this

Dim Total = TblPlumbingBidDetail.Compute("Sum(Total)", "")
0
 
dave_skyAuthor Commented:
Ok, making headway.

Can I change "Total" to something else?  It says "A column named 'Total' already belongs to this DataTable." (I tried Table1 with no luck)...

Is the sample code adding a column to the table named "Total" ??
0
 
CodeCruiserCommented:
Yes you can change it to anything.
0
 
dave_skyAuthor Commented:
No matter what I change the name to I still get the same error message
"A column named '_______' already belongs to this DataTable."

This happens on the second update to the database...

I know I have about wore this one out - but do you have any ideas on what to do.

Thanks!
0
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
try this:

Dim TblPlumbingBidDetail As DataTable = Me.MyDataSet.tblPlumbingBidDetail
        Dim sum As Long = 0
        For Each dr As DataRow In TblPlumbingBidDetail.Rows
            sum += (Long.Parse(dr.Item(0).ToString()) + Long.Parse(dr.Item(1).ToString())) * Long.Parse(dr.Item(2).ToString())
        Next
        Me.TextBoxEstFiMaterials.Text = sum.ToString()

Open in new window

0
 
CodeCruiserCommented:
Show us the code you tried.
0
 
dave_skyAuthor Commented:
Sedgwick:

Dim TblPlumbingBidDetail As DataTable = Me.MyDataSet.tblPlumbingBidDetail
        Dim sum As Long = 0
        For Each dr As DataRow In TblPlumbingBidDetail.Rows
            sum += (Long.Parse(dr.Item("FixtureUpgradePrice").ToString()) + Long.Parse(dr.Item("TrimUpgradePrice").ToString())) * Long.Parse(dr.Item("Qty").ToString())
        Next
        Me.TextBoxEstFiMaterials.Text = sum.ToString()

Error: Input string was not in a correct format.
0
 
dave_skyAuthor Commented:
CodeCruiser:

 Dim TblPlumbingBidDetail As DataTable = Me.MyDataSet.tblPlumbingBidDetail
        TblPlumbingBidDetail.Columns.Add("Calcs", GetType(Double), "(FixtureUpgradePrice + TrimUpgradePrice) * Qty")

        Dim Calcs = TblPlumbingBidDetail.Compute("Sum(Calcs)", "")
        TextBoxEstFiMaterials.Text = Calcs

Open in new window

0
 
dave_skyAuthor Commented:
Ok.......that took  care of the duplication error but I get this number in the textbox:

3064731.93

I tried removing all code that may be a conflict and removing all values in the columns in the total grid and that number still appears on a button click event and form load.
0
 
CodeCruiserCommented:
And you do not put any text in textbox at design time? Show us all the code you have.
0
 
dave_skyAuthor Commented:
I'm not entering anything in the textbox...

It's a lot of code...

Can you tell me......is the number appearing because all 3 column data types is money?
0
 
CodeCruiserCommented:
The number is appearing because it is being put in there. Textbox has nothing to do with grid or datatable so a number in textbox would not appear magically on its own unless above code is being called.
0
 
dave_skyAuthor Commented:
I will create a new app with no conflicting code and test this afternoon.
0
 
dave_skyAuthor Commented:
CodeCruiser:

Apoligies...My application was inserting values in the textbox......made up a new simple app and the code worked great.
0
All Courses

From novice to tech pro — start learning today.