使用System.Data.SqlClient带DateTime参数查询填充下拉框失败求助

核心问题点 #

1. 旧数据残留：注释掉了ComboSeats.Items.Clear();，导致切换选项时之前的座位不会被清除，出现“显示所有座位”的假象。
2. 座位生成逻辑错误：通过COUNT获取座位数量后循环生成连续座位号，忽略了两种关键情况：
   • 该日期下的座位号可能不连续（比如部分座位已售出或不存在）
   • 当COUNT返回0时下拉框无内容，但旧数据没清的话会继续显示之前的内容
3. 查询逻辑偏差：用COUNT(DISTINCT SeatNumber)只能得到有效座位数量，无法获取实际存在的座位号，无法实现精准过滤。

修复步骤 #

1. 恢复下拉框清空操作 #

取消注释ComboSeats.Items.Clear();，确保每次切换时先清空旧选项：

ComboSeats.Items.Clear();

2. 修改SQL查询与座位填充逻辑 #

直接查询该日期、区域、排下的所有SeatNumber，将实际存在的座位号添加到下拉框，替代“按数量循环生成”的逻辑：

// 替换原查询语句
string seatQuery = "SELECT DISTINCT SeatNumber FROM Stadium WHERE SectionRow = @SectionRow AND Section = @Section AND GameDate = @GameDate ORDER BY SeatNumber";

using SqlCommand seatCommand = new(seatQuery, seatConnection);

try
{
    seatCommand.Parameters.AddWithValue("@SectionRow", selectedRow);
    seatCommand.Parameters.AddWithValue("@Section", selectedSection);
    seatCommand.Parameters.Add("@GameDate", SqlDbType.DateTime).Value = selectedDate;

    seatConnection.Open();
    using SqlDataReader reader = seatCommand.ExecuteReader();
    
    while (reader.Read())
    {
        string seatNumStr = reader["SeatNumber"].ToString();
        ComboSeats.Items.Add(seatNumStr);
    }
}
catch (Exception ex)
{
    MessageBox.Show("Error: " + ex.Message);
}

3. 额外验证项 #

• 若ComboGameDates绑定的是字符串而非DateTime，需调整类型判断逻辑：

  if (DateTime.TryParse(ComboGameDates.SelectedItem?.ToString(), out DateTime selectedDate))
  {
      // 后续查询逻辑
  }
  

• 确认数据库中GameDate字段类型与参数类型匹配，避免日期格式不匹配导致查询无结果。

完整修改后的代码片段 #

private void ComboRows_SelectedIndexChanged(object sender, EventArgs e)
{
    UpdateBtnCheckPriceEnabledState();

    ComboSeats.Items.Clear(); // 恢复清空操作

    if (ComboRows.SelectedItem is string selectedRow && 
        ComboSections.SelectedItem is string selectedSection && 
        DateTime.TryParse(ComboGameDates.SelectedItem?.ToString(), out DateTime selectedDate))
    {
        string seatConnectionString = Constants.connectString;

        using SqlConnection seatConnection = new(seatConnectionString);

        string seatQuery = "SELECT DISTINCT SeatNumber FROM Stadium WHERE SectionRow = @SectionRow AND Section = @Section AND GameDate = @GameDate ORDER BY SeatNumber";

        using SqlCommand seatCommand = new(seatQuery, seatConnection);

        try
        {
            seatCommand.Parameters.AddWithValue("@SectionRow", selectedRow);
            seatCommand.Parameters.AddWithValue("@Section", selectedSection);
            seatCommand.Parameters.Add("@GameDate", SqlDbType.DateTime).Value = selectedDate;

            seatConnection.Open();
            using SqlDataReader reader = seatCommand.ExecuteReader();
            
            while (reader.Read())
            {
                string seatNumStr = reader["SeatNumber"].ToString();
                ComboSeats.Items.Add(seatNumStr);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }
    }
}

内容的提问来源于stack exchange，提问作者totters-j13