The conversion of a varchar data type to a datetime data type resulted in an out-of-range value in W-CodePudding

I am working on a program that takes the values of the columns of the table created in mssm into the datagrid, among these columns are columns with dates. In Microsoft SQL Server,the date is written as "1986-09-19 00:00:00.000", and in visual studio as "02.12.2022 0:00:00". How to get rid of this error? Convert these types into a single one?

            dgw.Rows.Add(record.GetString(0), record.GetString(1), record.GetInt32(2), record.GetString(3), record.GetString(4), record.GetInt32(5), record.GetInt32(6), record.GetString(7), *record.GetDateTime(8)*, RowState.ModifiedNew);

                var pub_date = dataGridView_Book.Rows[index].Cells[8].Value.ToString(); //

                var changeQuery = $"UPDATE Book SET Title_book = '{Title}', pages = '{pages}', language_book = '{lang}', format_book = '{format}', ID_Genre = '{genre}', ID_publishing_house = '{public_house}', FILE_path = '{file_path}', Publication_date = '{pub_date}' WHERE ISBN = '{ISBN}'";
                var command = new SqlCommand(changeQuery, database.getConnection());
                command.ExecuteNonQuery();

[date in sql][1]
[date in Visual Studio][2]


  [1]: https://i.stack.imgur.com/eajLS.jpg
  [2]: https://i.stack.imgur.com/25TVN.jpg

CodePudding user response：

Always, always, use strongly-typed parameters when communicating with the database. The GUI front-end can take a string and then should convert it to the proper data-type such as a datetime in your question. The GUI alerts the user if there is a problem converting the string.

//Handle the GUI datetime string. Convert to a datetime field.
DateTime pub_date = DateTime.MinValue;
if (!DateTime.TryParse(
    dataGridView_Book.Rows[index].Cells[8].Value.ToString(),
    out pub_date
    ))
{
    //Handle the error (alert the user) if string doesn't parse to a datetime.
}

string changeQuery = "UPDATE Book SET Title_book = @Title, pages = @Pages, language_book = @Lang, format_book = @Format, ID_Genre = @Genre, ID_publishing_house = @PublishingHouse, FILE_path = @FilePath, Publication_date = @PublicationDate WHERE ISBN = @ISBN";
var command = new SqlCommand(changeQuery, database.getConnection());
command.Parameters.AddWithValue("@Title", Title);
command.Parameters.AddWithValue("@PublicationDate", pub_date);

//Or better yet, specify the dbType for the parameter.
cmd.Parameters.Add("@PublicationDate", SqlDbType.DateTime).Value = pub_date;

... // Add rest of parameters.
command.ExecuteNonQuery();