Home / Questions / 10322
Accepted
Juan Manuel Palacios
Asked: 2020-05-19 09:47:21 +0800 CST 2020-05-19 09:47:21 +0800 CST

What is the correct way to pass a date as a parameter?

  • 772

I always have problems with dates when I want to pass them as a parameter to a SQL statement, I try to use a previous variable, a Convertor a Castbut I can never find the correct way.

I have the following statement in C# where I am building an Update statement and I am sending 3 parameters, of which two are numeric values ​​and the last one is the date in question.

UpdateStatement = "UPDATE Productos SET PrecioUnitario = " + nPrecioUnitario.ToString() + ", FactorVenta = " + nFactorVenta.ToString() + ", PrecioUnitarioFecha = CONVERT(DateTime,'" + dFecha.ToString() + "',103) WHERE ProductoId = " + nProductoId;

The error I get is the following:

Conversion failed when converting datetime from character string.

It is not a format issue because we are May 18 and the date is coming 05/18/2016 12:00:00. First I tried to leave it unconverted, then I put it in quotes and added Convert and I don't know at the end if there is any correct way to get a date to pass directly and without these problems.

Any ideas?

c#

2 Answers

  1. Best Answer

    You should always use Parameters, like:

    new SqlParameter()
{
    ParameterName = "@Unit",
    SqlDbType = SqlDbType.VarChar,
    Value = _unit
},

    With more context/better example:

    var dtFillRateResults = SQLDBHelper.ExecuteSQLReturnDataTable(
    ProActWebReportsConstsAndUtils.FILLRATE_STOREDPROC,
    CommandType.StoredProcedure,
    new SqlParameter()
    {
        ParameterName = "@Unit",
        SqlDbType = SqlDbType.VarChar,
        Value = _unit
    },
    new SqlParameter()
    {
        ParameterName = "@Member",
        SqlDbType = SqlDbType.VarChar,
        Value = _memberId
    },
    new SqlParameter()
    {
        ParameterName = "@BegDate",
        SqlDbType = SqlDbType.DateTime,
        Value = Convert.ToDateTime(_begindate)
    },
    new SqlParameter()
    {
        ParameterName = "@EndDate",
        SqlDbType = SqlDbType.DateTime,
        Value = Convert.ToDateTime(_enddate)
    }
    );

    ...Y:

    public static DataTable ExecuteSQLReturnDataTable(string sql, CommandType cmdType, params SqlParameter[] parameters)
{
    using (DataSet ds = new DataSet())
    using (SqlConnection connStr = new SqlConnection(ProActWebReportsConstsAndUtils.CPSConnStr))
    using (SqlCommand cmd = new SqlCommand(sql, connStr))
    {
        cmd.CommandType = cmdType;
        cmd.CommandTimeout = EXTENDED_TIMEOUT;
        foreach (var item in parameters)
        {
            cmd.Parameters.Add(item);
        }

        try
        {
            cmd.Connection.Open();
            new SqlDataAdapter(cmd).Fill(ds);
        }
        catch (SqlException sqlex)
        catch (Exception ex)
        {
        . . .
        return ds.Tables[0];
    }
}
    • 4

  2. You should not bind the values ​​in the string, but assign the parameters to the command object

    The structure should be something like

    using (SqlConnection conn = new SqlConnection("<connection string>"))  
{  
    conn.Open();  

    string query = @"UPDATE Productos SET PrecioUnitario = @preciounitario, 
                        FactorVenta = @fechaventa, PrecioUnitarioFecha = @preciounitariofecha 
                    WHERE ProductoId = @productoid";

    SqlCommand cmd = new SqlCommand(query, conn);  
    cmd.Parameters.AddWithValue("@preciounitario", nPrecioUnitario);  
    cmd.Parameters.AddWithValue("@fechaventa", nFactorVenta);  
    cmd.Parameters.AddWithValue("@preciounitariofecha", dFecha);
    cmd.Parameters.AddWithValue("@productoid", nProductoId);

    cmd.ExecuteNonQuery();  
}

    in this case both are understood dFechaand nFactorVentaare of the typeDateTime

    • 3