Reset invoice number for every new year vb.net

Hello, How can i Modified this code to reset the invoice number every time when year change, For example in year 2021 is Number last invoice number is 745 so when 2022 start i want to reset in 1 not continue in 746.

My Actual code is :

Please help to fix this because i'm new in vb.net

Thank You.

Code:

---

    con.Open()
            cmd = New SqlCommand("SELECT TOP 1 Inv_ID FROM InvoiceInfo ORDER BY Inv_ID DESC", con)
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If rdr.HasRows Then
                rdr.Read()
                value = rdr.Item("Inv_ID")
            End If
            rdr.Close()
            ' Increase the ID by 1
            value += 1

---

Well...you will need to save the previous year somewhere so you can check if the new year occurred.

Assuming that you have a field in the InvoiceInfo table that indicates the year somehow, you can alter your Select statement to only return the values for that year, eg:

Code:

---

"SELECT TOP 1 Inv_ID FROM InvoiceInfo WHERE yearField = 2022 ORDER BY Inv_ID DESC"

---

..if the field is a DateTime, you can use the Year function to check just the year:

Code:

---

"SELECT TOP 1 Inv_ID FROM InvoiceInfo WHERE Year(dateField) = 2022 ORDER BY Inv_ID DESC"

---

We have tables set up like fee schedules, rates, etc. that we set up maintenance jobs for. Each year the users set up for the next year. A lot of times it is just copying the same data and updating the year. That way your code doesn't have to keep checking.

Question: What's the primary key on the table? Is it "Inv_ID"??? If it is, this isn't going to work. If you're intending to have an invoice number that resets, or can change over time, 1) it shouldn't be your primary key, and 2) you should have a dedicated "Invoice_Number" field for it separate from your key.

-tg

si_the_geek , Hi, your code work Perfect, I use this code

Code:

---

"SELECT TOP 1 Inv_ID FROM InvoiceInfo WHERE Year(dateField) = 2022 ORDER BY Inv_ID DESC"

---

And work perfect. THANK YOU SO MUCH.

the problem was solved.

Quote:

---

Originally Posted by si_the_geek

Assuming that you have a field in the InvoiceInfo table that indicates the year somehow, you can alter your Select statement to only return the values for that year, eg:

Code:

---

"SELECT TOP 1 Inv_ID FROM InvoiceInfo WHERE yearField = 2022 ORDER BY Inv_ID DESC"

---

..if the field is a DateTime, you can use the Year function to check just the year:

Code:

---

"SELECT TOP 1 Inv_ID FROM InvoiceInfo WHERE Year(dateField) = 2022 ORDER BY Inv_ID DESC"

---

---

si_the_geek , Hi, your code work Perfect, I use this code

Code:

Code:

---

"SELECT TOP 1 Inv_ID FROM InvoiceInfo WHERE Year(dateField) = 2022 ORDER BY Inv_ID DESC"

---

And work perfect. THANK YOU SO MUCH.

the problem was solved.

Further to what has already been said, Invoice numbers are unique identification numbers assigned to invoices. In the UK, there is no legal requirement for how they are formatted, however, it is required that invoice numbers follow a sequence and do not include any repeats or gaps.

Quote:

---

Originally Posted by bmwpete

Further to what has already been said, Invoice numbers are unique identification numbers assigned to invoices. In the UK, there is no legal requirement for how they are formatted, however, it is required that invoice numbers follow a sequence and do not include any repeats or gaps.

---

Yes it is unique number for example I use 001/2021, and in new year I use 001/2022 so in this way they are unique.