CODEWITHSUNDEEP
c#.netsql-server
mzurita
mzurita

Reputation: 690

Error updating table in SQL Server: Conversion failed when converting character string to smalldatetime data type

In C#, I want to write an update query with smalldatetime columns, I have seen several posts on Stackoverflow but I didn't find one to solve the error:

Conversion failed when converting character string to smalldatetime data type.

I have a table RESERVAS, with 3 columns ID_RESERVA (int), LLEGADA (smalldatetime), SALIDA (smalldatetime)

The code:

 string strQuery = "UPDATE " + DB_TABLENAME + "  SET " +
 "LLEGADA = CONVERT(smalldatetime, '@LLEGADA', 126), " +
 "SALIDA = CONVERT(smalldatetime, '@SALIDA', 126) " +
 "WHERE ID_RESERVA = @ID_RESERVA";

 SqlCommand cmd = new SqlCommand(strQuery, con); // con = SqlConnection
 cmd.Parameters.Add("@ID_RESERVA", SqlDbType.Int);
 cmd.Parameters.Add("@LLEGADA", SqlDbType.SmallDateTime);
 cmd.Parameters.Add("@SALIDA", SqlDbType.SmallDateTime);

 cmd.Parameters["@ID_RESERVA"].Value = Convert.ToInt32(stringWithIdReserva);
 cmd.Parameters["@LLEGADA"].Value = stringWithLLegada.Replace(" ", "T"); //Real string value: "2015-03-30 00:00:00"
 cmd.Parameters["@SALIDA"].Value = stringWithSalida.InnerText.Replace(" ", "T"); //Real string value: "2015-04-01 00:00:00"

  try
  {
      cmd.ExecuteNonQuery();
  }
  catch (SqlException ex)
  {
      Console.WriteLine(ex.Message);
  }

Upvotes: 0

Views: 1766

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

That is because you have wrapped your parameters with single tick marks. The way you have it coded it is trying to parse the string literals instead of the values in your parameters.

string strQuery = "UPDATE " + DB_TABLENAME + "  SET " +
 "LLEGADA = CONVERT(smalldatetime, @LLEGADA, 126), " +
 "SALIDA = CONVERT(smalldatetime, @SALIDA, 126) " +
 "WHERE ID_RESERVA = @ID_RESERVA";

 SqlCommand cmd = new SqlCommand(strQuery, con); // con = SqlConnection
 cmd.Parameters.Add("@ID_RESERVA", SqlDbType.Int);
 cmd.Parameters.Add("@LLEGADA", SqlDbType.SmallDateTime);
 cmd.Parameters.Add("@SALIDA", SqlDbType.SmallDateTime);

 cmd.Parameters["@ID_RESERVA"].Value = Convert.ToInt32(stringWithIdReserva);
 cmd.Parameters["@LLEGADA"].Value = stringWithLLegada.Replace(" ", "T"); //Real string value: "2015-03-30 00:00:00"
 cmd.Parameters["@SALIDA"].Value = stringWithSalida.InnerText.Replace(" ", "T"); //Real string value: "2015-04-01 00:00:00"

  try
  {
      cmd.ExecuteNonQuery();
  }
  catch (SqlException ex)
  {
      Console.WriteLine(ex.Message);
  }

Upvotes: 4

Related Questions