SQL Server Filter datetime from SQL to DataGridView with ComboBox

pu82cl6c  于 2023-05-16  发布在  其他
关注(0)|答案(1)|浏览(63)

I have a table TKDV in stored procedures has properties including UsageTime of datatype datetime .

A datagridview to display data from database, a combobox named cbSelectMonth , a combobox named cbSelectYear .

When selecting the "All" option from cbSelectYear , the corresponding "all" option from cbSelectMonth will be automatically selected and I will filter and retrieve all data from table TKDV to be displayed in the datagridview.

When selecting a year from cbSelectYear and a month from cbSelectMonth , data in the database with the same month and year will be selected to be displayed in the datagridview.

When selecting a year from cbSelectYear and "All" option from cbSelectMonth , all data in the database for that selected year will be returned to be displayed in the datagridview.

How to do this?

I tried using array, loop, if else but it doesn't work!

thigvfpy

thigvfpy1#

in this case you would use event handlers for the comboboxes, and then refresh the datagridview based on the selected values. Here's a basic outline of how you might implement this in C#:

First, you would want to handle the selection change event for both the cbSelectYear and cbSelectMonth comboboxes.

private void cbSelectYear_SelectedIndexChanged(object sender, EventArgs e)
{
    RefreshDataGridView();
}

private void cbSelectMonth_SelectedIndexChanged(object sender, EventArgs e)
{
    RefreshDataGridView();
}

Then you would implement the RefreshDataGridView method:

private void RefreshDataGridView()
{
    string selectedYear = cbSelectYear.SelectedItem.ToString();
    string selectedMonth = cbSelectMonth.SelectedItem.ToString();

    string query;

    if (selectedYear == "All" && selectedMonth == "All")
    {
        query = "SELECT * FROM TKDV";
    }
    else if (selectedYear != "All" && selectedMonth == "All")
    {
        query = $"SELECT * FROM TKDV WHERE YEAR(UsageTime) = {selectedYear}";
    }
    else if (selectedYear != "All" && selectedMonth != "All")
    {
        int monthIndex = DateTime.ParseExact(selectedMonth, "MMMM", CultureInfo.InvariantCulture).Month;
        query = $"SELECT * FROM TKDV WHERE YEAR(UsageTime) = {selectedYear} AND MONTH(UsageTime) = {monthIndex}";
    }
    
    // Rather use stored procedures
}

This is a simple example that might need to be adapted based on your specific needs. It assumes that the "All" option is represented as the string "All" in the comboboxes. Also, it assumes that the selectedMonth is the full name of the month, and that's why it is using DateTime.ParseExact to get the month index.

相关问题