CODEWITHSUNDEEP
c#ado.netsqlparameter
Mike Marks
Mike Marks

Reputation: 10139

Conversion failed when converting date and/or time from character string #2

Here's my code:

DateTime Dob = Convert.ToDateTime("1/1/1800");
DateTime Dod = Convert.ToDateTime("1/1/1800");

if (!string.IsNullOrEmpty(p.birthday))
     Dob = Convert.ToDateTime(p.birthday);

if (!string.IsNullOrEmpty(p.deathday))
     Dod = Convert.ToDateTime(p.deathday);

string query = string.Format("insert into actor values (@name, @biography, @placeOfBirth, @profilePath, @dob, @dod)");
SqlCommand command = new SqlCommand(query, connection);

command.Parameters.Add(new SqlParameter("@name", !string.IsNullOrEmpty(p.name) ? p.name : "not available"));
command.Parameters.Add(new SqlParameter("@biography", !string.IsNullOrEmpty(p.biography) ? p.biography : "not available"));
command.Parameters.Add(new SqlParameter("@placeOfBirth", !string.IsNullOrEmpty(p.place_of_birth) ? p.place_of_birth : "not available"));
command.Parameters.Add(new SqlParameter("@profilePath", !string.IsNullOrEmpty(p.profile_path) ? p.profile_path : "not available"));
command.Parameters.Add(new SqlParameter("@dob", Dob));
command.Parameters.Add(new SqlParameter("@dod", Dod));

connection.Open();
command.ExecuteNonQuery();

The error I get is:

Conversion failed when converting date and/or time from character string

The values for Dod and Dob are below:

enter image description here

enter image description here

QUESTION: Is there something wrong with my DateTime objects that SQL doesn't like? If not, what's going on????

Upvotes: 1

Views: 5793

Answers (1)

canon
canon

Reputation: 41705

You're relying on the ordinal position of your columns... and it could be that those differ from the order of the corresponding values. You might try explicitly naming your target columns. I'm guessing at the column names here.

insert into actor 
(Name, Biography, PlaceOfBirth, ProfilePath, DoB, DoD)
values 
(@name, @biography, @placeOfBirth, @profilePath, @dob, @dod)

Upvotes: 1

Related Questions