CODEWITHSUNDEEP
c#sqldatetime
kaycee
kaycee

Reputation: 1309

error insert DateTime c# type to SQL DateTime column

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 ?

Upvotes: 0

Views: 1905

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

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.

Upvotes: 2

Related Questions