CODEWITHSUNDEEP
c#
S. Doe
S. Doe

Reputation: 95

Insert datetime to oracle database using c#

I want to insert date into an Oracle database but getting error with parameterized structure

using (OracleCommand command = new OracleCommand("insert into sometable (startup_time) values (:startup_time)", oraconrpsdb))
{
    command.Parameters.Add("startup_time", Convert.ToDateTime(dtDBInfo.Rows[0]["STARTUP_TIME"]));
    command.ExecuteNonQuery();
}

Value is not empty and it has correct structure but I get this error:

String was not recognized as a valid DateTime

When I debug the value of STARTUP_TIME is : 09:23:07 21/09/19

Upvotes: 2

Views: 1145

Answers (2)

mason
mason

Reputation: 32693

Your first problem is getting values out of dtDBInfo. You didn't create a minimal reproducible example but I'm going to assume it's a DataTable based on the name.

The object contained in that column is already a DateTime (unless youre storing dates as strings, which is a bad idea). There's no need to convert it. You can use generics to actually treat it as a DateTime. Change your code to this:

command.Parameters.Add("startup_time", dtDBInfo.Rows[0].Field<DateTime>("STARTUP_TIME"));

Your next problem is going to be that your arguments to your Add method are incorrect. Add doesn't take the object value in. It takes info about the type of parameter, and returns you an OracleParameter that you can then set the value of. Do this:

command.Parameters.Add("startup_time", OracleType.DateTime).Value = dtDBInfo.Rows[0].Field<DateTime>("STARTUP_TIME");

You may be able to get away with eliminating specifying the type of the DateTime altogether. I don't have an Oracle database to test with, but you can try this:

command.Parameters.Add("startup_time", OracleType.DateTime).Value = dtDBInfo.Rows[0]["STARTUP_TIME"];

Upvotes: 1

Grant Winney
Grant Winney

Reputation: 66439

The Convert.DateTime function doesn't test for every possibility of string input.. it really couldn't since some inputs are just ambiguous. Like, does "21/09/19" represent Sept 19, 2021 or Sept 21, 2019?

You can tell it exactly what format your string is in using DateTime.ParseExact:

DateTime.ParseExact("09:23:07 21/09/19", "hh:mm:ss dd/MM/yy", null)

Upvotes: 1

Related Questions