Delete and display record from database in ASP.Net GridView using C#

Last Reply 26 days ago By dharmendr

Posted 26 days ago

I am trying to delete row from database automatically if "last date of apply  date " is equal to "current date ".

I tried but it is not binding my gridview with select query. I want to bind my gridview after delete query.

    public partial class Applied_job : System.Web.UI.Page
    {
        SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["Q_SS_regnConnectionString1"].ToString());
        SqlCommand cmd = new SqlCommand();
        protected void Page_Load(object sender, EventArgs e)
        {

            checkExpired();
            FillGrid();
        }
        void checkExpired()
        {
            if (!IsPostBack)
            {
                string constr = ConfigurationManager.ConnectionStrings["Q_SS_regnConnectionString1"].ConnectionString;
                SqlConnection con = new SqlConnection(constr);
                SqlCommand cmd = new SqlCommand("delete from JS_Resume where email='" + Session["mail"].ToString() + "' and Last_Apply_date=CURRENT_TIMESTAMP and Status='Rejected'", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                GridView2.DataSource = dt;
                GridView2.DataBind();
            }
        }
        void FillGrid()
        {
            if (!IsPostBack)
            {
                string constr = ConfigurationManager.ConnectionStrings["Q_SS_regnConnectionString1"].ConnectionString;
                SqlConnection con = new SqlConnection(constr);
                SqlCommand cmd = new SqlCommand("select * from JS_Resume where email='" + Session["mail"].ToString() + "'", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                GridView2.DataSource = dt;
                GridView2.DataBind();
            }
        }

 

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    </br>
    </br>
    <div style="margin-left: 25%">
        <div class="row" style="width:260%">
            <div class="col-sm-4">
                <div class="shadow-lg p-3 mb-5 bg-white rounded panel panel-blue">
                    <div class="panel-heading" style="font-size: x-large; font-weight: bolder">
                        <center><b>Applied Jobs</b></center>
                    </div>
                    <div class="panel-body">
                        <center>
                        <asp:GridView ID="GridView2" runat="server" CssClass="mydatagrid" PagerStyle-CssClass="pager"
                            HeaderStyle-CssClass="header" EmptyDataText="No Records Found" RowStyle-CssClass="rows" AutoGenerateColumns="False"  OnSelectedIndexChanged="GridView2_SelectedIndexChanged">
                            <Columns>
                                <asp:BoundField DataField="Id" HeaderText="User ID" SortExpression="Id" ReadOnly="True" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" InsertVisible="False" Visible="false" />
                                <asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="resumefile" HeaderText="Resume File" SortExpression="resumefile" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" Visible="false" />
                                <asp:BoundField DataField="HireAnswer" HeaderText="Hire Answer" SortExpression="HireAnswer" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="Skills" HeaderText="Skills" SortExpression="Skills" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="JobPost" HeaderText="Job Post" SortExpression="JobPost" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="CompanyName" HeaderText="Company Name" SortExpression="CompanyName" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="CompanyEmail" HeaderText="Company Email" SortExpression="CompanyEmail" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="city" HeaderText="Company City" SortExpression="city" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="Last_Apply_date" HeaderText="Last Date of Apply" SortExpression="Last_Apply_date" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="JobId" HeaderText="Job Id" SortExpression="JobId" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" Visible="false" />
                                <asp:TemplateField HeaderText="Resume">
                                    <ItemTemplate>
                                        <asp:LinkButton ID="lnkDownload" Text="Download" CommandArgument='<%# Eval("Id") %>' runat="server" OnClick="lnkDownload_Click" CssClass="btn btn-success" ></asp:LinkButton>
                                    </ItemTemplate>
                                </asp:TemplateField>                               
                            </Columns>
                        </asp:GridView>
                       </center>
                    </div>
                </div>
            </div>
        </div>
    </div>
</asp:Content>

 

Posted 26 days ago Modified on 26 days ago

Hi itsme,

For deleting record ExecuteNonQuery method use.

After deleting the record you have to bind the gridview.

Check the updated code.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        checkExpired();
        FillGrid();
    }
}
void checkExpired()
{
    string constr = ConfigurationManager.ConnectionStrings["Q_SS_regnConnectionString1"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("delete from JS_Resume where email='" + Session["mail"].ToString() + "' and CONVERT(DATE, Last_Apply_date) = CONVERT(DATE, CURRENT_TIMESTAMP) and Status='Rejected'", con);
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}
void FillGrid()
{
    string constr = ConfigurationManager.ConnectionStrings["Q_SS_regnConnectionString1"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("select * from JS_Resume where email='" + Session["mail"].ToString() + "'", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    GridView2.DataSource = dt;
    GridView2.DataBind();
}