Solved

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

Posted on 2008-10-30
7
554 Views
Last Modified: 2013-11-23
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

0
Comment
Question by:mordauth
  • 4
  • 3
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22845209
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
 
LVL 1

Author Comment

by:mordauth
ID: 22846359
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22846415
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:mordauth
ID: 22846781
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22846796
Cool.  Glad you found that.  You should select your comment as solution if it works so future readers will know that solves it.
0
 
LVL 1

Author Comment

by:mordauth
ID: 22846823
Thanks for the advice.
Before I do it...I have to test it if it actually works, then I can post a solution
0
 
LVL 1

Accepted Solution

by:
mordauth earned 0 total points
ID: 22852912
To clarify,
The prepare statement works on MS Sql Server.
In order to execute use the following method:
executeUpdate() with no parameters.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
groovy example issue 10 90
mockito example issue 8 62
Java Server Faces parameter pass? 6 39
MySqlDump not dumping triggers 1 18
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This video teaches viewers about errors in exception handling.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now