Update C# DataTable with Return DateTime parameter
I’m trying to convert an existing working C#/Oracle application that is using the Microsoft Oracle Client to use the Oracle Data Provider (ODP.Net). I am using the OracleDataAdapter to insert a row into an Oracle table via SQL text. The SQL command uses the “Returning” clause, returning a datetime variable in a parameter.
OracleParameter p = new OracleParameter();
p.ParameterName = "p24";
p.SourceColumn = "AddDate";
p.OracleDbType = OracleDbType.Date;
p.Direction = ParameterDirection.Output;
insertCommand.Parameters.Add(p);
The Insert to the database works, and the datetime value is returned in the parameter I have defined. But when the OracleDataAdapter.Update() function tries to update the row in my DataTable using the return parameter, I get the following error:
Unable to cast object of type 'Oracle.DataAccess.Types.OracleDate' to type 'System.IConvertible'.Couldn't store <26-MAR-10> in AddDate Column.
Expected type is DateTime. ---> System.InvalidCastException:
Unable to cast object of type 'Oracle.DataAccess.Types.OracleDate' to type 'System.IConvertible'.
at System.Data.Common.DateTimeStorage.Set(Int32 record, Object value)
at System.Data.DataColumn.set_Item(Int32 record, Object value)
--- End of inner exception stack trace ---
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
After the exception is thrown, when I look at the output parameter, the Value member of the output parameter is defined as a Oracle.DataAccess.Type.OracleDate (the DBType of the parameter is set to “DateTime” and the OracleDbType is set to TimeStamp), so I think this is causing the problem, when it is trying to update a field in a DataTable that is defined as a System.DateTime. My question is what do I need to do to make the parameter able to be written to the DataTable? Thanks for any help.