C# How to reset the Auto Increment ID every 1st date of the Month

Question

I'm having hard time trying to figure out how can i do this. What I'm trying to do is..

For example:
DATE: Oct 1, 2018 to Oct 31, 2018, have 2 records as you can see on the image provided. You can see the id starts at 001 so on and so for. Next month is Nov.. the id should restart/back to 001 not 003.

PS: OrderID is VARCHAR Datatype in my Database

Please note that every new month there is new record displaying so the past record is not seen here

I hope someone would be able to help me

 public partial class SIMSSupplier : UserControl
{
    ADDPOrders order;     
    public SIMSSupplier()
    {
        InitializeComponent();

    }
    public string ORDERID = "000";
    private void ADDOrder_Click(object sender, EventArgs e)
    {        
        order = new ADDPOrders(this);
        POrderID._OrderID(order.lbl_orderID);
        order.ShowDialog(); 
    }
    private void DateOrder_ValueChanged(object sender, EventArgs e)
    {
        DateBetween._DateOrder(DateOrder, DateOrder, PurchaseOrder);
    }
    private void DateOrder2_ValueChanged(object sender, EventArgs e)
    {
        DateBetween._DateOrder(DateOrder, DateOrder2, PurchaseOrder);
    }      
}


public static class DateBetween
{
    public static void _DateOrder(DateTimePicker DateOrder, DateTimePicker DateOrder2, DataGridView PurchaseOrder)
    {
        using (var con = SQLConnection.GetConnection())
        {
            using (var select = new SqlCommand("Select * from Purchase_Order where Date between @date1 and @date2", con))
            {
                select.Parameters.Add("@date1", SqlDbType.Date).Value = DateOrder.Value;
                select.Parameters.Add("@date2", SqlDbType.Date).Value = DateOrder2.Value;
                using (var sd = new SqlDataAdapter(select))
                {
                    var dt = new DataTable();
                    sd.Fill(dt);
                    PurchaseOrder.DataSource = dt;
                }
            }
        }
    }
}

To update the existing value you can : Select all (real ID, OrderID, date ). Group by month. Select(real ID, OrderID = index+1). then update your data. — Drag and Drop, Oct 02 '18 at 09:14
I mean that you must have a real Id somewhere in the table, an Identity, Unique that do not get reset. How would you edit a Purchase_Order? by finding the row that has the same id and date? — Drag and Drop, Oct 02 '18 at 09:20
Unfortunately i don't have real id, I'm using the OrderID to edit my data. — , Oct 02 '18 at 09:26
If you are planning on resetting it each month, you cant just use OrderId. You will need to use composite then, by creating comparisons with at least date and orderid. Otherwise you would end up modifying / removing each entry that has the same orderid. — Cubicle, Oct 02 '18 at 09:29
Get a real Id "IDENTITY(1,1)". You wan't to modify `OrderID`. How long before you need to modify `Date` column? read: [Do database tables need to have IDs?](https://softwareengineering.stackexchange.com/questions/174860/do-database-tables-need-to-have-ids) — Drag and Drop, Oct 02 '18 at 09:38
Yes. You will need an actual id field and auto incremented id will work. Your OrderId is not an id on database level, which you need to solve in another matter. SQL Server has support for sequences, but it has no built-in mechanism for cycling it whenever month changes. — Cubicle, Oct 02 '18 at 09:39
Or [Should each and every table have a primary key?](https://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key) and [Should every table have a single-field surrogate/artificial primary key?](https://dba.stackexchange.com/questions/6108/should-every-table-have-a-single-field-surrogate-artificial-primary-key) — Drag and Drop, Oct 02 '18 at 09:40

score 1 · Accepted Answer · answered Oct 02 '18 at 09:07

1

If you have the possibility to change the field type to int is much easier to do this from SQL directly. You can auto increment and change the change the seed value each month with a script like the one below (note it also adds the month number in the beginning, if that's useful for you):

IF (DATEPART(DAY, GETDATE()) = 1)
BEGIN
    DECLARE @reseedValue INT
    SET @reseedValue = CAST(CONCAT(CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR(4)), '000') AS INT)
    DBCC CHECKIDENT ('[table_name]', RESEED, @reseedValue);
END

answered Oct 02 '18 at 09:07

Sorin87bv

156
8

Can you elaborate more ?, the code you provide is from sql, you mean here is i need to write that code every single time ? – Oct 02 '18 at 09:11
1

If you added that code to each insert (or select for that matter), it would reseed it on every query for the duration of the 1st. Not exactly optimal. – Cubicle Oct 02 '18 at 09:18
You may have a stored procedure that is called every day in an overnight job to keep it optimal (you can run the job once per day at 00:00). Anonymous - this is entirely an SQL solution, you need access to the SQL instance (to change the field type, create the stored procedure and the job). You may go for a .NET solution too, but you'd need additional steps (example: always calculating next value based on the last one - when the month doesn't change) so I would go for SQL here. – Sorin87bv Oct 02 '18 at 11:35

C# How to reset the Auto Increment ID every 1st date of the Month

1 Answers1