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;
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.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