“One of the identified items was in an invalid format” error using Oracle and .NET

While using the Oracle.ManagedDataAccess assembly to access an Oracle database, I received the error:

 One of the identified items was in an invalid format

I had some pretty typical code, using a parameterized query and passing in values like so:

using (OracleConnection conn = new OracleConnection(connString)) {
   using (OracleCommand comm = new OracleCommand(command, conn)) {
   OracleParameter param = new OracleParameter(":BuildDate", OracleDbType.Date);
   param.Value = buildDate.ToString();
   comm.Parameters.Add(param);

The dynamically generated command string was nothing too fancy, and the line in question looked like:

 "SELECT * FROM Coaster WHERE BuildDate = :buildDate"

No worries – seems straightforward. However, this line was causing the aforementioned error. Once more, the Sad Clown of Life had hit me with a low blow.

I found two ways of fixing this. The first way was to replace the three lines related to the OracleParameter with a single line, like so:

 comm.Parameters.Add(":BuildDate", OracleDbType.Date).Value = buildDate;

Another work around is to remove the parameter code altogether  and put the date right in the query:

 "SELECT * FROM Coaster WHERE BuildDate = '" + buildDate.ToString("dd-MMM-yyyy") + "'"

Not the prettiest result, but it resolved the issue. Feel free to leave a comment if you’ve had this same error and fixed in a better way.

Advertisements

, , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: