c# - 更改 gridview 内下拉菜单的日期格式

Question

我在 asp gridview 中有一个下拉列表，可以根据所选日期进行过滤。但是，当我在下拉列表中进行选择时，出现错误，MySql.Data.MySqlClient.MySqlException:第 1 行的“dateValue”列的日期值不正确:“9/12/2018 12:00:00 AM”。

下面是客户端代码:

<asp:GridView ID="gdvTM" runat="server" AutoGenerateColumns="False" ShowHeaderWhenEmpty="true" AllowPaging="true" OnPageIndexChanging="gdvTM_PageIndexChanging" DataKeyNames="ID" PageSize="10" CssClass="cssgridview" AlternatingRowStyle-BackColor="#d5d8dc" >
 <Columns >
 <asp:TemplateField>
 <HeaderTemplate>
  Date:
  <asp:Label ID="lbldate" Text="date" Visible="false" runat="server"></asp:Label>
  <asp:DropDownList ID="ddlgvdate" DataTextFormatString="{0:yyyy-MM-dd}" runat="server" OnSelectedIndexChanged="DropDownChange" AutoPostBack="true" AppendDataBoundItems="true">
  </asp:DropDownList>
  </HeaderTemplate>
  <ItemTemplate >
  <asp:Label ID="lbldate1" runat="server" Text='<%# Eval("date", "{0:yyyy-MM-dd}") %>'></asp:Label>
  </ItemTemplate>
  </Columns>
</asp:GridView>

下面是服务器端代码:

protected void Page_Load(object sender, EventArgs e)
{
   if (!IsPostBack)
   {
       BindGrid();
   }
}
private void BindDropDownList()
{
     PopulateDropDown((cells.FindControl("ddlgvdate") as DropDownList), (cells.FindControl("lbldate") as Label).Text);
}
private void PopulateDropDown(DropDownList ddl, string columnName)
    {
        ddl.DataSource = BindDropDown(columnName);
        ddl.DataTextField = columnName;
        ddl.DataValueField = columnName;
        ddl.DataBind();
        ddl.Items.Insert(0, new ListItem("Please Select", "0"));
    }
private void BindGrid()
    {
        DataTable dt = new DataTable();
        String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        MySqlConnection con = new MySqlConnection(strConnString);
        MySqlDataAdapter sda = new MySqlDataAdapter();
        MySqlCommand cmd = new MySqlCommand("GetApprovedData1");
        cmd.CommandType = CommandType.StoredProcedure;
        string date = null;
        DateTime dateValue1 = Convert.ToDateTime(date);
        string dateValue = dateValue1.ToString("yyyy-MM-dd");
        dateValue = null;
        if (ViewState["Date"] != null && ViewState["Date"].ToString() != "0")
        {
            dateValue = ViewState["Date"].ToString();
        }
        cmd.Parameters.AddWithValue("dateValue", dateValue);
        cmd.Connection = con;
        sda.SelectCommand = cmd; 
        sda.Fill(dt);
        gdvTM.DataSource = dt;
        int i = dt.Rows.Count;
        gdvTM.DataBind();
        this.BindDropDownList();
        TableCell cell = gdvTM.HeaderRow.Cells[0];
        setDropdownselectedItem(ViewState["Date"] != null ? (string)ViewState["Date"] : string.Empty, cell.FindControl("ddlgvdate") as DropDownList);
    }
 private void setDropdownselectedItem(string selectedvalue, DropDownList ddl)
    {
        if (!string.IsNullOrEmpty(selectedvalue))
        {
            ddl.Items.FindByValue(selectedvalue).Selected = true;
        }
    }
protected void DropDownChange(object sender, EventArgs e)
    {
        DropDownList dropdown = (DropDownList)sender;
        string selectedValue = dropdown.SelectedItem.Value;
        switch (dropdown.ID.ToLower())
        {
                case "ddlgvdate":
                ViewState["Date"] = selectedValue;
                break;
        }
        this.BindGrid();
    }
 private DataTable BindDropDown(string columnName)
    {
        String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        MySqlConnection con = new MySqlConnection(strConnString);
        MySqlCommand cmd = new MySqlCommand("SELECT DISTINCT (" + columnName + ") FROM approved WHERE " + columnName + " IS NOT NULL", con);
        MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        return dt;
    }

下面是 MySql 存储过程:

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetApprovedData1`
(in dateValue     date)
BEGIN
SELECT *
   FROM approved
   WHERE 
   (dateValue IS NULL OR date = dateValue);
END

请告诉我如何以正确的格式传递日期。提前致谢。

Answer 1

感谢约翰的建议，我通过使用 Nullable DateTime 解决了这个问题，如下所示

 private void BindGrid()
{
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
    MySqlConnection con = new MySqlConnection(strConnString);
    MySqlDataAdapter sda = new MySqlDataAdapter();
    MySqlCommand cmd = new MySqlCommand("GetApprovedData1");
    cmd.CommandType = CommandType.StoredProcedure;
    DateTime? dateValue = null;
    if (ViewState["Date"] != null && ViewState["Date"].ToString() != "0")
    {
        dateValue = DateTime.Parse(ViewState["Date"].ToString());
    }
    cmd.Parameters.AddWithValue("dateValue", dateValue);
    cmd.Connection = con;
    sda.SelectCommand = cmd; 
    sda.Fill(dt);
    gdvTM.DataSource = dt;
    int i = dt.Rows.Count;
    gdvTM.DataBind();
    this.BindDropDownList();
    TableCell cell = gdvTM.HeaderRow.Cells[0];
    setDropdownselectedItem(ViewState["Date"] != null ? (string)ViewState["Date"] : string.Empty, cell.FindControl("ddlgvdate") as DropDownList);
}