Formula Cell - GridDataBound Grid

[Vinay]

Hi,

I have databound grid having a formula cell. When the grid is bound to the datasource the formula cell in the first row displays the formula to be displayed in last row (but the computed value displayed is from the formula that was supposed to be displayed on the cell).

The rest of the rows shows the correct formulas.

Regards
Vinay

[Administrator]

In a griddataboundgrid, you need ot handle model.QueryCellInfo and Model.SaveCellInfo to dynamically provide/save GridFormulaTag objects as in this KB.

http://www.syncfusion.com/support/kb/grid/Default.aspx?ToDo=view&questId=104&catId=11

Are you doing this? If so, then it is this code that is providing the FormulaTag to the first and last row. And that would be where to look for the problem. It might be something like zero-based and one-based row indexes. (Normmaly, the first data row in the grid is row 1 but in your formulatag cache, this might be row 0???).

[Vinay]

Hi,

Yes, i am using the QueryCellInfo and SaveCellInfo, but i do not find the any problems in the code.

When the Grid is assigned a datasource and if the first cell i select is the formula cell in the first row. The formula displayed is the right formula. But if i select any cell another cell after assigning the datasource and go back to the first row the formula displayed is the formula that is to be diaplayed on the last row.

I have downloaded the sample u gave me and saw the same problem. i just made a small change in the code so that the formula is not fixed always.

if(e.ColIndex == 3 && e.RowIndex > 0 && e.RowIndex <= dt.Rows.Count)
{
e.Style.CellType = "FormulaCell";
if(e.Style.Text.Length == 0)
e.Style.Text = string.Format("=({0}{1} + {2}{1}) / 2", GridRangeInfo.GetAlphaLabel(1), GridRangeInfo.GetNumericLabel(e.RowIndex),GridRangeInfo.GetAlphaLabel(2));
e.Style.FormulaTag = (GridFormulaTag) this.formulaTags[e.RowIndex - 1];
e.Handled = true;
}

Regards
Vinay

[Administrator]

If you set the e.Style.Text to be something, then you have to make sure the cached FormulaTag is properly set as well. Normally, the safest way to to this is to set the savedFormulaTag to null when you change the text that defines the formula. This way, the grid should reparse the formula and recompute it. If you do not do this, then the grid will just use whatewver was last saved in the FormulaTag which may or may not be the proper values for the new formula in the text.

What forum thread holds the sample you are referring to?

[Vinay]

http://www.syncfusion.com/support/kb/grid/Default.aspx?ToDo=view&questId=104&catId=11

[Vinay]

Hi

The problem is that the text (actual formula string) in first row is changing to the last row value. But the computed value is the right value shown.

For ex. if the datasource had the following values
Row 1: =5+2
row 2: 2
row 3: 4
row 4: =3+2

The computed value shown on row1 is 7 but when focus on the cell the text displayed is 3+2 instead of 5+2.

Thanks
Vinay

[Administrator]

How do I see the problem in the sample from the K?. I copied your code into GridQueryCellInfo, and ran the sample. When I click on cell (1,3), I see (A1+B1)/2. and when I click on the last cell in teh column, I see (A10+B10)/2. What do you see when you do this?

I am using 3.0.1.0.

[Vinay]

Hi,

If u select the first row just as the forms loads the correct formula is displayed on the cell. After which if u move to any other row and then return to the first row the formula from the last cell is displayed.

If u select any other row after the form loads and then select the first row the formula from the last cell is displayed on the cell.

Regards
Vinay

[Administrator]

What version are you using? I do not see this behavior in 3.0.1.0.

If I click on 1,3, I see (A1+B1)/2. I thne click on 2,3 and see (A2+B2)/2. I then click back on row 1 and see (A1+B1)/2. Here is a picture of the 3 screens in the order of the clicks.

http://www.syncfusion.com/Support/user/uploads/pict_bb1810fe.zip

[Vinay]

Hi, I am using version 3.0.1.0. Have u made the change to the GridQueryCellInfo event (as in the earlier code which was downloaded from KB the formulas was always forced to be the same). Attached is the CS code. Regards Vinay CS_7051.zip

[Administrator]

I am sorry. It was me who was using the wrong version. I downloaded teh sample and just ran it, and it was pulling in 2.1.0.9 instead of 3.0.1.0.

I was able to avoid this problem by setting the DirectSaveCellInfo property on teh binder before setting the DataSource.

this.gridDataBoundGrid1.Binder.DirectSaveCellInfo = true;
this.gridDataBoundGrid1.DataSource = dt;

[Vinay]

Hi Clay,

Thanks for the help. It solved the problem but i encountered another problem related to circular reference. In the same sample if i assign a formula in row 1 =sum(c2:c9) and then in the row 4 i assign a formula =sum(c1:c3) i get a Stack overflow exception, instead of displaying a circular reference error message.

Regards
Vinay

[Administrator]

I think we will have to address this in our code.

There is an exceptionmanager class that you can use to catch this error, but at that point, there is no good way to back out of the error. And this is why I think we will have to do something to handle this. Here is the event if you want to take a look at it.

Syncfusion.Windows.Forms.ExceptionManager.ExceptionCatched += new Syncfusion.Windows.Forms.ExceptionCatchedEventHandler(ExceptionManager_ExceptionCatched);

[Vinay]

Hi,

I used the ExceptionCatchedEventHandler to catch the error. In the ExceptionCatchedEventHandler i am displaying the exception message, but this message keeps repeating and then there is a stack overflow exception and the application crashes.

Our entire application is dependent upon the GridControl with formulacells and this would be very serious issue for us. Any help will be greatly appreciated.

Regards
Vinay

>I think we will have to address this in our code.
>
>There is an exceptionmanager class that you can use to catch this error, but at that point, there is no good way to back out of the error. And this is why I think we will have to do something to handle this. Here is the event if you want to take a look at it.
>

>Syncfusion.Windows.Forms.ExceptionManager.ExceptionCatched += new Syncfusion.Windows.Forms.ExceptionCatchedEventHandler(ExceptionManager_ExceptionCatched);
>

[Administrator]

As I indicated, I think this will have to be handled in our code. I said I did not know how to clean up the exception even if you caught it, so I do not really have any suggestion on how you can handle cleaning up that exception.

We are working on this problem. We have implemented one solution, but are not sure it is the one we want to use. It is likely that something will be finalized today in regard to exactly how we inted to correct it. But you will have to get new code from us to handle this.

[Vinay]

Hi Clay,

Can u please let us know the status of the Circular reference issue reported. If resolved can u please send us the code as this is a very critical issue in our application.

Thanks & Regards
Vinay

[Administrator]

This has been corrected in our source code. A property as been added that whether circular checks are as part of the validation process when you try to leave a cell. Setting this property,

GridFormulaEngine engine = ((GridFormulaCellModel)this.gridDataBoundGrid1.Model.CellModels["FormulaCell"]).Engine;
engine.DoCircularCheckInValidating = true;

enbales this check which catches the error you mentioned above.

To get a private build, you must submit a direct trac support incident and request it. Or, you can wait to see if this change makes it into the next public release which is in the works. (If there is another test cycle scheduled, this change will make it into this upcoming release. If they decide to release the current build that is in testing, it will not.)