Click here to Skip to main content
15,947,472 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
C#
        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
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
Posted
Updated 21-Sep-15 0:50am
v2

1 solution

Declare a DateTime object at the beginning of Bind() method:
C#
DateTime choosenDate = DateTime.Minvalue;


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

you state:

C#
DateTime.TryParse(selectedDate, out choosenDate);


If the TryParse succeedded choosenDate has now a different date than 1 january 1

so:
C#
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();
}


see Also: https://msdn.microsoft.com/en-us/library/ch92fbc1(v=vs.110).aspx[^]
 
Share this answer
 
v2
Comments
Member 11878313 21-Sep-15 5:02am    
It does not return any data.
Herman<T>.Instance 21-Sep-15 5:06am    
don't to this either: selectedDATE += "'" + item.Text + "',"
just selectedDate = item.Text;
and then the TryParse.
Did you debug the code and saw step by step the values?
Member 11878313 21-Sep-15 5:29am    
With DATE.DataTextFormatString = "{0:dd-MMM-yyyy}"; it returns operator clashes with int. If I remove the datetextformat string, it does not read any values during debugging
Member 11878313 21-Sep-15 5:29am    
please advice thanks alot
Herman<T>.Instance 21-Sep-15 5:42am    
where does DATE.DataTextFormatString refer to? To a control in your aspx page?
If you set your breakpoint there and step over the line and run the debug line by line. Does it fail somewhere?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900