WelshWarrior

Hi people



I have a DataGridView which displays info from a Database. I need a couple of the columns to be able to do basic calculations such as +, - etc - nothing more complicated really. I am a bit stuck as to how to gte this working.I have tried the code below with no joy:

Code:
Private Sub cmdWorkout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdWorkout.Click For Each row As DataGridViewRow In DataGridView1.Rows row.Cells("SessionTotal").Value = (row.Cells("Points").Value - row.Cells("Assessment").Value) row.Cells("CumTotal").Value = row.Cells("CumTotal").Value + row.Cells("SessionTotal").Value row.Cells("Target").Value = 1000 - row.Cells("CumTotal").Value Next End Sub


AND:

Code:
Private Sub cmdWorkout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdWorkout.Click For Each row As DataGridViewRow In DataGridView1.Rows ds.Tables("SessionTotal").DisplayExpression = "Points" - "Sanctions" Next End Sub



But keep getting a "Conversion from string "Points" to type 'Double' is not valid." Error. The fields in the database are set to number.



Thanks in Advance


Re: Visual Basic General Calculations in a DataGridView

DigBoy2000

try this

CDbl(row.Cells("Points").Value) - CDbl(row.Cells("Assessment").Value)

If these values are supposed to be double but are being read as strings then somewhere in your code you must not transferring the data types of the fields when the grid's data source is set -- the default typeof string is being used instead. The above attempt at converting the values during the calculation should be understood as a quick fix (you would need to put in error-catching for non-parsable values and/or dbNull values). You should really try to figure out why the data types are not coming through.




Re: Visual Basic General Calculations in a DataGridView

Riquel Dong ĘC MSFT

Hi Welsh,

Based on your post, you need to add the column to the datagridview to represent the value based on the other columns value.

Because you use the datatable as the datasource, then you can add the new datacolumn to this datatable and use the DataColumn.Expression property to achieve this. Here is the code snippet about using this preperty to combine the there datacolumn (double type) to the new datacolumn. If you have any further questions, please tell me.

Code Snippet

Dim dt As New DataTable

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

For i As Integer = 0 To 10

Dim dr As DataRow = dt.NewRow

dr.Item(0) = 123.0

dr.Item(1) = 13.0

dr.Item(2) = 3.0

dt.Rows.Add(dr)

Next

dt.Columns.Add("total", Type.GetType("System.Double"), True)

dt.Columns("total").Expression = "number1+number2-number3"

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

dt.Columns.Add("number1", Type.GetType("System.Double"))

dt.Columns.Add("number2", Type.GetType("System.Double"))

dt.Columns.Add("number3", Type.GetType("System.Double"))

DataGridView1.DataSource = dt

End Sub

For more information about how to use the DataColumn.Expression property, visit here: http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.80).aspx .

Best regards.






Re: Visual Basic General Calculations in a DataGridView

WelshWarrior

Hi Riquel thanks for the help so far

My database has the following columns: StudID, StudName, ClsGrp, Points, Sanctions, Pastoral, Assessment, House, CumTotal and Target

The user clicks button to add values to Points and Sanctions - this works fine - the problem is with the next bit! CumTotal must be updated when the program runs and then saved back when the user clicks update. I need 1 extra column in the DataGridView called SessionTotal which would just be a temp column to aid the calculations.

When the user clicks update, I need the following calcs to take place:

SessionTotal = Points - Sanctions (doesn't need to be stored in Database)

CumTotal = CumTotal + SessionTotal (this one returned a circular error message when I tried)

Target = 1000 - CumTotal (doesn't need to be stored in database)

I need to know how to do the above calculations and I am not sure whether what you gave me will work. Any chance you could give me an example using the info I gave you above Sorry to be a pain!

Thanks a lot





Re: Visual Basic General Calculations in a DataGridView

WelshWarrior

You there Riquel

I managed to get all the columns working but cannot get the CumTotal (cumulative total) column working because of a circular reference error. I am desperate for some help with it. Here is the code so far:

Code Snippet

Private Sub cmdWorkout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdWorkout.Click

Dim SessionTotal As DataColumn = New DataColumn

Dim Target As DataColumn = New DataColumn

For Each row As DataGridViewRow In DataGridView1.Rows

'CumTotal = 0

With SessionTotal

.DataType = System.Type.GetType("System.Decimal")

.ColumnName = "SessionTotal"

.DefaultValue = 0

'.Expression = "Points - Sanctions"

.Expression = "IIF(Sanctions > 0, Points - Sanctions, Points)"

End With

With Target

.DataType = System.Type.GetType("System.Decimal")

.ColumnName = "Target"

.DefaultValue = 0

.Expression = "1000 - SessionTotal"

End With

Next

'da.Fill(ds)

ds.Tables(0).Columns.Add(SessionTotal)

ds.Tables(0).Columns.Add(CumTotalTemp)

ds.Tables(0).Columns.Add(Target)

'DataGridView1.DataSource = ds.Tables(0)

DataGridView1.Refresh()

'DataGridView1.Columns("Total").Index = 8

'OnTrip(DataGridView1)

As I said in the last Post, the cumtotal column should keep a sort of running total for each row. This value will be saved in the table when the user clicks Update. The Update is working fine.

Thanks





Re: Visual Basic General Calculations in a DataGridView

Riquel Dong ĘC MSFT

Hi Ben,

Based on your post, you need to edit the value in the datagridview. I recommend that you can add the SessionTotal by using the DataColumn.Expression property. When you modify the value in other datacolumns, you need to operate directly to the underlying the datasource. In this scenario, you use the datatable object. Please see Manipulating Data in a DataTable .

Best regards,

Riquel.






Re: Visual Basic General Calculations in a DataGridView

WelshWarrior

Thanks Riquel

I am still abit confused. If I make a column called CumulativeTotal at runtime to workout the Cumulative Total, I will not be able to save the info as there is already a Column in the table with the same name. This has to be as otherwise, there is no way for me to keep track of the running total for each student so to speak.

Can I use DataTable alongside DataGridView to perfrom the necessary calculations Will this help sto the Circular Reference Error I have been having

Thanks a lot for your help so far - it is much appreciated!