RE: CF Code to create AutoIncrement column MS SQL?
Hi Dawn, This is something which should be handled by the database. In a database where you have auto incrementing the primary keys it shouldn't be the applications responsibility to keep an eye on that, you should be doing simple INSERT statements and allowing the DB to do the work. In a SQL Server database, provided you're using SQL Server Management Studio to connect to the DB you can set the column to auto-increment by right clicking the table in the object explorer and choosing 'modify' or 'edit' then highlight the PK column and in the 'column properties' pane at the bottom expand the 'identity specification' section, you'll then be able to set the column to an identity and have it auto increment. Hope that helps, Rob -Original Message- From: Dawn Sekel [mailto:dse...@ciber.com] Sent: 23 April 2009 17:26 To: cf-talk Subject: CF Code to create AutoIncrement column MS SQL? Hi: I have a customer whose programmer has vanished and he is trying to get his application back on line. I'm an intermediate CF programmer and I've managed to get everything working again except for one problem. His database is hosted - and somehow, when he restored his application, the fields in his database lost their autonumbering capability. I tried creating the next number right before the insert by getting the max value of the field and then adding 1 to it, and that works for the most part, but occassionally, we are seeing the old Violation of PRIMARY KEY constraint 'PK_TblTestAnswers'. Cannot insert duplicate key in object 'dbo.TblTestAnswers' - and I can't figure out why unless to users are somehow hitting the same page at exactly the same time and clicking submit. So the only thing I can think of to fix the problem is to recreate the autoincrment key fields somehow. Does anyone have any Coldfusion code to insert a new autoincrement key field in to a MS SQL table and then remove the old one? Or is there a way to modify a key field to autoincrement? Thanks in advance for any advice. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321862 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF Code to create AutoIncrement column MS SQL?
Does anyone have any Coldfusion code to insert a new autoincrement key field in to a MS SQL table and then remove the old one? Or is there a way to modify a key field to autoincrement? If you're just running into occasional collisions, you could use cflock with an exclusive lock to limit that part of the code so that it can only be run by one thread at a time (others will get queued up and wait for the first to complete. If you do want to switch to an autonumber field, if you're running SQL Server, you'll need to switch the field to be an identity column. I generally use Enterprise Manager to make these kinds of changes so I don't have the code. There is another related discussion at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65257 with some info that may or may not help you out. -Justin ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321863 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Code to create AutoIncrement column MS SQL?
I have a customer whose programmer has vanished and he is trying to get his application back on line. I'm an intermediate CF programmer and I've managed to get everything working again except for one problem. His database is hosted - and somehow, when he restored his application, the fields in his database lost their autonumbering capability. I tried creating the next number right before the insert by getting the max value of the field and then adding 1 to it, and that works for the most part, but occassionally, we are seeing the old Violation of PRIMARY KEY constraint 'PK_TblTestAnswers'. Cannot insert duplicate key in object 'dbo.TblTestAnswers' - and I can't figure out why unless to users are somehow hitting the same page at exactly the same time and clicking submit. So the only thing I can think of to fix the problem is to recreate the autoincrment key fields somehow. You can fix the problem, in the short term, by using the CFTRANSACTION tag to select the current value, increment by one, and insert the new record within a single transaction. Does anyone have any Coldfusion code to insert a new autoincrement key field in to a MS SQL table and then remove the old one? Or is there a way to modify a key field to autoincrement? It's not really a matter of writing CF code, you just have to write the appropriate SQL statement to modify the schema. I think that would involve creating a new table with the appropriate columns, copying the values from the original table to the new table, dropping the relationships to the original table, creating the relationships on the new table, then dropping the original table. I don't think you can simply alter the column if it's part of an index or primary key, although I could certainly be wrong about that. Once you write that SQL statement, you could run it through CFQUERY if permissions allow, or from any other environment that lets you send SQL statements to the server. You might be able to do this easier if you can connect to the SQL Server with the native management tools, as well - you may be able to just change the column type in place. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more informa ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321864 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Code to create AutoIncrement column MS SQL?
If you're just running into occasional collisions, you could use cflock with an exclusive lock to limit that part of the code so that it can only be run by one thread at a time (others will get queued up and wait for the first to complete. If you want to enforce transactional behavior on the database, use CFTRANSACTION, not CFLOCK. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321865 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Code to create AutoIncrement column MS SQL?
Hi Robert: Yes, that would be the best way. The problem is, I'm working from home, don't have any SQL tools installed on my home computer. So I was trying to think of a short cut that I could modify the table using Coldfusion. The database is a student test answer repository -- so they never modify records, only insert new ones. The largest table has about 22,000 records in it. Can I MS Microsoft SQL Server Management Studio Express, if I download it, to do what you are suggesting? Thanks! Dawn ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321868 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Code to create AutoIncrement column MS SQL?
CFTransaction is a good thought -- thanks -- I had thought of CFLock. I just wasn't sure what would be the best way. Long term though -- do you think it is better to redo the database? I was trying to do the quickest fix because it is a freebie. Thank you for everyone's help. Dawn I have a customer whose programmer has vanished and he is trying to get his You can fix the problem, in the short term, by using the CFTRANSACTION tag to select the current value, increment by one, and insert the new record within a single transaction. Does anyone have any Coldfusion code to insert a new autoincrement key field in to a MS SQL table and then remove the old one? Or is there a way to modify a key field to autoincrement? It's not really a matter of writing CF code, you just have to write the appropriate SQL statement to modify the schema. I think that would involve creating a new table with the appropriate columns, copying the values from the original table to the new table, dropping the relationships to the original table, creating the relationships on the new table, then dropping the original table. I don't think you can simply alter the column if it's part of an index or primary key, although I could certainly be wrong about that. Once you write that SQL statement, you could run it through CFQUERY if permissions allow, or from any other environment that lets you send SQL statements to the server. You might be able to do this easier if you can connect to the SQL Server with the native management tools, as well - you may be able to just change the column type in place. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more informa ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321869 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Code to create AutoIncrement column MS SQL?
CFTransaction is a good thought -- thanks -- I had thought of CFLock. I just wasn't sure what would be the best way. Long term though -- do you think it is better to redo the database? I was trying to do the quickest fix because it is a freebie. The quickest fix is to use CFTRANSACTION. The best approach would be to use an IDENTITY column, but again that might require significant work - you'd have to change the column as previously described, and you'd have to change your SQL in CF to accommodate that. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more informati ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321872 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CF Code to create AutoIncrement column MS SQL?
Dawn, yes the express edition will do the job perfectly for you, I'd recommend getting that installed. I'd speak with the hosting company about connecting, they'll give you the details you need to get started. My concern on this at the moment is working with live databases is never a good idea, especially if you're new to this stuff :-) if you can get a backup copy to test on first that would be favourable! Or at least make sure you have a backup incase you cock it up ;-) If you need any more guidance then let me know. Rob -Original Message- From: Dawn Sekel [mailto:dse...@ciber.com] Sent: 23 April 2009 17:51 To: cf-talk Subject: Re: CF Code to create AutoIncrement column MS SQL? Hi Robert: Yes, that would be the best way. The problem is, I'm working from home, don't have any SQL tools installed on my home computer. So I was trying to think of a short cut that I could modify the table using Coldfusion. The database is a student test answer repository -- so they never modify records, only insert new ones. The largest table has about 22,000 records in it. Can I MS Microsoft SQL Server Management Studio Express, if I download it, to do what you are suggesting? Thanks! Dawn ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321874 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Code to create AutoIncrement column MS SQL?
Studio Express or Toad for MS SQL both have free versions you can download. On Thu, Apr 23, 2009 at 9:51 AM, Dawn Sekel dse...@ciber.com wrote: Hi Robert: Yes, that would be the best way. The problem is, I'm working from home, don't have any SQL tools installed on my home computer. So I was trying to think of a short cut that I could modify the table using Coldfusion. The database is a student test answer repository -- so they never modify records, only insert new ones. The largest table has about 22,000 records in it. Can I MS Microsoft SQL Server Management Studio Express, if I download it, to do what you are suggesting? Thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321877 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Code to create AutoIncrement column MS SQL?
Try running: dbcc checkident(tablename, RESEED) If the identity seed has been reset, this should reseed it to the current highest PK value (ie. fix it without needing to create new columns). You can also use checkident to see what the current seed is and also to set it to any value you desire, eg. dbcc checkident(tablename) dbcc checkident(tablename, RESEED, 300) More: http://technet.microsoft.com/en-us/library/ms176057.aspx HTH, Dominic 2009/4/23 Dawn Sekel dse...@ciber.com: Hi: I have a customer whose programmer has vanished and he is trying to get his application back on line. I'm an intermediate CF programmer and I've managed to get everything working again except for one problem. His database is hosted - and somehow, when he restored his application, the fields in his database lost their autonumbering capability. I tried creating the next number right before the insert by getting the max value of the field and then adding 1 to it, and that works for the most part, but occassionally, we are seeing the old Violation of PRIMARY KEY constraint 'PK_TblTestAnswers'. Cannot insert duplicate key in object 'dbo.TblTestAnswers' - and I can't figure out why unless to users are somehow hitting the same page at exactly the same time and clicking submit. So the only thing I can think of to fix the problem is to recreate the autoincrment key fields somehow. Does anyone have any Coldfusion code to insert a new autoincrement key field in to a MS SQL table and then remove the old one? Or is there a way to modify a key field to autoincrement? Thanks in advance for any advice. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321902 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
