[Solved] Data types in datatables

[Rakhee]

Hi

I am using telerik radgrid which has GridBoundColumns.

The query that is ran to populate the grid successfully returns the data.

However when the return values from the query is populated in a datatable it is blank and Im not sure why? Using C#

The column on the database is NUMBER datatype.

This is the grid on aspx page.

Any ideas on how to fix this please?

Thanks

Rakhee

[Rumen]

Hi Rakhee,

Thank you for the details! Based on what you've shared, there are two likely causes for the blank cells.

Most likely cause — Oracle ODP.NET type mapping

Since the column is of type NUMBER, this suggests you may be using an Oracle database. When filling a DataTable via OracleDataAdapter (ODP.NET), NUMBER columns are stored as Oracle.ManagedDataAccess.Types.OracleDecimal — a proprietary Oracle struct — instead of a standard .NET decimal. RadGrid cannot render this type and displays blank cells.

Fix 1 — Use SafeMapping (recommended):

var adapter = new OracleDataAdapter(command);
adapter.SafeMapping.Add("BOOKEDTIME", typeof(decimal));
adapter.Fill(dataTable);

Fix 2 — Convert the column after filling:

dataTable.Columns.Add("BOOKEDTIME_FIXED", typeof(decimal));
foreach (DataRow row in dataTable.Rows)
    row["BOOKEDTIME_FIXED"] = OracleDecimal.ToDecimal((OracleDecimal)row["BOOKEDTIME"]);
dataTable.Columns.Remove("BOOKEDTIME");
dataTable.Columns["BOOKEDTIME_FIXED"].ColumnName = "BOOKEDTIME";

Secondary check — DataField casing

Note that DataTable column name lookup in ASP.NET is case-insensitive, so DataField="BookedTime" and DataField="BOOKEDTIME" both work, this is unlikely to be the cause, but worth verifying that the DataField value matches the actual column name returned by your query.

If the above does not solve the issue, could you confirm:

• Which database driver are you using? (Oracle.ManagedDataAccess, System.Data.OracleClient, or something else?)
  
• How are you filling the DataTable — OracleDataAdapter.Fill(), DataReader, or an ORM?
  

This will help us pinpoint the exact fix for your setup.

 

Regards,
Rumen
Progress Telerik

[Rakhee]

Hi Rumen

Thank you that is is very informative.

I applied the second options as I am already looping through the datatable. But I am getting a cast error.

[Rumen]

You are welcome, Rakhee!

Looking at the screenshot, the problem is on this line: dr["BOOKEDTIME_FIXED"] = (Oracle.ManagedDataAccess.Types.OracleDecimal)dr["BOOKEDTIME"];

The cast to OracleDecimal succeeds, but assigning an OracleDecimal struct into a decimal-typed column fails. You need one more cast to decimal. Also handle potential NULL values:

dr["BOOKEDTIME_FIXED"] = dr["BOOKEDTIME"] == DBNull.Value
    ? (object)DBNull.Value
    : (decimal)(Oracle.ManagedDataAccess.Types.OracleDecimal)dr["BOOKEDTIME"];

Or using OracleDecimal.ToDecimal():

dr["BOOKEDTIME_FIXED"] = dr["BOOKEDTIME"] == DBNull.Value
    ? (object)DBNull.Value
    : Oracle.ManagedDataAccess.Types.OracleDecimal.ToDecimal(
        (Oracle.ManagedDataAccess.Types.OracleDecimal)dr["BOOKEDTIME"]);

The key fix is the double cast: first unbox to OracleDecimal, then cast to decimal before storing in the column.

 

Regards,
Rumen
Progress Telerik

[Rakhee]

Good Morning

We found the issue was not with the column it self, it was the query that was being ran that returned the data.

The query has a subquery and one of the joins are date fields, so had to add a format for the date, the bookedTime is now populating.

to_date(FIELD01,'DD-MM-YY') = times.FIELD02

Thanks

Rakhee

[Rumen]

That is a great finding! Thank you for sharing the solution with us, Rakhee! 

Keep up the good work!