datetime conversion failed when converting date or time from character string SQL (c#)

I am trying to get my program to work, it is using parameters in an SQL query to add it to the database. When I run the program it comes up with “datetime conversion failed when converting date or time from character string”. I have tried many types of coding to make it work and all have failed. The SQL code itself works. It’s simply the conversion that is breaking. I have tried multiple other posts on here and none have worked. Here’s my code:

string query = $"UPDATE[***].[***]  SET [***]= '@FName', [***]= '@SName', [***] = '@Modified', [***] = '@Arrival', [***] = '@Departure'  where [Id] = '{id}'; ";
Dictionary<string, string> parameters = new Dictionary<string, string>();
parameters.Add("@FName", record.guestGiven);
parameters.Add("@SName", record.guestSurname);
parameters.Add("@Modified", record.ModifiedDate.ToString());
parameters.Add("@Arrival", Convert.ToString(record.arrivalDate));
parameters.Add("@Departure", Convert.ToString(record.departureDate));
mySuilvision.InsertData(query, parameters);

I also tried to do the following

parameters.Add("@Modified", record.ModifiedDate.ToString("yyyy-mm-dd hh:MM:ss));

and all it came up with is the same error and for the last piece of code the VS error was “cannot convert from string to system.IFormatProvider. Any help is appreciated.

Answer

Change your SQL:

string query = $"UPDATE[***].[***]  SET [***]= @FName, [***]= @SName, [***] = @Modified, [***] = @Arrival, [***] = @Departure where [Id] = '{id}'; ";

By putting the parameter names in quotes they are treated as string literals, which won’t work.

Also, why are you adding the ID as a literal? You should add that as a parameter too.

Advertisement

Answer