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
