Problem with inserting date - Oracle and C#

Question

Hi

I have Date Var in Oracle, and I try to insert Data from my C# program

sql = "insert into Table(MyDate) values (" + convert.todatetime(txt) + ")";

I get an Error, what can i do ?

Answer 1

Use parameters. It's going to solve your problem and prevent injection.

Answer 2

Oracle expects it to be an actual date value, not just a string that looks like a date. You have to use the TO_DATE() function to explain how your string is formatted, something like this:

INSERT INTO Table (myDate)
VALUES(TO_DATE('2009-03-30 12:30:00', 'YYYY-MM-DD HH:mi:ss'));

Answer 3

1. What error do you get?
2. What is the value of txt.
3. Why are you converting the string txt to a DateTime only to convert it back to a string again?
4. The default format for a DateTime is probably not the correct format for Oracle to parse, you may need to use .ToString() on the DateTime to get it into the correct format.

Answer 4

Try using DateTime.TryParse(text) or DateTime.Parse(text)

Answer 5

cmd.CommandText = "INSERT INTO Table (myDate)VALUES(:dateParam)";

cmd.Parameters.Add(new OracleParameter(":dateParam", OracleType.DateTime))
    .Value = DateTime.Now;

cmd.ExecuteNonQuery();

Answer 6

Please bind your variables (like ocdecio tells) ! Not only does it prevent sql injection it is also much faster. Especially in a multi concurrency situation. Read for example here: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28844/building_odp.htm#CEGCGDAB .

"Bind variables are placeholders inside a SQL statement. When a database receives a SQL statement, it determines if the statement has already been executed and stored in memory. If the statement does exist in memory, Oracle Database can reuse it and skip the task of parsing and optimizing the statement. Using bind variables makes the statement reusable with different input values. Using bind variables also improves query performance in the database, eliminates the need for special handling of literal quotation marks in the input, and protects against SQL injection attacks."