Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    93
    Rep Power
    0

    Default The statement was aborted... error

    Hello. I am building a program that uses a GUI to display and manipulate a small database. So far, I have successfully gotten the program to display a database record, move forwards and backwards through the database to display additional records, and make and save changes to an existing record. The next task was to add and save a new record

    And I'm 99.5% of the way there. (thanks to you guys I was able to identify and correct two additional NullPointerErrors that cropped up in the new code I had written. Thank you so much!! )

    Here's what happens. The program compiles, and the GUI correctly displays the first record in the database. I have a "Clear" button to wipe the display so I can enter new data. The "Clear" button works fine. After entering the new info in the GUI, I then have a "Save" button to write the new info into the ResultSet, and then into the database. My database is small: it only has six records in it. Each record has a unique identifier in it, called "ID", a simple integer, now going from 1 thru 6. If I enter a new record and give it the ID value of 7, I get an error when I click on the "Save" button. If I enter a new record and give it an ID value of 8, it is saved, and the new record is added to the database (no error message when clicking on "Save").

    This is the error message in it's entirity:
    The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL121007102045790' defined on 'WORKERS'.
    BUILD SUCCESSFUL (total time: 27 seconds)


    ("WORKERS is the name I gave the database") So the code I've written works, except when I use ID value of 7. I understand the database requires the ID value to be unique for each entry (I set it up this way). But the value of 7 IS unique in the existing database, so I'm not understanding why I get an error when using 7 as the ID, but not 8, or 9 or other numbers.

    Can anyone help me figure this out?

    Here is the code for the "Save" button operation (I'm working in NetBeans.).

    Java Code:
    private void butSaveNewActionPerformed(java.awt.event.ActionEvent evt) {
            
            // Collect information from the textfields
            String first = textFName.getText();
            String last = textLName.getText();
            String job = textJob.getText();
            String ID = textID.getText();
            int newID = Integer.parseInt(ID);
            
            try {
                // Creates a new row to the ResultSet, and adds the new info to it 
                rs.moveToInsertRow();
                rs.updateInt("ID", newID);
                rs.updateString("First_Name", first);
                rs.updateString("Last_Name", last);
                rs.updateString("Job_Title", job);
                rs.insertRow();                // Inserts the info in the ResultSet 
                                               // into a new row in the database
                
                // Closing the Statement Object and the ResultSet Object
                stmt.close();
                rs.close();
                // Now reload the ResultSet.  This manuver ensures the new entry is 
                // added to the ResultSet and the database. 
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
                String SQL = "SELECT * FROM APP.Workers";   
                rs = stmt.executeQuery(SQL);
                // Reloading the first database row into the ResultSet, and then into the GUI
                rs.next();
                int id_col = rs.getInt("ID");
                String id = Integer.toString(id_col);
                String first2 = rs.getString("First_Name");
                String last2 = rs.getString("Last_Name");
                String job2 = rs.getString("Job_Title");
                
                textID.setText(id);
                textFName.setText(first2);
                textLName.setText(last2);
                textJob.setText(job2);   
                
                JOptionPane.showMessageDialog(Workers.this, "New entry saved");
                
            }
            catch (SQLException err){
                System.out.println(err.getMessage());
                
            }
        }

  2. #2
    pbrockway2 is offline Moderator
    Join Date
    Feb 2009
    Location
    New Zealand
    Posts
    4,585
    Rep Power
    12

    Default Re: The statement was aborted... error

    Quote Originally Posted by Humphrey Bogart View Post
    If I enter a new record and give it the ID value of 7, I get an error when I click on the "Save" button. If I enter a new record and give it an ID value of 8, it is saved, and the new record is added to the database (no error message when clicking on "Save").

    This is the error message in it's entirity:
    The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL121007102045790' defined on 'WORKERS'.
    BUILD SUCCESSFUL (total time: 27 seconds)
    It is common for id values to be automatically generated by the database rather than having the code have to specify them. You can probably appreciate the value of this! but it may not be relevant here if this is a homework problem or whatever.

    Try putting sprinkling some System.out.println()s about. Or perhaps you have... But make sure you put them *everywhere* that data gets added (or removed) from the database.

    Java Code:
    System.out.printf(
            "About to insert row: %d-%s-%s-%s%n",
            newId, first, last, job);
    rs.insertRow();
    I'm partial to printf(), but println() will do the job just as well. The database will not lie to you: somewhere or other a record with id "7" had been added.

    Don't forget to include the code that first populates the database. (7==6+1, which is less evidence than many courts seem to require.)

    ---

    Not necessarily what you ought to do right now: if the database access code were in a different class and not here and there among the gui handlers you might better be able to keep track of "accounting" stuff like the id. And better be able to check the database calls when anything goes wrong.

    Secondly, IDEs include a debugger which will give you all the variable value information you need at the click of a button. They are well worth getting used to at some point.
    Last edited by pbrockway2; 10-13-2012 at 01:22 AM.

  3. #3
    Join Date
    Mar 2011
    Posts
    93
    Rep Power
    0

    Default Re: The statement was aborted... error

    Hello.

    I haven't yet tried to track this down using System.out.print, so I will try and report back.

    The database I'm using doesn't automatically generate id values. Ultimately, I hope to write a method to automatically increment the last id value and put it into the GUI and database. This will make life easier for the user (the user will not need to know how many records already exist in the database), and helps prevent user errors.

    Thanks for your help.

  4. #4
    Join Date
    Mar 2011
    Posts
    93
    Rep Power
    0

    Default Re: The statement was aborted... error

    Hello. Update: it turns out (yet again!) that the problem exists between my ears!!

    The program works fine, but I had previously entered a record using ID value of 7, so the error message I was getting was correctly telling me that there already exists a record of ID value 7.

    Duh, right?

  5. #5
    pbrockway2 is offline Moderator
    Join Date
    Feb 2009
    Location
    New Zealand
    Posts
    4,585
    Rep Power
    12

    Default Re: The statement was aborted... error

    I'm glad you've got it sorted out!

    the problem exists between my ears!
    Think about it this way: the database, the Java language, etc have all had clever engineers working on them for quite a while. They form a coherent, well functioning set of technologies. The extra ingredient is *you*. When things go wrong (or don't go right), almost certainly they'll be a misunderstanding, a lack of knowledge or some technique not yet known about or mastered. As you put it: a problem between the ears.

    The other side of the story is much more upbeat, however. The technologies change only slowly, but you will be learning something every day: what the compiler messages mean, how to deal with them. (later, styles of code that are less liable to failure, and the ways and role of testing, etc).

    It's when we start learning something that we make the most rapid progress. Contrast what you've done (gone from a broken to a functioning program) with what a whole team of engineers might come up with labouring for a week (some incremental improvements that you won't notice unless they're brought to your attention in a whitepaper).

    It's not just quantitative either (amount of stuff learnt per hour), it's qualitative: from developing an appreciation of the value of patient, painstaking attention to detail to experiencing the creativity of computing. (Concerning the latter point, a case could be made that the last 20 years of ubiquitous consumer oriented computers and content have fostered a generation largely unaware of the fun, creative, stuff played around with by their parents with soldering irons and games supplied in the form of source code.)

    So, keep going! Use this (or other) forums where you feel you just can't make progress. And remember to stop occasionally and celebrate your successes: with a post here, perhaps, or something more liquid.

Similar Threads

  1. Replies: 2
    Last Post: 03-26-2012, 05:27 PM
  2. SQL Statement Error
    By Dcalladi in forum New To Java
    Replies: 3
    Last Post: 01-30-2012, 05:38 PM
  3. Error: if statement
    By nandhinianand in forum NetBeans
    Replies: 2
    Last Post: 08-07-2011, 06:32 PM
  4. error in if statement
    By carman12 in forum New To Java
    Replies: 11
    Last Post: 12-25-2010, 02:13 AM
  5. Replies: 7
    Last Post: 06-08-2010, 02:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •