Hi yogeshc,
Refer the below sample.
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
</head>
<body>
<form id="form1" runat="server">
<div align="center">
<table>
<tr>
<td>
<asp:DropDownList ID="ddlMonth" runat="server" CssClass="form-control">
<asp:ListItem Text="Select" />
<asp:ListItem Text="August" Value="8" Selected="True" />
</asp:DropDownList>
</td>
<td>
<asp:DropDownList ID="ddlYear" runat="server" CssClass="form-control">
<asp:ListItem Text="Select" />
<asp:ListItem Text="2017" Value="2017" Selected="True" />
</asp:DropDownList>
</td>
<td>
<asp:Button ID="Button1" Text="Get" runat="server" OnClick="Get" CssClass="btn btn-default" />
</td>
</tr>
</table>
</div>
<br />
<div>
<asp:GridView ID="gvStaff" runat="server" EmptyDataText="No Record found" CssClass="table table-striped table-hover"
BorderStyle="None" GridLines="Vertical" AutoGenerateColumns="true">
</asp:GridView>
<asp:Label ID="lblerror" runat="server" />
</div>
</form>
</body>
</html>
VB.Net
Protected Sub [Get](sender As Object, e As EventArgs)
bindinboxgridview()
End Sub
Private Sub bindinboxgridview()
Dim amonth As Integer = Convert.ToInt32(ddlMonth.SelectedValue)
Dim ayear As Integer = Convert.ToInt32(ddlYear.SelectedItem.Text)
Dim constring As String = ConfigurationManager.ConnectionStrings("EIMSConnectionString").ConnectionString
Dim con As New SqlConnection(constring)
Try
con.Open()
Dim sqlComm As New SqlCommand("sp_show_staff_att", con)
sqlComm.Parameters.AddWithValue("@attmonth", amonth)
sqlComm.Parameters.AddWithValue("@attyear", ayear)
sqlComm.Parameters.AddWithValue("@result", SqlDbType.Int);
sqlComm.CommandType = CommandType.StoredProcedure
Dim ds As New DataSet("TimeRanges")
Dim da As New SqlDataAdapter()
da.SelectCommand = sqlComm
da.Fill(ds)
con.Close()
If ds.Tables(0).Rows.Count > 0 Then
For i As Integer = 1 To DateTime.DaysInMonth(ayear, amonth)
Dim columnExist As Boolean = False
For j As Integer = 1 To ds.Tables(0).Columns.Count - 1
If i.ToString() = ds.Tables(0).Columns(j).ColumnName Then
columnExist = True
End If
Next
If Not columnExist Then
Dim col As New DataColumn() With {.ColumnName = i.ToString(), .DataType = GetType(String), .DefaultValue = "P/A"}
ds.Tables(0).Columns.Add(col)
End If
Next
gvStaff.DataSource = ds.Tables(0)
gvStaff.DataBind()
Else
lblerror.ForeColor = System.Drawing.Color.Red
lblerror.Text = "Record Not Found"
End If
Catch ex As Exception
lblerror.ForeColor = System.Drawing.Color.Red
lblerror.Text = ex.Message
Finally
con.Close()
End Try
End Sub
C#
protected void Get(object sender, EventArgs e)
{
bindinboxgridview();
}
private void bindinboxgridview()
{
int amonth = Convert.ToInt32(ddlMonth.SelectedValue);
int ayear = Convert.ToInt32(ddlYear.SelectedItem.Text);
string constring = ConfigurationManager.ConnectionStrings["EIMSConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
try
{
con.Open();
SqlCommand sqlComm = new SqlCommand("sp_show_staff_att", con);
sqlComm.Parameters.AddWithValue("@attmonth", amonth);
sqlComm.Parameters.AddWithValue("@attyear", ayear);
sqlComm.Parameters.AddWithValue("@result", SqlDbType.Int);
sqlComm.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet("TimeRanges");
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlComm;
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 1; i < DateTime.DaysInMonth(ayear, amonth) + 1; i++)
{
bool columnExist = false;
for (int j = 1; j < ds.Tables[0].Columns.Count; j++)
{
if (i.ToString() == ds.Tables[0].Columns[j].ColumnName)
{
columnExist = true;
}
}
if (!columnExist)
{
DataColumn col = new DataColumn { ColumnName = i.ToString(), DataType = typeof(string), DefaultValue = "P/A" };
ds.Tables[0].Columns.Add(col);
}
}
gvStaff.DataSource = ds.Tables[0];
gvStaff.DataBind();
}
else
{
lblerror.ForeColor = System.Drawing.Color.Red;
lblerror.Text = "Record Not Found";
}
}
catch (Exception ex)
{
lblerror.ForeColor = System.Drawing.Color.Red;
lblerror.Text = ex.Message;
}
finally
{
con.Close();
}
}
Procedure Output
| name |
1 |
2 |
3 |
4 |
5 |
7 |
| yogesh kumar |
P |
P |
P |
P |
P |
P |
| Sunit Kumar |
P |
A |
P |
P |
P |
A |
| Ravi Datt |
P |
P |
P |
P |
P |
P |
| Ram Narayan |
P |
P |
P |
P |
P |
P |
| pawan Kumar |
P |
P |
P |
P |
P |
P |
| Krishna Singh |
P |
P |
A |
P |
P |
P |
| Aakash Singh |
P |
P |
P |
A |
P |
P |
| aa fdfds |
P |
P |
P |
P |
P |
P |
Page Output
| name | 1 | 2 | 3 | 4 | 5 | 7 | 6 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 |
|---|
| yogesh kumar |
P |
P |
P |
P |
P |
P |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
| Sunit Kumar |
P |
A |
P |
P |
P |
A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
| Ravi Datt |
P |
P |
P |
P |
P |
P |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
| Ram Narayan |
P |
P |
P |
P |
P |
P |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
| pawan Kumar |
P |
P |
P |
P |
P |
P |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
| Krishna Singh |
P |
P |
A |
P |
P |
P |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
| Aakash Singh |
P |
P |
P |
A |
P |
P |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
| aa fdfds |
P |
P |
P |
P |
P |
P |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
P/A |
