Bind (Populate) GridView using multiple DropDownList in ASP.Net

Hi indradeo,

Check this example. Now please take its reference and correct your code. Make sure your procedure is working correctly.

HTML

<form id="form1" runat="server">
    <table align="center" border="1" cellpadding="4" cellspacing="2" style="border: 1px solid #999999; width: 812px; margin-left: 0px;">
        <tr>
            <td class="auto-style16">
                <strong>Select Year:</strong></td>
            <td>
                <asp:DropDownList ID="ddl_Year" runat="server" Width="200px" Height="25px">
                </asp:DropDownList></td>
            <td class="auto-style13">
                <strong>Select Month:</strong></td>
            <td class="auto-style6">
                <asp:DropDownList ID="ddl_Month" runat="server" Width="190px" Height="25px" CssClass="auto-style8">
                </asp:DropDownList>
            </td>
            <td class="auto-style9">
                <asp:Button ID="Button1" runat="server" Text="Show" OnClick="Button1_Click" CssClass="auto-style7" Height="26px" Width="115px" />
            </td>
        </tr>
        <tr>
            <td class="auto-style15" colspan="5">
                <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
            </td>
        </tr>
    </table>
    <asp:GridView ID="GridView1" runat="server" >
    </asp:GridView>
</form>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        int currentYear = DateTime.Today.Year;
        ddl_Year.Items.Clear();
        ddl_Year.Items.Add("Select");
        for (int i = 2; i >= 0; i--)
        {
            int fy = currentYear - i;
            int fy1 = fy + 1;
            if (DateTime.Now.Date > Convert.ToDateTime(fy + "-03-31").Date)
            {
                ddl_Year.Items.Add(fy.ToString() + "-" + fy1.ToString());
            }
        }
        DateTime currentDate = DateTime.Now;
        List<ListItem> items = new List<ListItem>();
        items.Add(new ListItem
        {
            Text = currentDate.AddMonths(-1).Month.ToString(),
            Value = currentDate.AddMonths(-1).Month.ToString()
        });
        items.Add(new ListItem
        {
            Text = currentDate.Month.ToString(),
            Value = currentDate.Month.ToString()
        });
        ddl_Month.DataSource = items;
        ddl_Month.DataTextField = "Text";
        ddl_Month.DataValueField = "Text";
        ddl_Month.DataBind();
    }
}

private SqlCommand _sqlCommand;
private SqlDataAdapter _sqlDataAdapter;
DataSet _dtSet;
public void CreateConnection()
{
    string strConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection _sqlConnection = new SqlConnection(strConnectionString);
    _sqlCommand = new SqlCommand();
    _sqlCommand.Connection = _sqlConnection;
}
public void OpenConnection()
{
    _sqlCommand.Connection.Open();
}
public void CloseConnection()
{
    _sqlCommand.Connection.Close();
}
public void DisposeConnection()
{
    _sqlCommand.Connection.Dispose();
}

public void connection()
{
    CreateConnection();
    OpenConnection();
}

protected void Button1_Click(object sender, EventArgs e)
{
    connection();
    _sqlCommand.CommandText = "count";
    _sqlCommand.CommandType = CommandType.StoredProcedure;
    _sqlCommand.Parameters.AddWithValue("@Month", ddl_Month.SelectedValue);
    if (ddl_Year.SelectedIndex > 0)
    {
        _sqlCommand.Parameters.AddWithValue("@Year", ddl_Year.SelectedValue);
    }
    else
    {
        _sqlCommand.Parameters.AddWithValue("@Year", (object)DBNull.Value);
    }
    SqlDataAdapter sda = new SqlDataAdapter(_sqlCommand);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    if (dt.Rows.Count > 0)
    {
        GridView1.Visible = true;
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    else
    {
        GridView1.Visible = false;
        Label1.Visible = true;
        Label1.Text = "The Records for Month " + ddl_Month.SelectedValue + " is not Available.";
    }
    CloseConnection();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Dim currentYear As Integer = DateTime.Today.Year
        ddl_Year.Items.Clear()
        ddl_Year.Items.Add("Select")
        For i As Integer = 2 To 0
            Dim fy As Integer = currentYear - i
            Dim fy1 As Integer = fy + 1
            If DateTime.Now.Date > Convert.ToDateTime(fy & "-03-31").Date Then
                ddl_Year.Items.Add(fy.ToString() & "-" & fy1.ToString())
            End If
        Next

        Dim currentDate As DateTime = DateTime.Now
        Dim items As List(Of ListItem) = New List(Of ListItem)()
        items.Add(New ListItem With {
            .Text = currentDate.AddMonths(-1).Month.ToString(),
            .Value = currentDate.AddMonths(-1).Month.ToString()
        })
        items.Add(New ListItem With {
            .Text = currentDate.Month.ToString(),
            .Value = currentDate.Month.ToString()
        })
        ddl_Month.DataSource = items
        ddl_Month.DataTextField = "Text"
        ddl_Month.DataValueField = "Text"
        ddl_Month.DataBind()
    End If
End Sub

Private _sqlCommand As SqlCommand
Private _sqlDataAdapter As SqlDataAdapter
Private _dtSet As DataSet

Public Sub CreateConnection()
    Dim strConnectionString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim _sqlConnection As SqlConnection = New SqlConnection(strConnectionString)
    _sqlCommand = New SqlCommand()
    _sqlCommand.Connection = _sqlConnection
End Sub

Public Sub OpenConnection()
    _sqlCommand.Connection.Open()
End Sub

Public Sub CloseConnection()
    _sqlCommand.Connection.Close()
End Sub

Public Sub DisposeConnection()
    _sqlCommand.Connection.Dispose()
End Sub

Public Sub connection()
    CreateConnection()
    OpenConnection()
End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
    connection()
    _sqlCommand.CommandText = "count"
    _sqlCommand.CommandType = CommandType.StoredProcedure
    _sqlCommand.Parameters.AddWithValue("@Month", ddl_Month.SelectedValue)
    If ddl_Year.SelectedIndex > 0 Then
        _sqlCommand.Parameters.AddWithValue("@Year", ddl_Year.SelectedValue)
    Else
        _sqlCommand.Parameters.AddWithValue("@Year", DBNull.Value)
    End If    
    Dim sda As SqlDataAdapter = New SqlDataAdapter(_sqlCommand)
    Dim dt As DataTable = New DataTable()
    sda.Fill(dt)

    If dt.Rows.Count > 0 Then
        GridView1.Visible = True
        GridView1.DataSource = dt
        GridView1.DataBind()
    Else
        GridView1.Visible = False
        Label1.Visible = True
        Label1.Text = "The Records for Month " & ddl_Month.SelectedValue & " is not Available."
    End If

    CloseConnection()
End Sub