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:
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!
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.