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

Question

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

Navigation

#1 by (4 votes)
#2 by (3 votes)

2

I always have problems with dates when wanting to pass them as a parameter to a SQL statement, I try to use a previous variable, a Convert or a Cast but I 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 try to leave it without converting, then I put it in quotation marks and I added the Convert and I do not know at the end if there is any correct way to make a date happen directly and without these problems.

Any ideas?

sql c# visual-studio

asked by Juan Manuel Palacios 18.05.2016 в 19:47

source

2 answers

3

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 we understand both dFecha and nFactorVenta are of type DateTime

answered by 18.05.2016 в 19:59

My question is related to regular expressions in Java [closed] Pass null values from C # to sql server

score 4 · Accepted 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)
    }
    );

... and:

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];
    }
}