• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

How can I create a unique but sequential number that is always six digits long?

I'm starting to populate my database and I need a way to create a unique identifying number/letter combination for each of my records.  The format needs to be first a letter which is always the same and then a six digit number (ex. Z000001, Z000002 etc.)  My database is set up with an ID field used as the Identity but for display purposes I need this type of name.

I'm thinking that I will only need to create this name at the point of the Insert statement so I need to somehow create this on the fly.  It would be nice to have the @@Identity number be used in the name.  My problem is how do I get it to always use six digits for the number?

Will I first have to do an insert statement and then query for the MAX(ID) as newID and then create a name with <cfset newName = "Z#newID#">  But how can I make sure that the newName will have the correct amount of numbers (six digits)?  Any suggestions?  Thanks.
0
Ike23
Asked:
Ike23
  • 4
  • 2
1 Solution
 
mrichmonCommented:
Easy..

It is just a display issue.
<cfset newName = "Z" & NumberFormat(newID, "000000")>
0
 
mrichmonCommented:
So you can still use @@Identity too... and treat the order number in the database as 1 or 34 or 503, but when it displays it will be:

Z000001
Z000034
Z000503

One catch is that it is not RESTRICTING to 6 digits - it is doing a min of 6 digits so if your ID became:
10000000 you would get:
Z10000000 (i.e. after Z999999) which is 7 digits after the Z
0
 
mrichmonCommented:
If you want to treat Z10000000 as Z000000 (i.e. start looping in display even though the ID is longer you can do mod arithmetic which is also easy...
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
Ike23Author Commented:
Cool that works well.  Here's the code I am planning on using.  Does it look right as far as getting the correct identity or should I be using the @@IDENTITY attribute somehow?  How does the second query know what the @@IDENTITY attribute value is?  It works but should I be using #GetID.NewID# variable instead?  Thanks for your help!

<cftransaction>
      
      <cfquery datasource="#Datasource#" name="Add">
      INSERT INTO table1 (ProjectID, )
      VALUES (#form.project_id#)
      </cfquery>
      <cftransaction action="commit" />

              <cfquery datasource="#Datasource#" name="Get">
            Select MAX(ID) as NewID
            From table1
              </cfquery>

      <cfset newName = "B" & NumberFormat(#GetID.NewID#, "000000")>

            <cfquery datasource="#Datasource#" name="update">
                  Update table1
                  Set name = '#TRIM(newName)#'
                  Where ID = @@IDENTITY
            </cfquery>
            
  </cftransaction>
0
 
mrichmonCommented:
You can't use @@Identity in that case.  @@Identity or the other similar functions work only in the same SQL transaction (not cf transaction).

So in the above you would use GetID.NewID, but if you do it that way you also need to use locks or move the commiting of the transaction to after the selection o fhte max ID.

0
 
Ike23Author Commented:
I'm using the <cftransaction action="commit" /> to commit the first INSERT into the database so I can query for the MAX(ID) and make sure it is the newest ID.  Since it is within another cftransaction tag doesn't that make sure it all is in the same transaction?  Thanks.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now