'Failed to convert parameter value from a string to a datetime.'

See more:

ASP.NET

SQL

C#6.0

I am currently getting this error when trying to select a date from a drop down list, once the date is selected it should Display data in a GridView depending on the drop down list value. This is achieved using a 'Where' statement equals the value of the selected index.

I understand that it is a problem with the way the dates are being displayed in the drop down list, as opposed to the way they are saved in the database. So I have tried to swap the format from dd/mm/yyyy to yyyy/mm/dd in the drop down list, to see if this fixes the problem. But I can't seem to get it to work.

Please can someone recommend a fix to this?

Drop Down List selected index change C# :

protected void DropDownList2_SelectedIndexChanged(object sener, EventArgs e)
        {
            String query = "SELECT Stock_Take.Username, Item.ItemID, Item.ItemDesc, Stock_Take_Item.BarQuantity, Stock_Take_Item.StorageQuantity, Stock_Take.StockTakeIDNew FROM Item INNER JOIN Stock_Take_Item ON Item.ItemID = Stock_Take_Item.ItemID INNER JOIN Stock_Take ON Stock_Take_Item.StockTakeIDNew = Stock_Take.StockTakeIDNew where Stock_Take.Username = @USER AND Stock_Take.StockDate = @DATE";

            SqlConnection con = new SqlConnection(@"Data Source=(local)\;Initial Catalog=SmallBatch;Integrated Security=True;");
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);

            cmd.Parameters.Add("@USER", SqlDbType.VarChar).Value = DropDownList1.SelectedValue;
            cmd.Parameters.Add("@DATE", SqlDbType.DateTime).Value = DropDownList2.SelectedValue;
           // DateTime date = Convert.ToDateTime(DropDownList2.SelectedValue.ToString());
            SqlDataReader reader = cmd.ExecuteReader();
            GridView1.DataSource = reader;
            GridView1.DataBind();
            con.Close();
         

        }

Binding the dates from the database to the drop down list C# code:

private void BindDropDownList2(String field)
        {
            DataTable dataTable = new DataTable();
            SqlConnection con = new SqlConnection(@"Data Source=(local)\;Initial Catalog=SmallBatch;Integrated Security=True;");
            try
            {
                con.Open();
                String Query = "Select StockDate, StockTakeIDNEW from Stock_Take WHERE Username = @Value1";
                SqlCommand sqlCmd = new SqlCommand(Query, con);
                sqlCmd.Parameters.AddWithValue("@Value1", field);
               
                SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
                sqlDa.Fill(dataTable);
                if (dataTable.Rows.Count > 0)
                {
                    DropDownList2.DataSource = dataTable;
                    DropDownList2.DataTextField = "StockDate";
                    DropDownList2.DataValueField = "StockTakeIDNew";
                   // DropDownList2.DataTextFormatString = "(yyyy/MM/dd}";
                    DropDownList2.DataBind();
                }
               
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string msg = "Fetch Error";
                msg += ex.Message;
                throw new Exception(msg);
            }
            finally
            {
                con.Close();
            }


        }

What I have tried:

*I also have another drop down list, that a user selects a Username from, which in turn affects what dates are shown in the DropDownList2