Results 1 to 9 of 9

Thread: Reset invoice number for every new year vb.net

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2013
    Posts
    26

    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

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    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!

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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"

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    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!

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jun 2013
    Posts
    26

    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.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jun 2013
    Posts
    26

    Re: Reset invoice number for every new year vb.net

    Quote Originally Posted by si_the_geek View Post
    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.

  8. #8
    Addicted Member
    Join Date
    Jul 2017
    Location
    Exeter, UK
    Posts
    180

    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.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jun 2013
    Posts
    26

    Re: Reset invoice number for every new year vb.net

    Quote Originally Posted by bmwpete View Post
    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
  •  



Click Here to Expand Forum to Full Width