Thanks. I'll give it a shot. _____
Gary L. Alford Manufacturing Operations Project Specialist Bell Helicopter XWorx Phone: (817) 280-6233 Fax: (817) 278-6233 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> I have not failed. I've found 10,000 ways that won't work. Thomas A. Edison _____ -----Original Message----- From: Schreck, Tom [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 05, 2005 8:23 AM To: [email protected] Subject: RE: Random Number Generator... Sure. It's a good question. My explanation assumes you are working with MS SQL Server. An identity field is a setting you can place on an int data type field where SQL Server will automatically generate a number which is the next available number in a sequence. ORACLE has a similar concept but it's implemented differently. In ORACLE you have to create a sequence table. I'm not sure how you get the value from the sequence table to use in your insert statement. Anyways, the concept is the same. A GUID stands for Global Unique IDentifier. It's a string that is guaranteed to be unique because the algorithm used to generate the GUID uses different variables like your MAC address and the date and time to construct the string. In SQL server you would use the uniqueidentifier data type with a NewID() function as the default value of the field. CF has a createUUID() method that produces a GUID. However, the CF GUID is different from a SQL Server generated GUID. You can do the same in ORACLE. You can convert a CF GUID to a SQL Server GUID by doing the following: Insert(createUUID(),'-',23) I think this is the correct syntax. Anyways, I hope this helps. Thanks Tom Schreck 972-361-9943 -----Original Message----- From: Alford, Gary L [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 05, 2005 7:59 AM To: '[email protected]' Subject: RE: Random Number Generator... Forgive me for being dumb, but I don't think I understand the concept of an Identity field or GUID. Can you please explain? _____ Gary L. Alford Manufacturing Operations Project Specialist Bell Helicopter XWorx Phone: (817) 280-6233 Fax: (817) 278-6233 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> I have not failed. I've found 10,000 ways that won't work. Thomas A. Edison _____ -----Original Message----- From: Schreck, Tom [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 05, 2005 7:55 AM To: [email protected] Subject: RE: Random Number Generator... OH, I see. You need to check to make sure the companyID is unique before the insertion. Is it possible to make the companyID either an Identity field where the database assigns the next sequential number as the companyID? Or, perhaps use a GUID as the companyID? If neither of those scenarios are possible and you have to use the companyName and a random number as the uniqueID, then I would suggest doing the following: 1. create the companyID as you are currently (companyName+randomNumber) 2. create a loop that fires say 10 times 3. within loop query the company table for any matches of your companyID 4. if the first query finds no matches, then you know your companyID is good and simply <cfbreak> out of the loop and proceed with the insertion. 5. if the query finds a matching companyID, meaning that companyID has already been used, then reset the companyID to a new value then proceed with the loop Also, you may want to consider increasing the randRange to a number higher than 999. This solution is kludge, but I think it will get you past your problem. I highly recommend using an Identity field or a GUID. It's cleaner and you will have fewer issues later (especially if you need to use companyID as a foreign key) Thanks Tom Schreck 972-361-9943 -----Original Message----- From: Alford, Gary L [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 05, 2005 7:23 AM To: '[email protected]' Subject: RE: Random Number Generator... I have already got a unique constraint on the data table. But, when inserting a new record, if a duplicate exists, wouldn't the system simply error out due to the duplicate and not rerun the ID statement to recreate the ID? _____ Gary L. Alford Manufacturing Operations Project Specialist Bell Helicopter XWorx Phone: (817) 280-6233 Fax: (817) 278-6233 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> I have not failed. I've found 10,000 ways that won't work. Thomas A. Edison _____ -----Original Message----- From: Schreck, Tom [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 05, 2005 7:17 AM To: [email protected] Subject: RE: Random Number Generator... Why not place a unique constraint on your companyID field in your database? Do you need to randomly select a company from your company table? If so, I have a cool way of doing that via a SQL Select statement. Thanks Tom Schreck 972-361-9943 -----Original Message----- From: Alford, Gary L [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 05, 2005 7:10 AM To: DFW ColdFusion Users Group (E-mail) Subject: Random Number Generator... I am currently running a random number generator and assigning it to a variable as follows: <cfset CompanyId = #LCase(Left(form.CompanyName,2))# & #RandRange(100, 999)#> The problem is that there will always be the possibility of duplication. I would like to run a <cfquery... to extract the Company IDs from the table in order to double check for duplication. If a duplication is found, how can I cause the random number generator to run again and recheck for duplication without reloading the entire page? _____ Gary L. Alford Manufacturing Operations Project Specialist Bell Helicopter XWorx Phone: (817) 280-6233 Fax: (817) 278-6233 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> I have not failed. I've found 10,000 ways that won't work. Thomas A. Edison _____ ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm
