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

See more:

C#

ASP.NET

SQL

C#4.0

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

I have tried it with other datatypes such as nvarchar it works, which I am to passed in multiple selected values to 1 stored procedure parameter and return the select statement using dytnamic sql to populate my gridview.

Your kind help is kindly appreciated. Thanks:)


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()
       {
DateTime choosenDate = DateTime.MinValue;

               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 += "'" + item.Text + "',";
                            }
                        }

                        selectedDATE = selectedDATE.Substring(0, selectedDATE.Length - 1);
                       DateTime.TryParse(selectedDATE, out choosenDate);
                    }
 if (!choosenDate.Equals(DateTime.MinValue))
                    {
 
                   cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param", SqlDbType.DateTime).Value = choosenDate;
 

                   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