PreparedStatement Insert statement MS SQL Server problem

Discussion in 'Java' started by ddog, Feb 7, 2007.

  1. ddog

    ddog Guest

    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?
     
    ddog, Feb 7, 2007
    #1
    1. Advertising

  2. On 07.02.2007 18:06 ddog wrote:
    > 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
     
    Thomas Kellerer, Feb 7, 2007
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Manoj S. P.
    Replies:
    2
    Views:
    4,420
    Manoj S. P.
    Jul 2, 2003
  2. Ken Philips
    Replies:
    4
    Views:
    49,038
    venkat.sadasivam
    Nov 5, 2008
  3. JY
    Replies:
    11
    Views:
    25,148
    Andrea Desole
    Feb 24, 2006
  4. tomas
    Replies:
    3
    Views:
    1,325
    Bomber
    Sep 16, 2006
  5. Alex Kizub
    Replies:
    10
    Views:
    1,777
    Alex Kizub
    Feb 11, 2010
Loading...

Share This Page