I'm filling UltraGrid with a DataTable. When I try to filter, it gives an error that DBNull can't be converted to other types. Is there a way to handle this at the grid-level w/o having to check every single col and every single row?
I've got a lot of cols and a lot of rows so it would be a lot of code and would probably slow it down too much.
Hello Sean,
I recommend trying to set the Nullable property to Nullable.Null for each ultraGrid column where a null value is considered valid. If this doesn't help I recommend you send us a sample demonstrating the behavior.
Quoted from the API
"Different databases deal with null values in different ways. Since the UltraGrid is designed to work with a variety of data sources, it has the ability to query the back end and find out which way to store null values. Depending on the type of connection to the database, this can have a significant impact on performance. If you know how the database handles the storage of null values, you can improve performance by setting the Nullable property to either 1 (NullableNull) or 2 (NullableEmptyString). Setting this value to 0 (NullableAutomatic) will provide a greater range of compatibility, but performance will suffer.
If the database does not support null values, and you attempt to force the storage of nulls by setting Nullable to 1 (NullableNull), an error will result. If you encounter problems when you attempt to save a record that contains a null value, you can change the setting of Nullable which should fix the problem. In any case, you should implement error-checking code to insure that the storage operation succeeded.
The setting of this property controls how the UltraWinGrid control will attempt to store the null value. In some cases, the mechanism used for data binding may change the null value before actually committing it to the database."
Ok, so I created a dt and loaded it manually with data, and I set one of the cols to DBNull.Value. And for some reason I wasn't able to repro the issue when filtering.
I even made sure that the data types were definitely coming through the same in both projects. And they are. So the question is, what setting would there be through the designer that would effect this?
I've pasted the grid from the other project and it doesn't repro in the new project either. And I've disabled a bunch of stuff in the orig project like styles, and some error handling, etc and it still errors when I filter.
Cool. Glad you got it solved. :)
It's probably not immediately obvious that the events like AfterCellUpdate fire not just for normal data cells, but also for the filter cells. It's something to keep in mind when handling any of the cell or row events in the grid.
YEP, THAT FIXED IT!
I was trying to find that IsFilterRow property but didn't hit the right combo.
Aha! AfterCellUpdate does not fire on every keystroke. It fires whenever the cell is committed to the underlying data source. For a normal data cell, that happens when the grid loses focus or you leave the cell. But for the FilterCell, it's probably happening on every keystroke so that the filter gets updated as you type.
This is very easy to solve. Keep using the AfterCellUpdate event. But whatever you are doing in that event, you don't need to do it for the FilterCell, which is not part of the data, anyway.
So all you have to do is check, at the top of AfterCellUpdate, if grid.ActiveCell.Row.IsFilterRow and if so, bail out.
Ok now we're getting somewhere. I allow for updating a single col in the AfterCellUpdate event. It's calling that event after every keystroke, which isn't what I want. I only want it to update when I leave the cell. Ok, I can choose a new event.
The question is though, how do I make it know the difference between the filter row and a data row so that the event won't fire for the filter row?
Okay, that's not an exception, that's a message that is bubbled up from the grid's events.
Just to clarify, from the screen shot, it looks like you are filtering by a value of "2014", so you are not trying to filter by a null value. Is that correct?
The weird thing is that this message seems to indicate that the grid is trying to save a value to the underlying data source - which doesn't make sense. There's no reason why the grid should be updating the data in the middle of a filtering operation. Unless maybe something in your code is doing that. Like maybe you are handing some event of the grid that is getting fired and doing something in your code that is trying to modify the data while in the middle of a filtering operation.
Try trapping the grid's CellDataError and Error events and put breakpoint in there and see if either of those events is getting fired when this message comes up. If so, post the call stack here so we can see why the event got fired.