PreparedStatement Insert statement MS SQL Server problem

D

ddog

I'm trying to execute the following insert statement on MS SQL Server
v.8 using their JDBC drivers. I'm unable to insert the last field in
the table for some reason that I don't understand. The code follows:

Connection connection =null;
PreparedStatement pstmt = null;

Integer jobId = value.getJobTypeId();
Integer sequenceNumber = value.getSequenceNumber();
String fieldName = value.getFieldName();
String fieldDescription = value.getFieldDescription();
String fieldIdentifier = value.getFieldIdentifier();
String recordLocator = value.getRecordLocator();
java.sql.Date entryDttm = value.getEntryDttm();
java.sql.Date lastUpdatedDttm = value.getLastUpdatedDttm();


try {
connection = ConnectionManager.getConnection();
String sql = "INSERT INTO RECON_FIELD_IDENTIFIER
VALUES(?,?,?,?,?,?,?,?,?)";

pstmt = connection.prepareStatement(sql);

pstmt.setInt(1, jobId);
pstmt.setInt(2, sequenceNumber);
pstmt.setString(3, fieldName);
pstmt.setString(4, fieldDescription);
pstmt.setString(5, fieldIdentifier);
pstmt.setString(6, recordLocator);
pstmt.setDate(7, entryDttm);
pstmt.setDate(8, lastUpdatedDttm);
pstmt.setNull(9, java.sql.Types.INTEGER); // set value to null -
experiment
// OR pstmt.setNull(9, 1); // valid value for foreign key, fails also

// fails here....
pstmt.executeUpdate();
Error Message:
ReconFieldIndentifierDAO.insertReconFieldIdentifier() =
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK__RECON_FIE__FK_RE__239E4DCF'.
The conflict occurred in database 'RECON', table 'RECON_JOB_TYPE',
column 'JOB_TYPE_ID'.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]
[SQLServer]
INSERT statement conflicted with
COLUMN FOREIGN KEY constraint
'FK__RECON_FIE__FK_RE__239E4DCF'.
The conflict occurred in database 'RECON', table
'RECON_JOB_TYPE', column 'JOB_TYPE_ID'.

Both of the conflicting fiels are defined as ints.
The FK field in RECON_JOB_TYPE table named FK_RECON_FIELD_IDENTIFIER_1
is defined as (int, null) and accepts null values using straight
INSERT
statements via SQL Query Analyzer. When I try to insert a valid value
for
the foreign key or set it to null as above for an insert, I get the
same error
message.

Any ideas?
 
T

Thomas Kellerer

Error Message:
ReconFieldIndentifierDAO.insertReconFieldIdentifier() =
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK__RECON_FIE__FK_RE__239E4DCF'.
The conflict occurred in database 'RECON', table 'RECON_JOB_TYPE',
column 'JOB_TYPE_ID'.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]

This has noting to do with Java.

You are trying to insert a value into the column job_type_id which
obviously does not reference a valid primary key for the related foreign
table.

This is what the message
> INSERT statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK__RECON_FIE__FK_RE__239E4DCF'.

tells you.

Check the values of your insert statement and make sure the referenced
rows exist.

Thomas
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,832
Latest member
GlennSmall

Latest Threads

Top