Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: Sequential Numbering

    Hi All

    New to this as in VBScripting, I have been asked to create a barcode that changes its format depending on Customer Number.
    I am using a labeling software "Bartender" and gathering all information from our SQL Server, I have nearly cracked this, first time for everything, one of the formats requires that after every print it increments by one, so after each print the code is never the same.
    This is limited to a count to 999, question is how do you do this in VB, how do you serialise or sequential numbering after every print.

    This would help me no end and regain some lost hair

    Cheers

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Do you need to keep track of this sequence number from run to run (if the program is halted, or the PC rebooted between runs?) If yes, you will need to save the sequence number.

    This could be accomplished a great many ways. Store the value to a text or binary file. Store it in the registry, or in an ini or xml file.

    However, since you're already using a database, why not add a new table, with a single field, containing a integer field with a default identity value? To retrieve a sequence value, insert a new record into the table, then

    Code:
    select Scope_Identity % 1000 as Seq from tablename
    The % is the modulus operator in SQL Server. When the app closes, delete all records from the table. Or not. It's not critical that it be done, but the records will eat up space...

    Other databases, like Oracle or PostgreSQL, which support standalone sequence objects, can be configured to automatically wrap around when the max value has been generated. This can eliminate the modulus calc in the SQL statement.
    Last edited by loquin; 07-28-07 at 03:35.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jul 2007
    Posts
    2
    Cheers Loquin
    Yes we have a SQL server, but I am a novice to using SQL 2005 and creating a table
    "why not add a new table, with a single field, containing an integer field with a default identity value?"
    How would this be done...??
    The sequence numbers would only need to be kept up to 999 then it could start over again.....
    Any help would be a bonus or some direction to the required result....

    Thank you

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Download SQL Books online. If you'll be working with SQL Server, it's really a necessity. If nothing else, use the online version.

    The basic syntax for creating your table is

    CREATE TABLE YourTableNAame Column_Name Integer IDENTITY


    If you use the moduluds operator, you don't need to reset the identity. Resetting the identity every 1000 calls is more of a pain than just using the modulus operator on every call.

    N % 1000 returns the remainder of N/1000...

    As N increases past 1000, N % 1000 returns 1, 2, 3, ... 998, 999, 0, 1, 2, ...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •