Dynamically display selected month missing Days in GridView using C# and VB.Net in ASP.Net

yogeshc

SOLVED CLOSED ACTIVE ANSWERED
User: yogeshc
Posted: on Sep 25, 2017 01:14 AM
Forum: Data Controls
Answer: 1
Views: 15918
Sample Code: Download

sir i have have table that hold attendance record of employees of every month

now i wants to show attendance in gridview but problem is that every month days are change for example march has 31 and apirl has 30 and feb has 28 or 29

i wants fetch record month basis using where clouse in sql now piont is on week off attendance not marked (so sql table not holds that day data )that means in august 2017 on date 6,13,20,27 was sunday

i wants to show as

name 1 2 3 4 5 7 8 9 10 11 12 14

so wants to add column as with header text as date and cell text as P/A

thank you

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 1

Reply
User: dharmendr
Replied: on Sep 25, 2017 01:15 AM
Report

Hi yogeshc,

Please provide html and code what you have implemented till now with database table structure.

yogeshc

0 2

Reply
User: yogeshc
Replied: on Sep 25, 2017 04:23 AM
Report

my aspx page code

<asp:GridView ID="gvStaff" runat="server" EmptyDataText ="No Record found" CssClass="table table-striped table-hover" BorderStyle="None" GridLines="Vertical" AutoGenerateColumns="false">
                                    <Columns>
              <asp:TemplateField HeaderText="Name">
                   <ItemTemplate>
                       <asp:Label ID="lblStName" runat="server" Text='<%#Eval("Name") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
               </Columns>
                 </asp:GridView>

code Behind

Private Sub bindinboxgridview()
        Dim amonth As Integer = CInt(ddlMonth.SelectedValue.ToString)
        Dim ayear As Integer = CInt(ddlYear.SelectedItem.Text.ToString)

        Dim constring As String = ConfigurationManager.ConnectionStrings("EIMSConnectionString").ConnectionString
        Dim con As SqlConnection = New SqlConnection(constring)
        Try
            con.Open()
            Dim sqlComm As SqlCommand = 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 DataSet = New DataSet("TimeRanges")
            Dim da As SqlDataAdapter = New SqlDataAdapter
            da.SelectCommand = sqlComm
            da.Fill(ds)
            con.Close()
            If ds.Tables(0).Rows.Count > 0 Then
                gvStaff.DataSource = ds.Tables(0)
                gvStaff.DataBind()
            Else
                lblerror.ForeColor = Drawing.Color.Red
                lblerror.Text = "Record Not Found"
            End If

        Catch ex As Exception
            lblerror.ForeColor = Drawing.Color.Red
            lblerror.Text = ex.Message.ToString
        Finally
            con.Close()
        End Try
    End Sub

my stored procedure is

ALTER proc [dbo].[sp_show_staff_att](@attmonth int ,@attyear int,@result int output)
as

BEGIN
SELECT *
INTO #Temp
FROM 
  (SELECT [Emp_Code] ,[Name],[Status],attDay 
  FROM Table_Attendance_Staff WHERE attMonth =@attmonth  and attYear=@attyear ) as x        
    
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),@ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(attDay) FROM (SELECT DISTINCT attDay 
FROM #Temp) AS [Days]
SET @DynamicPivotQuery = ';WITH CTE AS(SELECT [attDay],name,Status FROM #Temp )
SELECT name,'+@ColumnName+' FROM CTE
PIVOT (MAX(Status) FOR [attDay] IN('+@ColumnName+')) p 
ORDER BY name DESC'
EXEC(@DynamicPivotQuery)
drop table #Temp    
END

my table as

CREATE TABLE [dbo].[Table_Attendance_Staff](
	[Emp_Code] [varchar](30) NOT NULL,
	[Name] [varchar](60) NOT NULL,
	[Status] [char](1) NULL,
	[AttendanceDate] [varchar](50) NOT NULL,
	[attDay] [int] NULL,
	[attMonth] [int] NULL,
	[attYear] [int] NULL

dharmend...

0 3

Reply
User: dharmendr
Replied: on Sep 25, 2017 04:42 AM
Report

Provide some sample record of table Table_Attendance_Staff.

yogeshc

0 4

Reply
User: yogeshc
Replied: on Sep 25, 2017 05:03 AM
Report

here is the first week record on 6th august on sunday that's why no records 6th

Emp_Code	Name	Status	AttendanceDate	attDay	attMonth	attYear
Emp0001	Ram Narayan	P	8/1/2017	1	8	2017
Emp0002	Ravi Datt	P	8/1/2017	1	8	2017
Emp0001	Ram Narayan	P	8/2/2017	2	8	2017
Emp0002	Ravi Datt	P	8/2/2017	2	8	2017
Emp0001	Ram Narayan	A	8/3/2017	3	8	2017
Emp0002	Ravi Datt	A	8/3/2017	3	8	2017
Emp0001	Ram Narayan	P	8/4/2017	4	8	2017
Emp0002	Ravi Datt	P	8/4/2017	4	8	2017
Emp0001	Ram Narayan	P	8/5/2017	5	8	2017
Emp0002	Ravi Datt	P	8/5/2017	5	8	2017
Emp0001	Ram Narayan	P	8/7/2017	7	8	2017
Emp0002	Ravi Datt	P	8/7/2017	7	8	2017

dharmend...

0 5

Reply
ANSWER
User: dharmendr
Replied: on Sep 26, 2017 01:13 AM
Modified: on Sep 26, 2017 11:59 PM
Report

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

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Dynamically display selected month missing Days in GridView using C# and VB.Net in ASP.Net

Company

Explore

Follow us

Contact Us