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

Last Reply one month ago By dharmendr

Posted one month 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>

 

You are viewing reply posted by: dharmendr one month ago.
Posted one month ago Modified on one month 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();
}