Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Datagridview Col1+Col2*Col3 = Textbox value

Posted on 2013-01-12
31
Medium Priority
?
681 Views
Last Modified: 2013-01-16
Software vb.net & sql server 2005......databound datagridview.

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

Thanks!
0
Comment
Question by:dave_sky
[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
  • 15
  • 9
  • 7
31 Comments
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 38771325
>>Col1+Col2*Col3 = Textbox value
what is that mean?
0
 

Author Comment

by:dave_sky
ID: 38772140
Databound Datagridview:

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

(2 + 3) times 2 = Textbox1 value (10)
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 38772175
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:dave_sky
ID: 38772208
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
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 38772512
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
 

Author Comment

by:dave_sky
ID: 38772730
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
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 38773475
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38773998
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
 

Author Comment

by:dave_sky
ID: 38775131
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38775167
No. Just add this at the end

YourTextBoxName.Text = Total
0
 

Author Comment

by:dave_sky
ID: 38775252
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
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 38775286
Whats the error?
Did u try both suggestions?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38775414
I said no to "should i replace total with textbox name"

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

TextBoxEstFiMaterials.text = total
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 38775854
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
 

Author Comment

by:dave_sky
ID: 38776463
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
 

Author Comment

by:dave_sky
ID: 38776468
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38776503
Change that line to this

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

Author Comment

by:dave_sky
ID: 38776599
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38776665
Yes you can change it to anything.
0
 

Author Comment

by:dave_sky
ID: 38776723
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
 
LVL 42

Assisted Solution

by:Meir Rivkin
Meir Rivkin earned 400 total points
ID: 38777264
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38777764
Show us the code you tried.
0
 

Author Comment

by:dave_sky
ID: 38779224
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
 

Author Comment

by:dave_sky
ID: 38779238
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 1600 total points
ID: 38779317
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
 

Author Comment

by:dave_sky
ID: 38779781
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38779941
And you do not put any text in textbox at design time? Show us all the code you have.
0
 

Author Comment

by:dave_sky
ID: 38780650
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38782180
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
 

Author Comment

by:dave_sky
ID: 38783464
I will create a new app with no conflicting code and test this afternoon.
0
 

Author Comment

by:dave_sky
ID: 38785236
CodeCruiser:

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Suggested Courses

647 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