Hi indradeo,
Check this example. Now please take its reference and correct your code.
HTML
Default
<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" AutoGenerateColumns="false" CssClass="auto-style11" Height="138px" Width="99.7%">
<Columns>
<asp:BoundField DataField="DEPARTMENT" HeaderText="DEPARTMENT" />
<asp:TemplateField HeaderText="Lag">
<ItemTemplate>
<asp:LinkButton ID="lnkLag" Text='<%# Eval("Lag") %>' runat="server" OnClick="OnLeadLag" CommandName="Lag" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Lead">
<ItemTemplate>
<asp:LinkButton ID="lnkLead" Text='<%# Eval("Lead") %>' runat="server" OnClick="OnLeadLag" CommandName="Lead" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
Details
<asp:GridView runat="server" ID="gvDetails"></asp:GridView>
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#
Default
protected void Page_Load(object sender, EventArgs e)
{
Session["Department"] = null;
Session["Lag"] = null;
Session["Lead"] = null;
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);
_sqlCommand.Parameters.AddWithValue("@Year", ddl_Year.SelectedValue);
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();
}
protected void OnLeadLag(object sender, EventArgs e)
{
GridViewRow row = (sender as LinkButton).NamingContainer as GridViewRow;
string department = row.Cells[0].Text.Trim();
string lag = (row.FindControl("lnkLag") as LinkButton).Text.Trim();
string lead = (row.FindControl("lnkLead") as LinkButton).Text.Trim();
Session["Department"] = department;
if ((sender as LinkButton).CommandName == "Lag")
{
Session["Lag"] = lag;
}
if ((sender as LinkButton).CommandName == "Lead")
{
Session["Lead"] = lead;
}
Response.Redirect("Details.aspx");
}
Details
protected void Page_Load(object sender, EventArgs e)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT * FROM Details WHERE DepartMent = @Department";
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@DepartMent", Session["DepartMent"].ToString());
if (Session["Lag"] != null)
{
query += " AND Lag = @Lag";
cmd.Parameters.AddWithValue("@Lag", Session["Lag"].ToString());
}
if (Session["Lead"] != null)
{
query += " AND Lead = @Lead";
cmd.Parameters.AddWithValue("@Lead", Session["Lead"].ToString());
}
cmd.CommandText = query;
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
}
}
Session["Department"] = null;
Session["Lag"] = null;
Session["Lead"] = null;
}
VB.Net
Default
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Session("Department") = Nothing
Session("Lag") = Nothing
Session("Lead") = Nothing
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)
_sqlCommand.Parameters.AddWithValue("@Year", ddl_Year.SelectedValue)
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
Protected Sub OnLeadLag(ByVal sender As Object, ByVal e As EventArgs)
Dim row As GridViewRow = TryCast((TryCast(sender, LinkButton)).NamingContainer, GridViewRow)
Dim department As String = row.Cells(0).Text.Trim()
Dim lag As String = (TryCast(row.FindControl("lnkLag"), LinkButton)).Text.Trim()
Dim lead As String = (TryCast(row.FindControl("lnkLead"), LinkButton)).Text.Trim()
Session("Department") = department
If TryCast(sender, LinkButton).CommandName = "Lag" Then
Session("Lag") = lag
End If
If TryCast(sender, LinkButton).CommandName = "Lead" Then
Session("Lead") = lead
End If
Response.Redirect("Details.aspx")
End Sub
Details
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "SELECT * FROM Details WHERE DepartMent = @Department"
Using con As SqlConnection = New SqlConnection(conString)
Dim cmd As SqlCommand = New SqlCommand()
cmd.Parameters.AddWithValue("@DepartMent", Session("DepartMent").ToString())
If Session("Lag") IsNot Nothing Then
query += " AND Lag = @Lag"
cmd.Parameters.AddWithValue("@Lag", Session("Lag").ToString())
End If
If Session("Lead") IsNot Nothing Then
query += " AND Lead = @Lead"
cmd.Parameters.AddWithValue("@Lead", Session("Lead").ToString())
End If
cmd.CommandText = query
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As DataTable = New DataTable()
sda.Fill(dt)
gvDetails.DataSource = dt
gvDetails.DataBind()
End Using
End Using
End Using
Session("Department") = Nothing
Session("Lag") = Nothing
Session("Lead") = Nothing
End Sub
