473,379 Members | 1,355 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

Update to calculated column in datatable

Hey Guys
I am using a datagrid to extract information out of SQL Server
datbase. The fields extracted are category,week,budget,Last
Year,Forecast and Projection. Also i add a calculated column Week% to
the datatable. Also i add calculated row - SPLH(Revenue/Hours) and
AWR(Payroll/Hours) for each week. My grid layout is as follows-
Catgory Week Budget LY Fcst Projection Week%
Revenue 1 2000 1500 2400 2000
Cost 1 1000 900 800 600 30
Hours 1 200 200 300 200
SPLH 1 10 7.5 8 10
AWR 1 2.5 3 2.3 3.5
Dt Pay 1 500 600 700 600
Mgmt Pay 1 200 150 250 100
Benefit 1 100 200 150 350 50

In the above example, week% column defines week percent for categories
based on revenue or payroll. Like cost is based on revenue and beneift
on payroll. After extracting data into data table, i add column week%
and calculate values for each record base don category description.
also have added eventhandler columnchnaging to week% column. Therefore
if someone updates Week% for Benefit and Cost, it will take new
percent and multiply by corresponding week's revnue or payroll
projection. So if i change benefit week 50 to 60, then new benefit
projection will be 420 and if change cost percent to 40, then cost
projection will be 800. I am attaching code for the event below. When
i change cost%, it works fine and updates cost projection and week%.
But when i change var benefit week%, it creates a row above SPLH with
changed number and week%. Also it updates projection in the original
row but keeps week% as same. Any suggestions what i might be doing
wrong in code?
Private Sub PdFcst_ColumnChanging(ByVal sender As Object, ByVal e As
System.Data.DataColumnChangeEventArgs)
Dim strCat, strWk As String
Dim strOrigValue, strChangeValue As String
Dim intWk, intRow As Integer
Dim dblOrig, dblChange, dblCat, dblRev As Double
Dim dblWkPt, dblDtPay, dblMgmtPay As Double
Dim rowRev, rowDtPay, rowMgmtPay, row As DataRow
Dim rowRevCat, rowPayCat, rowDtPayCat, rowMgmtPayCat As
DataRow

strCat = e.Row.Item(0)
strChangeValue = CType(e.ProposedValue, String)
strWk = CInt(e.Row.Item(1))
dblChange = CDbl(strChangeValue)

'If week% is changed
If (e.Column.ColumnName.Equals("Week%") And blUpdate) Then
'Get original Week% value
strOrigValue = e.Row.Item(intWkPtCol,
DataRowVersion.Original)
dblOrig = CDbl(strOrigValue)
'If original value for week% is "" then week%
'is not defined for this category
If (strOrigValue = "") Then
MsgBox("Week% not defined for this Category",
MsgBoxStyle.Critical)
e.ProposedValue = ""
GoTo ExitSub
End If

'Week% cannot be more than 100
If (dblOrig > 100) Then
MsgBox("Week% cannot be more than 100",
MsgBoxStyle.Critical)
e.ProposedValue = strOrigValue
GoTo ExitSub
End If

'If nothing changed then no updates
If (strOrigValue = strChangeValue) Then
GoTo ExitSub
End If

'if entered value is not numeric then exit
If Not IsNumeric(strChangeValue) Then
MsgBox("Week% is not valid", MsgBoxStyle.Critical)
e.ProposedValue = strOrigValue
GoTo ExitSub
End If

'If revenue based category, then get revenue for
'corresponding week and use changed week% to get
'new PdFcst for the revenue based category
If (strCat = "COGS" Or strCat = "Controllables" Or _
strCat = "Var OIF" Or strCat = "Rent Contract" Or _
strCat = "Theo Cost") Then
For Each rowRev In dtPdFcst.Rows
If (rowRev(0) = "Revenue" And rowRev(1) = strWk)
Then
dblRev = CDbl(rowRev(5))
Exit For
End If
Next
blUpdate = False
e.Row.Item(5) = CStr(((dblChange / 100) *
dblRev).ToString("0.00"))
End If

'If payroll based category, then get payroll for
'corresponding week and use changed week% to get
'new PdFcst for the payroll based category
If (strCat = "Var Benefit") Then
For Each row In dtPdFcst.Rows
'Get Dt Pay$
If (row(0) = "Dt Pay" And row(1) = strWk) Then
dblDtPay = CDbl(row(5))
Exit For
End If
Next
For Each row In dtPdFcst.Rows
'Get Mgmt Pay$
If (row(0) = "Mgmt Pay" And row(1) = strWk) Then
dblMgmtPay = CDbl(row(5))
Exit For
End If
Next
blUpdate = False
e.Row.Item(5) = CStr(((dblChange / 100) * (dblDtPay +
dblMgmtPay)).ToString("0.00"))
e.ProposedValue = strChangeValue
'Me.BindingContext(Me.dgWklyPnl.DataSource).EndCur rentEdit()
End If

End If

