ODBC Error Code = 23000 (Integrity constraint violation)

LelloLello used Ask the Experts™
My code is working fine but the insert is not going into the table why?
i'm having the following ODBC Error Code 230000

My ID is unique...

<!--- Insert a record of the Transaction in the Database --->
 						<cfquery name="InsertUserInfo" datasource="#application.datasource#" username="#application.username#" password="#application.password#" maxrows=25>
						tblC(dteDate, Name, Amount, Confirmation_Num, Invoice, CardNum, Data, Event)   
						<cfif CreditCard EQ "Yes"> 
						'#Mid(Form.intCardNo, 1, Len(Form.intCardNo)-4)#',				
						'#text_to_insert#', 'CIA Network Subscription 2012') 

Open in new window

My SQL Table

id  int  not allow nulls
dteDate datetime allownulls
name varchar 255 allow nulls
Twelvedigits varchar50
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You need to look and see what constraits you have on the table.   Either a unique key or the primary key is being violated.

That is, the table constrait ensures that you do not enter a duplicated value into the table (the value already exists).   The question is what is that column or combination of columns that you are not allowed to duplicate...

Then ensure you do not enter a value that is already in the table.
to expand on what GD has said, this is often the case when you don't have autoincrement enabled for your primary key

id  int  not allow nulls

if you are not using autoincrement you have to write code to get the last id ie: max(id) and increment it manually) or use an ID that is a GUID
Most Valuable Expert 2015
> write code to get the last id ie: max(id) and increment it manually)
> or use an ID that is a GUID

For my $0.02 .. please don't use max(id)  :) Go with an auto number or GUID like Sid suggested.

Yes, it's possible to do it safely ;) ... but we all know how often people forget about multithreading and end up swapping record ids or showing the wrong data to the wrong person. Yikes...
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

ha yes you're right bad suggestion (slaps self on wrist) possible do do safely but you do have to know what you're doing. Far easier to simply change the ID to autoInc.

A note about autoincrement. If you are using an autoincrement ID for a userid or something similiar it's important to not rely on that exclusively for things like displaying information via url variable or hidden form variables.

If you have sequential numbers and no other checks (such as against a session id set at login, it is simple to change a user id in a url or hidden form value to view another users information.

And in case you don't think this is a problem, it affects a major US govt website that we use on a regular basis. I've reported it twice but they still (last time I checked) haven't fixed it. The information disclosure isn't very sensitive but it should never happen.


You are the experts !! Thank you i saw the error on the increment in SQL. ... but i have a question on this form. 

Right now i have two option.
Fellows, Affiliates or Correspondents of an actuarial organization or CFA members   100.00*
Associates of an actuarial organization   $525*

If i take out this line 'Associates of an actuarial organization   $525* ' my form tax calculations will not work and no one is able to know the problem so i will post the question pls help me. 

Associates of an actuarial organization   $525*  

Please help me on that question... thank you.


Looking forward for your advice on my other question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial