how to handle auto generate number java with MS Sql Server in prepared statement

I attempt to insert values into MS Sql server table with auto generate number.
I use prepared statement.
how can I handle it. ?
My comments are in the code below
I appreciate anyone's suggestion.
Chris



String insertQry = " insert into " + eventView +
         "(EV_ID_NUMBER "         +  //auto number
         "SI_ID"                             +
         ", EV_COUNT_DATE"     +
         ", EV_DIR"                        +
         " ,EV_CONS"                   +
         " ,EV_INS_DATE"            +
         " ,EV_INS_WEATHER"    +
         " ,EV_REM_DATE"           +
         " ,EV_REM_WEATER"  +
         " ,EV_STATION_TYPE"  +
         " ,EV_RECEIVE_DATE)"  +
         "VALUES (?,?,?,?,?,?,?,?,?,?,?)";
// staID_FK - it is a number - used as a foreign key
//staInfoFromFile it is an object that contain incoming information, I need to insert to database
   MSSqlServer.updateQuery(insertQry, staID_FK, staInfoFromFile);
//-----------different class----------------------
public static void updateQuery (String query,Integer staID_FK, StationInfoStructure staInfoFromFile ) {
       try {
          PreparedStatement pstmt = connection.prepareStatement(query);
          //HERE I should add somehow auto number, but I am not sure
          //exactly how.... ???
          //set the values
          pstmt.setInt(1, staID_FK);
          pstmt.setString(2, staInfoFromFile.getCountDate() );
          pstmt.setString(3,staInfoFromFile.getDirection() );    
          pstmt.setString(4,staInfoFromFile.getConsultant());
          pstmt.setString(5, staInfoFromFile.getInstallDate() );
          pstmt.setString(6, staInfoFromFile.getInstallWeather());
          pstmt.setString(7,staInfoFromFile.getRemoveDate() );
          pstmt.setString(8,staInfoFromFile.getRemoveWeather() );
          pstmt.setString(9,staInfoFromFile.getStationType() );
          //data for testing purposes only , later get the NOW date
          //from the system.    
          pstmt.setString(10, "1/1/2009");
          //execute insert statement
          pstmt.executeUpdate();        
          
       } catch (SQLException ex) {
          StringWriter sw = new StringWriter();
          PrintWriter pw = new PrintWriter(sw);
          ex.printStackTrace(pw);
          JOptionPane.showMessageDialog ( null, new SubControllerErrorPanel("Error Occured in the Application", " Error when inserting/updating records in DB, MSSqlserver class.   \n" + sw.toString()),
                                                                                 "Submission Controller  Error",JOptionPane.ERROR_MESSAGE);
       }
           
    }

Open in new window

LVL 1
mordauthAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Remove this EV_ID_NUMBER from the INSERT statement and don't pass a value for it.

The database will take care of the autonumbering/value on insert, so you can just leave it off.


String insertQry = " insert into " + eventView +
         "(SI_ID"                             +
         ", EV_COUNT_DATE"     +
         ", EV_DIR"                        +
         " ,EV_CONS"                   +
         " ,EV_INS_DATE"            +
         " ,EV_INS_WEATHER"    +
         " ,EV_REM_DATE"           +
         " ,EV_REM_WEATER"  +
         " ,EV_STATION_TYPE"  +
         " ,EV_RECEIVE_DATE)"  +
         "VALUES (?,?,?,?,?,?,?,?,?,?)";

Open in new window

0
mordauthAuthor Commented:
It was my first try.
But I received an error message.
(I did similar program in Delphi, and there a database handled auto number; however Delphi uses different database driver)
It said about trying to insert "null" value into the column, which is not allowed.
I use JDBC driver.
0
Kevin CrossChief Technology OfficerCommented:
See if one of the 10 parameter values is returning null in your code -- use debugger of your development environment if you have one.  See if the values being passed are null at any point.  Either change the database column to allow nulls if column is optional or change code to pass "" for any nulls you find.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

mordauthAuthor Commented:
I used the debugger.
However, I think I have to change a method I use for Insert.
Using prepare statement is not going to work with AutoGenerated ID.
I found some refence about the following method: executeUpdate(String sql, int autoGeneratedKeys)
I think I have to change my insert into regular Sql statement and execute it with this method.
 
0
Kevin CrossChief Technology OfficerCommented:
Cool.  Glad you found that.  You should select your comment as solution if it works so future readers will know that solves it.
0
mordauthAuthor Commented:
Thanks for the advice.
Before I do it...I have to test it if it actually works, then I can post a solution
0
mordauthAuthor Commented:
To clarify,
The prepare statement works on MS Sql Server.
In order to execute use the following method:
executeUpdate() with no parameters.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.