Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    34

    Unanswered: How do I generate a Unique ID?

    Hi,

    I have 5 tables that need ID columns. These ID columns are the primary keys for these tables. They appear in the tables like so:

    Clients table

    CLT00001
    CLT00002
    CLT00003

    Volunteers table

    VOL00001
    VOL00002
    etc...

    These ID's are given to the Clients/Volunteers so they need to be more than just a number (I want to use the 3 char prefix), but I want them to auto-increment. I am not concerned with gaps if a row is deleted.

    At the moment I have a user defined datatype in MSSQL, and I am incrementing and adding the prefix in VB before inserting into the database.

    This works fine, except I dont know how to retrieve the last ID number from the DB so I can increment it when I add a new record.

    I was using a VB function that did this: SELECT MAX(client_id) FROM clients, then strip everything of the front but the number, increment + 1, add the prefix and leading zeros back on again, and return the new ID. This worked until CLT00011, then it returned CLT00002 again.

    Is there a better way to do what I am trying to do? Please help!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One solution:

    Separate your ID into two fields; the prefix and the numeric portions. Autoincrement the numeric portion, but pad it with zeros and concatenate it with the prefix for display. You could also create a calculated field in your table that combined the two components.

    Either way, you can set the combination of the two columns as the primary key, or just as a unique index.

    blindman

  3. #3
    Join Date
    Sep 2003
    Posts
    34
    Does that mean I would have a Prefix field with 'VOL' in every row, and my IDENTITY field in another. Sounds good, but does it kmatter that I have redundant info. i.e. the prefix field?

    How do I join the two with a calculated field, because I still need to have a 8 char long ID, 3 chars for the prefix, then the ID num padded with zeros between the two, e.g.

    VOL00001
    VOL00002

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Create a trigger what will generate primary key for your table (you can use autoincremet field or calculate new key).

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If every record has the same prefix then is it not necessary to dedicate a column to it.

    Set up your autoincrement row, and then create your calculated field with this formula:

    'VOL' + right('00000' + cast([AutoIncColumn] as varchar(5)))

    blindman

Posting Permissions

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