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:
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# :
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:
C#
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
Posted
Updated 17-Apr-18 11:51am
Comments
j snooze 17-Apr-18 17:51pm    
Did you try
cmd.Parameters.Add("@DATE", SqlDbType.DateTime).Value = Convert.Datetime(DropDownList2.SelectedValue)
Member 13771716 17-Apr-18 18:10pm    
Unfortunately returns the same error.

1 solution

You need to convert the value to a DateTime value.

You can use DateTime.TryParse - refer; DateTime.TryParse Method (String, DateTime) (System)[^] or DateTime.TryParse Method (String, IFormatProvider, DateTimeStyles, DateTime) (System)[^]

Alternatively you can use DateTime.TryParseExact - refer; DateTime.TryParseExact Method (System)[^]

Note: If you just want the date value - I.E. - no time component, SQL parses "yyyymmdd" format regardless of the collation of the database;

Kind Regards
 
Share this answer
 

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