Change DataType of Bound grid

[Kewalramani Kanchan]

We want to change the datatype of one column in a bound data grid.
Scenario -
1. Import data from Excel sheet, having a specific format. One of the columns in this format is of type Decimal. As we understand, excel has a limit for Decimal to (15,5).
2. We are using the OLEDB connection to read the excel file and fill a Dataset.
3. This Dataset is associated to our grid.
4. Once the data is attached to the data grid, we would like to increase the size of the above field to Decimal(38,10). This is because the data grid is editable and the user should be able to change the value. But just associating the dataset created above, retains the original field size i.e. (15,5).
5. Even after the value is edited on the grid, the format of that column changes to an exponential value, thus losing the precision of the decimal value entered, instead of retaining the value as entered by the user.

What would be the best way to do this?

[Administrator]

I think it is the DataSet that you have to modify, not the GridDataBoundGrid. The grid just reflects what is in the DataSet. So, if the DataColumn cannot hold that many digits, the grid cannot put that many digits into it.

I suspect the DataColumn.DataType for that column is set to typeof(double), and that is what things are being truncated the way they are. So, after loading teh DataSet, you might try just explicitly setting DataColumn.DataType to typeof(decimal) to see if that will work. If not, the only thing I woul dknow to do is to explicilty add a DataColumn of decimal type and then move the values from the old column to teh new column, and finally remove the old column.

I do not think working with the grid will be able to help with this task as the grid cannot save more information than the column DataType allows.

[Kewalramani Kanchan]

Hey Clay:

Thanks for your help.
I fully agree that this should be handled at the Dataset level rather than grid.
We have tried implementing a similar approach but that affects the performance of the application (as we are dealing with large number of records).
Just wanted to check if there is an alternative, we appreciate you sparing the time.

[K Kewalaramani]

Hi Clay:

I have a column with Data type Decimal(38,10)with Maxlength set to 38. The grid doesnot allow me to enter data beyond 30 digits (including the decimal point). The max value allowed should be "123456789012345678901234567.1234567890" but when we enter this data it gets converted to "123456789012345678901234567.12"
I though that changing the format would help but if I allowed to enter "123456789012345678901234.12345"

I am not sure where to change. Any help will be greatly appreciated.

[Kewalramani Kanchan]

Just wanted to add one more point. Even though the data type is Decimal (38,10) the grid accepts values with more than 10 precisions (only check is that the total digits should be 30). Incase it increases beyond 30 then data is truncated from right. Is there a way to restrict this behavior?

Thanks.

[Administrator]

I suspect the grid is being restricted to the decimal System.DataType which is roughly 30 significant digits if the style.CellValueType of that column is being set to typeof(decimal).

If you want the grid to handle more than that number of significant digits, then set the style.CellValueType of that column to typeof(string). If you do this, then it will be up to you to validate the entries to make sure that they are as you require. You can use the CurrentCellValidating event to do validation as your user leaves the cell, or you can use CurrentCellValidateString to validate things on each keystroke.

[Kewalramani Kanchan]

Clay :

Could you help me as to how I can set the format of the cell to accept decimal format data with max of (28,10) values only.

It is OK to appended trailing zeros.

[Administrator]

Here is a try at using CurrentCellvalidateString to do this. Cellvalidation_6669.zip