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!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|||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|||Create a trigger what will generate primary key for your table (you can use autoincremet field or calculate new key).|||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

How do I enumerate the database?

I'm adapting C++ objects to ADO and ADOX for unmanaged targets, and ADOX doesn't appear to be able to fully enumerate the database.

So, I want to know who I can do that in SQL - something SQL92 can handle, so that MySQL, MSSQL2000, Interbase and others will be compatible with the approach.

From ADOX, I can get the entire list of catalogs (databases), then the tables within them, and all fields within the table. What I can't get are the columns within the Indexes or Keys. If I can just figure out how to get that information from SQL, I'd be set.

ADOX has the collections that supposedly provides an enumeration of the "Fields" within the Index, or "Fields" within the key, but attempting to get the "Count" or an "Item" from that collection causes an exception for MySQL and MSDE (MS SQL 2000).

Thanks.SQL Server isn't "object oriented" so you can't enumerate it like classes or collections. Instead, the information is stored in system table in the databases and in the master database. You can get information on fields and indexes from the syscolumns and sysindexes tables. You will have to know (or be prepared to learn) a lot about SQL Server's database engine if you want to do this.

blindman