-
Mar 15th, 2022, 04:57 AM
#1
Thread Starter
Junior Member
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
-
Mar 15th, 2022, 08:00 AM
#2
Re: Reset invoice number for every new year vb.net
Well...you will need to save the previous year somewhere so you can check if the new year occurred.
Please remember next time...elections matter!
-
Mar 15th, 2022, 08:27 AM
#3
Re: Reset invoice number for every new year vb.net
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"
-
Mar 15th, 2022, 08:40 AM
#4
Re: Reset invoice number for every new year vb.net
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.
Please remember next time...elections matter!
-
Mar 15th, 2022, 09:33 AM
#5
Re: Reset invoice number for every new year vb.net
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
-
Mar 15th, 2022, 11:52 AM
#6
Thread Starter
Junior Member
Re: Reset invoice number for every new year vb.net
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.
-
Mar 15th, 2022, 11:53 AM
#7
Thread Starter
Junior Member
Re: Reset invoice number for every new year vb.net
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.
-
Mar 15th, 2022, 11:54 AM
#8
Addicted Member
Re: Reset invoice number for every new year vb.net
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.
-
Mar 16th, 2022, 05:01 AM
#9
Thread Starter
Junior Member
Re: Reset invoice number for every new year vb.net
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|