> 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
