Can you share a Coldfusion query to convert field to auto increment?

[Raymond Thompson]

Use CFLock around the insert to block any other person from using the same
code.

<cflock timeout="10" throwontimeout="Yes" name="DBInsert" type="EXCLUSIVE">
  <cfquery name="GetNextKey" datasource="ds">
    Select IsNull(Max(KeyColumn),0) + 1 as NextKey
    from DataBase
  </cfquery>
  <cfset tNextKey=GetNextKey.NextKey>
  <cfquery>
    insert into DataBase
      (KeyColumn,
       Column1)
    values
      (<cfqueryparam value="#tNextKey#" cfsqltype="CF_SQL_INTEGER">,
       ColumnData)
  </cfquery>
</cflock>

Ray Thompson
Tau Beta Pi (www.tbp.org)
The Engineering Honor Society
865-546-4578

-----Original Message-----
From: Dawn Sekel [mailto:dsekel@...]
Sent: April 23, 2009 12:10 PM
To: sql
Subject: Can you share a Coldfusion query to convert field to auto
increment?


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/sql/message.cfm/messageid:3215
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Dawn Sekel]

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/sql/message.cfm/messageid:3214
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Maya Tulchinsky]

 To recreate the auto increment key:
If you can go to table  - design -  set Identity Specification (or Identity for SQL Sever 2000) to Yes.

Also, if you need to reset the
identity value of the table:

DBCC CHECKIDENT can reseed (reset) the
identity value of the table. For example, YourTable has 25 rows with 25
as last identity. If we want next record to have identity as 35 we need
to run following T SQL script in Query Analyzer.
DBCC CHECKIDENT (yourtable, reseed, 34)
Hope this helps.

Maya

--- On Thu, 4/23/09, Dawn Sekel <dsekel@...> wrote:

From: Dawn Sekel <dsekel@...>
Subject: Can you share a Coldfusion query to convert field to auto increment?
To: "sql" <sql@...>
Date: Thursday, April 23, 2009, 9:10 AM


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/sql/message.cfm/messageid:3216
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6