Insert c# DateTime object to Oracle Table using OleDbDataAdapter

[jh]

I have a table defined as:
create table product_price
(
productId int,
price number(20,10),
price_date date,
primary key (productId)
);

I am reading through a price file in c# and I would like to insert a row
into the table for every price+date+productId combination. I am using
the DataAdapter insertCommand and Table.Rows.Add(newRow) method but it
fails when setting the date of the new row.

// Setting the insert command
priceDA.InsertCommand = priceDA.SelectCommand.Connection.CreateCommand
();
priceDA.InsertCommand.CommandText = "insert into product_price
(productId, price, price_date)" +
"values (@productId, @price, TO_DATE('@priceDate', 'YYYYMMDD'))";

// Adding the row
private void addPriceRow (int prodId, decimal price, DateTime priceDate,
DataTable priceTable)
// priceDate and other params defined elsewhere
DataRow newPriceRow = priceTable.NewRow();
newPriceRow["productId"] = prodId;
newPriceRow["price"] = prodPrice;
newPriceRow["price_date"] = priceDate;

priceDA.Update(priceDS, "product_price"); // Dies here with an oracle
error ORA-00936 missing expression
priceDS.AcceptChanges();


I think this is some sort of date format error, so I tried: newPriceRow
["price_date"] = priceDate.ToString("yyyyMMdd"); but that line fails at
runtime with: "String was not recognised as a valid DateTime"

Any ideas how I can save this date to the table?
Many thanks in advance.

 

[Miha Markic]

Why the heck don't you use parameter for date, too?

 

[jh]

Why the heck don't you use parameter for date, too?

Wasn't that what just I outlined in the code?
I'm new to dotnet and C# data access so apologies if i've missed
something obvious.

I've also tried this which doesn't work:
priceDA.InsertCommand.CommandText = "insert into product_price
(productId, price, price_date)" + "values (@productId, @price,
@priceDate)";
<snip code>
newPriceRow["productId"] = prodId;
newPriceRow["price"] = prodPrice;
newPriceRow["price_date"] = priceDate;

priceDA.Update(priceDS, "product_price"); // Still dies here with the
oracle error
priceDS.AcceptChanges();

 

[Miha Markic]

What is the error this time?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Why the heck don't you use parameter for date, too?

Wasn't that what just I outlined in the code?
I'm new to dotnet and C# data access so apologies if i've missed
something obvious.

I've also tried this which doesn't work:
priceDA.InsertCommand.CommandText = "insert into product_price
(productId, price, price_date)" + "values (@productId, @Price,
@priceDate)";
<snip code>
newPriceRow["productId"] = prodId;
newPriceRow["price"] = prodPrice;
newPriceRow["price_date"] = priceDate;

priceDA.Update(priceDS, "product_price"); // Still dies here with the
oracle error
priceDS.AcceptChanges();

 

[jh]

What is the error this time?

I got the same Oracle error as before: "ORA-00936 missing expression."

I would assume that the DataTable price_date field is converting the
value into some generic string and replacing the @priceDate parameter in
the InsertCommand with this string. Then this string is Obviously
incompatible with Oracle's date format.
If I could view the insert statement before it is executed then this
would help, but the VS debugger does not allow me to debug into the code
for the DataAdapter.Update() method.

If you have any pointers on how to debug this better that would help. I
really do not want to store the date as a string field in the database
for lots of reasons.

 

[Miha Markic]

Right, I've should seen it sooner. Oracle doesn't use @ for a parameter
prefix. Instead it uses :.
So, your sql statament should read like "insert into (..., ...) values
first, :second, ...)
About debugging issues statements: oracle probably has a tool that lets you
peek into communication between client and server (SQL has SQL profiler).