Ask a Question related to Coldfusion Database Access, Design and Development.
-
crystalfish0318 #1
ODBC Error Code = 23000 (Integrity constraint violation)
Hi,
I have been trying to fix this problem for 3 hours, but still no progress.
The error is like this:
ODBC Error Code = 23000 (Integrity constraint violation)
[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the
table were not successful because they would create duplicate values in the
index, primary key, or relationship. Change the data in the field or fields
that contain duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again.
****************************
my main question is, how do i avoid the duplicate problem?
i'm pretty all of the names of my table, subject, etc are fine.
thansk a lot
Here's my attached ColdFusion code:
**********************************************
admin_add_subject.cfm
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<CFINCLUDE template="sl_admin.cfm">
<cfinclude template = "js_admin_add_subject.cfm">
<html>
<head>
<title>Add Subject to ADMIN LIST</title>
</head>
<body>
<table border="0" cellspacing="0">
<tr>
<td> </td>
</tr>
<td colspan="3" align="left" align="top">
</td>
</tr>
<tr>
<td> </td>
</tr>
<td colspan="3" align="CENTER" bgcolor="#4245A6">
<font face="arial, sans-serif" color="White">
<b>Add Subject to Database</b></font></td>
</tr>
<tr>
<td> </td>
</tr>
<form method="POST" name="memberform" action="add_source.cfm?type=subject">
<tr>
<td colspan=3 bgcolor="#DFEFFF">
<font face="arial, sans-serif" size=2 color="blue">
<b>Subject Information</b><BR></font>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td>
<font face="arial, sans-serif" size=2>
<b>Subject Name:</b></font></td>
<td>
<input type="text" name="course" size=25 maxlength=40 value="">
</tr>
<script language="javascript">
document.memberform.course.focus();
</script>
<tr>
<td>
<font face="arial, sans-serif" size=2>
<b>Subject Code:</b></font></td>
<td>
<input type="text" name="code" size=25 maxlength=40 value="">
</tr>
<tr>
<td height="55" colspan="4" align="center">
<input type="hidden" name="signup">
<input type="button" value="Add Subject" onClick="checkForm();">
<input type="Reset" value="Reset"
onClick="window.location.href='admin_add_subject.c fm'">
</td>
</tr>
</form>
</tr>
</table>
</body>
</html>
************************************************** ******
js_admin_add_subject.cfm
<CFINCLUDE template="sl_Admin.cfm">
<cfquery name="showSubject" datasource="dstbc" dbtype="ODBC">
Select subjectID, subject, subjectCode
from a2tblSubject
</cfquery>
<cfoutput query="showSubject">
<cfset listId = ValueList(showSubject.subjectID)>
</cfoutput>
<script type="text/javascript">
function checkForm(){
subjectLen = <cfoutput>#ListLen(listId)#</cfoutput>;
course = Array();
code = Array();
message = "";
IsError = false;
subFill = false;
codeFill= false;
if (document.memberform.course.value != ''){
for (i=0; i<(subjectLen-1); i++){
<cfoutput query="showSubject">
course[i] = "#subject#";
if (document.memberform.course.value == course[i]){
message += "The subject you enter already existed, please
re-enter.\n";
IsError = true;
break;
}
</cfoutput>
}
if (!IsError){
subFill = true;
}
}
else{
message += "Subject field is not completed.\n";
}
if (document.memberform.code.value != ''){
for (j=0; j<(subjectLen-1); j++){
<cfoutput query="showSubject">
code[j] = "#subjectCode#";
if (document.memberform.code.value == code[j]){
message += "The subject code you enter already existed, please
re-enter.\n";
IsError = true;
break;
}
</cfoutput>
}
if (!IsError){
codeFill = true;
}
}
else{
message += "Subject code field is not completed.";
}
if ((subFill) && (codeFill)){
document.memberform.submit();
}
else{
alert("The following errors occur:\n\n" + message);
};
}
</script>
crystalfish0318 Guest
-
Inconsistent error with create table statement containing foreign key constraint
Here is the clause defining a foreign key constraint. CONSTRAINT `contact_id_fk` FOREIGN KEY (`contact_id`) REFERENCES `conts` (`contact_id`) ... -
Recordset Disappears After an Integrity Constraint Violation
Hi everyone, I have some CF code that calls on a SQL Server 2000 stored procedure. The procedure has logic where it tries to insert into a table... -
ODBC Error Code = S0002 (Base table not found)
I just deployed my first CF application. The ISP has set up the DSN. But now, when I run the application, I get the following error: ODBC Error... -
Integrity constraint violation
I'm getting the following error when I try to insert the following code: Code: <cfquery datasource="#data_source#"> INSERT INTO... -
Error Evaluating Check Constraint
I receive this error when entering data in a currencty field, the funny thing is I don't have any validation rules set for this field. Does... -
SQLMenace #2 Re: ODBC Error Code = 23000 (Integrity constraintviolation)
my main question is, how do i avoid the duplicate problem?
You have a unique constraint/primary key on a table, you can not insert
duplicate values
First check if the value exists if it does give a message back saying name
already exists if it does not exists do an insert
I do not lnow what data goes into the table you can also make the key not
unique
SQLMenace Guest
-
mxstu #3 Re: ODBC Error Code = 23000 (Integrity constraintviolation)
crystalfish0318,
In addition... a "ODBC Error Code = 23000 (Integrity constraint violation)"
error is typically caused by an INSERT or UPDATE statement. I do not see an
insert or update query in the the code you posted. Maybe it is on the action
page or in a cfinclude file?
1) Can you post the cfquery code that throws this error message?
2) What is the table name involved in the cfquery throwing the error?
3) What are the primary keys/constraints for that table?
mxstu Guest
-
crystalfish0318 #4 Re: ODBC Error Code = 23000 (Integrity constraintviolation)
Hi mxstu,
1)
The error message i got is this:
Error Diagnostic Information
ODBC Error Code = 23000 (Integrity constraint violation)
[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the
table were not successful because they would create duplicate values in the
index, primary key, or relationship. Change the data in the field or fields
that contain duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again.
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (6:4) to (6:66).
Date/Time: 10/14/05 13:01:58
Browser: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.5)
Gecko/20050729 Netscape/8.0.3.3
Remote Address: 142.232.131.84
HTTP Referrer: [url]http://tutorbc.com/admin/admin_add_subject.cfm[/url]
Query String: type=subject
HOWEVER, i believe the actual error query is at my add_source.cfm
<cfswitch expression="#URL.type#">
<cfcase value="subject">
<cftransaction>
<cfquery name="InsertSubject" datasource="dstbc" dbtype="ODBC">
insert into a2tblSubject (subject, subjectCode)
values ('#form.course#',
'#form.code#')
</cfquery>
</cftransaction>
2) table names involved in the query:
a2tblSubject
which contains subjectID, subject, and subjectCode these 3 attributes.
3) PK constrainst:
subjectID is my PK. ----- Required: No
------ Indexed: Yes (No Duplicates)
subject ---- Indexed: No
subjectCode ---- Indexed: Yes (Duplicates OK)
~crystalfish0318
crystalfish0318 Guest
-
mxstu #5 Re: ODBC Error Code = 23000 (Integrity constraintviolation)
I think your problem is that "subjectID" is your primary key, but is not
required (which doesn't seem to make sense). So the first time you perform an
insert
insert into a2tblSubject (subject, subjectCode) values ('#form.course#',
'#form.code#')
.. the subjectID inserted will probably be null (or zero if this is a numeric
column). The next time you perform the insert, the db engine will try and
insert that same value (null or zero) which throws an error because the
"subjectID" values must be unique due to the "Indexed: Yes (No Duplicates)"
setting.
I'm assuming "subjectID" should be some type of unique numeric ID value? If so
you should probably make the column type "AutoNumber" so the id number is
created automatically each time you insert a new record.
mxstu Guest
-
crystalfish0318 #6 Re: ODBC Error Code = 23000 (Integrity constraintviolation)
thanks, mxstu,
I've changed it to AUTONUMBER, and it worked.
now, another problem came out:
Error Diagnostic Information
ODBC Error Code = 23000 (Integrity constraint violation)
[Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record
because a related record is required in table 'a2tblLevel'.
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (39:4) to (39:66).
Date/Time: 10/15/05 18:47:26
Browser: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.12)
Gecko/20050915 Firefox/1.0.7
Remote Address: 207.216.165.119
HTTP Referrer: [url]http://tutorbc.com/admin/admin_add_subject.cfm[/url]
Query String: type=subject
ColdFusion sure is interesting :)
i'm gonna continue tracking the errors.
crystalfish0318 Guest
-
mxstu #7 Re: ODBC Error Code = 23000 (Integrity constraintviolation)
It sounds like there is a relationship, with enforce referential integrity,
between one the columns in the [a2tblSubject ] table and the [a2tblLevel]
table, meaning you can only insert/use values in the related [a2tblSubject ]
table column that that already exist in the [a2tblLevel] table. So either
modify/remove the relationship or ensure that the necessary value exists in the
[a2tblLevel] table before running the insert into the [a2tblLevel] table.
Btw, all of the errors you have described are from your Access database not
CF.
mxstu Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
