error insert DateTime c# type to SQL DateTime column

Question

i am trying to make INSERT command to my SQLEXPRESS db and recieve error while trying to enter a value to my DateTime column.

this is the sql command i use:

SqlDateTime sTime = new SqlDateTime(book.PublishedDate);                

string sql = string.Format("Insert into Books" +
"(Name, PublishDate, IsInternal) Values" +
"('{0}', '{1}', '{2}')",
book.Name, sTime.Value, book.IsInternal);

book.PublishedDate - is DateTime type and the PublishedDate column is sql DateTime

i recieve the following error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

how can i resolve that ?

Damien_The_Unbeliever · Accepted Answer · 2011-09-29 13:54:27Z

2

Use parametrised queries. They're supported in practically any data access technology you might be using. And they allow you to keep treating dates as dates, instead of converting everything into strings.

E.g. (ADO.Net SqlCommand)

SqlCommand cmd = new SqlCommand("Insert into Books (Name,PublishDate,IsInternal) Values (@Name,@PublishDate,@IsInternal)");
cmd.Parameters.Add(new SqlParameter("@Name", System.Data.SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@PublishDate", System.Data.SqlDbType.DateTime));
cmd.Parameters.Add(new SqlParameter("@IsInternal", System.Data.SqlDbType.Bit));

cmd.Parameters["@Name"].Value = book.Name;
cmd.Parameters["@PublishDate"].Value = book.PublishedDate;
cmd.Parameters["@IsInternal"].Value = book.IsInternal;

The single largest source of errors people make when they report datatype issues between their client code and an SQL database is where, for whatever reason, they've converted everything to strings. Not only is this usually less efficient, but you're also relying on at least two conversions to happen correctly in between (Type -> string and string -> Type), and frequently at least one of those conversions will be left to whatever the default conversion function(s) are.

edited Sep 29, 2011 at 13:54

answered Sep 29, 2011 at 13:40

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

kaycee Over a year ago

now when using the parametrised queries i get error while i have null values on certain fields: The parameterized query '(@Name nvarchar(57),@PublishDate datetime,@IsValid bit,@IsApprov' expects the parameter '@Author', which was not supplied.

Damien_The_Unbeliever Over a year ago

@kaycee - I can't see your code. The code I've posted so far is almost exactly equivalent to the code you've posted so far.

kaycee Over a year ago

i have a field in the query which is null cmd.Parameters["@Author"].Value = book.Author; and if book.Author == null, then i get the error i just mentioned... it seems like it doesn't know to handle with null value

Damien_The_Unbeliever Over a year ago

@Kaycee - does cmd.Parameters["@Author"].Value = (object)(book.Author) ?? DBNull.Value; work?

kaycee Over a year ago

yes Damien it works. but it looks like it demand a lot of work: to check if the value is null -> then DBNull it else --> insert the value i have many fields...

|

Collectives™ on Stack Overflow

error insert DateTime c# type to SQL DateTime column

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related