'If week% has changed then check if number has changed
'from previous value and previous value is not null.
If (e.Column.ColumnName.Equals("Pd Fcst") And blUpdate) Then
'Get original PdFcst value
strOrigValue = e.Row.Item(intPdFcstCol,
DataRowVersion.Original)
dblOrig = CDbl(strOrigValue)

'if entered value is not numeric then exit
If Not IsNumeric(strChangeValue) Then
MsgBox("PdFcst is not valid", MsgBoxStyle.Critical)
e.ProposedValue = strOrigValue
GoTo ExitSub
End If

'If nothing changed then no updates
If (strOrigValue = strChangeValue) Then
GoTo ExitSub
End If

'If revenue changes, then revenue based categories be
'modified by corresponding week%
If (strCat = "Revenue") Then
dblRev = e.ProposedValue
For Each rowRevCat In dtPdFcst.Rows
'Get revenue based category
If ((rowRevCat(0) = "COGS" Or _
rowRevCat(0) = "Controllables" Or rowRevCat(0) =
"Theo Cost" Or _
rowRevCat(0) = "Rent Contract" Or rowRevCat(0) =
"Var OIF") _
And rowRevCat(1) = strWk) Then
dblWkPt = CDbl(rowRevCat(6))
rowRevCat(5) = (dblWkPt / 100) * dblRev
End If
Next
End If

'If DtPay changes, then payroll based categories be
'modified by corresponding week%
If (strCat = "Dt Pay") Then
dblDtPay = e.ProposedValue
'Get Mgmt Pay
For Each rowDtPayCat In dtPdFcst.Rows
If ((rowDtPayCat(0) = "Mgmt Pay") And _
rowDtPayCat(1) = strWk) Then
dblMgmtPay = CDbl(rowDtPayCat(5))
Exit For
End If
Next
dblDtPay = dblDtPay + dblMgmtPay

'Update payroll based category
For Each rowMgmtPayCat In dtPdFcst.Rows
'Get payroll based category
If ((rowMgmtPayCat(0) = "Var Benefit") And _
rowMgmtPayCat(1) = strWk) Then
dblWkPt = CDbl(rowMgmtPayCat(6))
rowMgmtPayCat(5) = (dblWkPt / 100) * dblDtPay
Exit For
End If
Next
End If

'If MgmtPay changes, then payroll based categories be
'modified by corresponding week%
If (strCat = "Mgmt Pay") Then
dblMgmtPay = e.ProposedValue
'Get Mgmt Pay
For Each row In dtPdFcst.Rows
If ((row(0) = "Dt Pay") And _
row(1) = strWk) Then
dblDtPay = CDbl(row(5))
Exit For
End If
Next
dblMgmtPay = dblDtPay + dblMgmtPay

'Update payroll based category
For Each row In dtPdFcst.Rows
'Get payroll based category
If ((row(0) = "Var Benefit") And _
row(1) = strWk) Then
dblWkPt = CDbl(row(6))
row(5) = (dblWkPt / 100) * dblMgmtPay
Exit For
End If
Next
End If
e.ProposedValue = strChangeValue
End If

'Commit all changes
dtPdFcst.AcceptChanges()
dgWklyPnl.SetDataBinding(dsPdFcst, "PdFcst")
ExitSub:
blUpdate = True
End Sub
Any help is appreciated.

Thanks
Manish
Nov 20 '05 #1
0 1767

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Rich G | last post by:
Hi - can anyone comment on why performance would be so slow to update a DataTable row a simple value? With 3000 rows, it takes 5 seconds, which is unacceptable. Sample code below. Suggestions?...
8
by: Dan Keeley | last post by:
Hi, I have a dataset which is used to populate my datagrid something like this: My question is, how do I add a caluclated display column to the datagrid? It will be based on ScoreCardScore... ...
1
by: Sean | last post by:
I have a table I am using to load a DataSet.DataTable to display data on a DataGrid. I have a Column called StartTime that is a DateTime and I have a column called Duration that is an Int32, that...
1
by: Manish | last post by:
Hello Everyone I am having weird problem in my datagrid bounded to datatable. My datatable is populated from SQLServer database. DataGrid has Calculated column Week% and Calculated record, SPLH....
8
by: Zorpiedoman | last post by:
I keep getting a concurrency exception the second time I make a change and attempt to update a dataadapter. It appears this is by design, so there must be something I can do to avoid it. ...
1
by: cindy | last post by:
this is the call private void Page_Load(object sender, System.EventArgs e) { OdbcConnection connection = new OdbcConnection ("DSN=PFW52"); CreateDataAdapter(connection); } this is the code,...
5
by: explode | last post by:
I made a procedure Public Sub Novo(ByVal nova1 As String, ByVal nova2 As String) that creates a new oledbDataAdapter with insert update select and delete commads. I also added that commands can...
2
by: BobLewiston | last post by:
Some of you may have seen my earlier thread “PasswordHash NULL problem”. I’ve started a new thread because investigation has shown that the problem is actually quite different than I previously...
1
by: giovannino | last post by:
Dear all, I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !). 1) Given that I'm not a programmer I can't understand why...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.