Can I catch GridFormulaEngine exceptions to print messages?

[Byron Tate]

I can''t see where to put try - catch blocks to catch the exceptions raised by the GridFormulaEngine when formulas have bad syntax. All I see in the debugger is a break at the closing } of the static void Main() function.

[Administrator]

In 3.2.1.0, there is a engine.IsFormulaValid method you can use to test whether a string holds a valid formula or not. You can use the grid.CurrentCellValidating event to manage this.

private void gridControl1_CurrentCellValidating(object sender, CancelEventArgs e)
{
	string text = this.gridControl1.CurrentCell.Renderer.ControlText;
	GridFormulaEngine engine = ((GridFormulaCellModel)this.gridControl1.CellModels["FormulaCell"]).Engine;
	string errorMessage, parsedFormula, computedValue;
	if(text.StartsWith("=") && !engine.IsFormulaValid(text, out parsedFormula, out errorMessage, out computedValue))
	{
		this.gridControl1.CurrentCell.ErrorMessage = errorMessage;
		e.Cancel = true;
	}
}

[byron tate]

I have a named range as the f ormula in a cell, like this "=X". This works OK when I call engine.RecalculateRange(GridRangeInfo.Table(),true,true);

But if I type the same thing, "=X" in the same cell, the engine.IsFormulaValid call in CurrentCellValidating returns FALSE.

Does engine.IsFormulaValid only work with formulas that don''t reference other NamedRanges?

[byron tate]

BTW, I''m using a GridDataBoundGrid here. My CurrentCell Validating is:

private void gridDataBoundGrid1_CurrentCellValidating(object sender, CancelEventArgs e)
{
string text = this.gridDataBoundGrid1.CurrentCell.Renderer.ControlText;
string errorMessage, parsedFormula, computedValue;
if(text.StartsWith("=") && !engine.IsFormulaValid(text, out parsedFormula, out errorMessage, out computedValue))
{
this.gridDataBoundGrid1.CurrentCell.ErrorMessage = errorMessage;
e.Cancel = true;
}
}

[Administrator]

Are you storing your formulatags as shown in this KB article? This would be required for a GridDataBoundGrid (and not for a non-virtual Gridcontrol). http://www.syncfusion.com/support/kb/grid/Default.aspx?ToDo=view&questId=104

Here is a GDBG sample that seems to work OK for me. I can type =X in a formulacell without a problem in this sample where X is a namedrange. Are you doing something different?

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

[byron tate]

Hi Clay. I ran your code OK. Then I created these named ranges in place of the one you had:

engine.AddNamedRange("Z","1+sin(45)");
engine.AddNamedRange("X","a1+Y");
engine.AddNamedRange("Y","if(2-2=0,(Z+a1),0)");
engine.AddNamedRange("V","a1+Z");

Then in formula col, type in "=Z" OK, "=X" OK, "=V" OK BUT , "=Y" gives ''invalid chars following an operator''

Notice that ''=X'' works, and it refers to ''Y'' named range.

Looks like a bug .

[byron tate]

I found this problem when trying to get a named range to work again when I retype it in the cell. add "=X" to a DataTable cell. Make the DT the DataSource of the griddataboundgrid.

Create this named range
engine.AddNamedRange("X","A0+2");

Turn on CurrentRowNotationEnabled.

Start the grid. The cell with ''=X'' gets the right value. Then click the cell, and retype "=X". This time the gridDataBoundGrid1_CurrentCellValidating function says "Bad index".

If I change ''A0'' to ''A1'', then restart, I can retype "=X" in the cell OK.

It''s as if engine.IsFormulaValid doesn''t know about the CurrentRowNotationEnabled feature.

[Administrator]

The first problem is that currently, referring to functions in namedranges, is not fully supported. In particular, when namedranges holding function references are used as arguments in outer function references, these are currently not pick up as function references within the namedranges properly.

We will change this so functions can be used in named ranges within other function arguments.

The second problem is that calling IsFormulaValid and passing it a formula does not set the row/col context of the expression. This means that trying to set the proper rowindex for the currentrownotation is failing within the IsFormulaValid call. We will change this as well.

These changes have been completed in our code base, but I am not sure they will make the release candidate 3.3 release. But they should be in the final release.

[byron tate]

Thanks Clay. I''m glad the changes needed to fix this was not such a big deal, as you say you have done it already.

So now this issue is getting critical here. I can''t move forward with my development without this. I must deploy in September. I want to request a private build for this. I''ll do this as an update in my direct-trac issue 20100.