ERROR: Failed to convert parameter value from a String to a DateTime

Question

I have faced this error: Failed to convert parameter value from a String to a DateTime when trying to pass multiple selected dates from checkboxlist to my parameter to be used in sql.

I have tried it with other datatypes such as nvarchar and it works when I pass multiple selected values to 1 stored procedure parameter and return the select statement using dynamic sql to populate my gridview.

Ps. In my webserver i'm displaying in checkboxlist as e.g 31-Aug-2013, using DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";. In my sql database, it is displayed as e.g 2013-08-31.

ASPX.CS

    protected void Page_Load(object sender, EventArgs e)
    {
        DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";

        using (SqlConnection conn = new SqlConnection(dbConn))
        {
            try //Call stored procedure
            {

                SqlCommand cmd = new SqlCommand(spddl, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (!IsPostBack)
                {
                    DATE.DataSource = ds.Tables[0];
                    DATE.DataTextField = ds.Tables[0].Columns["DATE"].ToString();
                    DATE.DataBind();



                }
                if (IsPostBack)
                {
                    Bind();
                }    

            }

            catch (Exception i)
            {
                bool exception = true;
                if (exception == true)
                {
                    //txtMessage.Text += e.Message;
                }
            }
        }
    }

public void Bind()
   {
           using (SqlConnection conn = new SqlConnection(dbConn))
       {
           using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
           {
                 String selectedDATE = String.Empty;


                if (DATE.SelectedValue == "All")
                {
                    selectedDATE = "DATE";
                }
                else
                {
                    foreach (ListItem item in DATE.Items)
                    {
                        if (item.Selected)
                        {
                            selectedDATE += "'" + item.Text + "',";
                        }
                    }

                    selectedDATE = selectedDATE.Substring(0, selectedDATE.Length - 1);
                }


               cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param", SqlDbType.DateTime).Value = selectedDATE;


               conn.Open();
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               DataSet ds= new DataSet();
               da.Fill(ds);
               GRIDVIEW.DataSource = ds.Tables[0];
               GRIDVIEW.DataBind();

           }
       }

SQL

ALTER PROCEDURE [dbo].[SP]

@param nvarchar(512)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM TABLENAME WHERE [COLUMN] IN (' + @param + ')'
EXEC sp_executesql @sql;
END

You are going about this the wrong way. Since sql server 2008, you can (and should) use a [table valued parameter](http://stackoverflow.com/questions/31965233/adding-multiple-parameterized-variables-to-a-database-in-c-sharp/31965525#31965525) to pass array like data you stored procedures. For earlier versions you can build your select statement dynamically in your c# code, using parameters. — Zohar Peled, Sep 21 '15 at 10:51

Sachin · Answer 1 · 2015-09-21T11:25:00.750

0

On SQL side..
If [COLUMN] is DateTime type than you should convert it first like

CONVERT(varchar, [COLUMN], 112)

i.e. your SP should be like:

ALTER PROCEDURE [dbo].[SP]

@param nvarchar(512)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM TABLENAME WHERE CONVERT(varchar, [COLUMN], 112) IN (' + @param + ')'
EXEC sp_executesql @sql;
END

On Code side..
Another thing is the parameter, if you are passing multiple Dates in @param than you have to convert them before passing like :

foreach (ListItem item in DATE.Items)
{
    if (item.Selected)
    {
        DateTime dtTemp = Convert.ToDateTime(item.Value);
        selectedDATE += "'" + dtTemp.ToString("yyyyMMdd") + "',";
    }
}

selectedDATE = selectedDATE.Substring(0, selectedDATE.Length - 1);

edited Sep 21 '15 at 11:25

answered Sep 21 '15 at 10:51

Sachin

2,152
1
21
43

Hi there is an error with DateTime in Convert.DateTime(item.Value) and exactly where should I use the convert statement in my sql server SP? PLease kindly advise thanks – James Boer Sep 21 '15 at 11:21
sorry its typo.. try `Convert.ToDateTime(item.Value)` instead of `Convert.DateTime(item.Value);` use that in your `Bind()` method.. – Sachin Sep 21 '15 at 11:24
Hi, I still receive the same error Failed to convert parameter value from a String to a DateTime at da.Fill(ds);. However when I debug, the selectedDATE do read the value that I wanted. please advice. thanks alot – James Boer Sep 22 '15 at 00:16

ERROR: Failed to convert parameter value from a String to a DateTime

1 Answers1