You must Sign In to post a response.
  • Category: SQL Server

    How to auto update the row based on month or date?

    hi everyone,
    how to auto update/expire the row based on date or month.
    Thanks and Regards
  • #766712
    Hi,

    could you please elaborate your requirement clearly, so that it help us to give valid response from our end.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766717
    Thank you responding,
    i want update the row in sql based on date. means i save duration 6 months in this time in table has "status=1" after 6 months automatically i want update the status. that is "Status=0"

    Thanks and Regards

  • #766721
    Hi Sadiq,

    For that you have to wrote one console and run the Timer Job for the same, and give the duration to 6 months for the same, that was helpful to you.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766722
    you have any example sir please send me.
    Thanks and Regards

  • #766725
    Hi Sadiq,

    Prepare one console application, and inside that console prepare code for updating the status to "0", and after preparing the console put the same in one physical folder and while execute the Task Scheduler pass the Physical path and set the timer for executing the job.

    http://www.sevenforums.com/tutorials/12444-task-scheduler-create-new-task.html

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766735
    Hi,
    For that query, you have a write an stored Procedure and have run through the Job Schedule. In that, job you have to set the date and time.

    Regards,
    Karunanidhi.K

  • #766740
    Hi
    if you need auto update means you need use jobs in sql server based on records daily check and update or monthly based you can update no need gridview. if you manual update you can follow this code which one i mention the code.
    follow this steps

    Client Side


    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="id"
    OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">
    <Columns>
    <asp:BoundField DataField="id" HeaderText="S.No." />
    <asp:BoundField DataField="name" HeaderText="Name" />
    <asp:CommandField ShowEditButton="true" />
    <asp:CommandField ShowDeleteButton="true" />
    </Columns>
    </asp:GridView>



    Server Side


    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
    int userid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
    GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
    Label lblID = (Label)row.FindControl("lblID");
    TextBox textName = (TextBox)row.Cells[0].Controls[0];
    GridView1.EditIndex = -1;
    conn.Open();
    SqlCommand cmd = new SqlCommand("update detail set name='"+textName.Text+"' where id='"+userid+"'",conn);
    cmd.ExecuteNonQuery();
    conn.Close();
    FillGrid();
    }


    protected void FillGrid()
    {
    conn.Open();
    SqlCommand cmd = new SqlCommand("Select * from detail", conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    conn.Close();
    if (ds.Tables[0].Rows.Count > 0)
    {
    GridView1.DataSource = ds;
    GridView1.DataBind();
    }
    else
    {
    ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
    GridView1.DataSource = ds;
    GridView1.DataBind();
    int columncount = GridView1.Rows[0].Cells.Count;
    GridView1.Rows[0].Cells.Clear();
    GridView1.Rows[0].Cells.Add(new TableCell());
    GridView1.Rows[0].Cells[0].ColumnSpan = columncount;
    GridView1.Rows[0].Cells[0].Text = "No Records Found";
    }

    }


    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
    GridView1.EditIndex = e.NewEditIndex;
    FillGrid();
    }


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766754
    Easisst option is create an SSIS job in SQL and schedule to run everyday to update the table according to the value you require.

    https://msdn.microsoft.com/en-us/library/ms169917.aspx


    Regards,
    Asheej T K
    Microsoft MVP[ASP.NET/IIS]
    DotNetSpider MVM

  • #766782
    you need to set the JOB in SQL integration services, and schedule it from windows scheduler.
    follow below steps to schedule job
    1.In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
    2.Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
    3.Select the Schedules page, and then click New.
    4.In the Name box, type a name for the new schedule.
    5.Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.
    6. Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
    for more details switch to below link
    http://www.c-sharpcorner.com/UploadFile/raj1979/create-and-schedule-a-job-in-sql-server-2008/

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #766791
    Write a trigger for update as it will automatically updates when it meets the condition.
    SRI RAMA PHANI BHUSHAN KAMBHAMPATI


Sign In to post your comments