Solved

Using JDBC Insert Statement Against MS Access With A Column That Is Set As AutoNumber

Posted on 2004-08-30
9
934 Views
Last Modified: 2008-01-09
Using JDK 1.3.1_09 and JDBC with Jdbc-odbc bridge against MS Access in a JSP page.

Primary key column (req_id) is set as autonumber so that it will auto populate upon inserting data into the other columns.

Receiving error: [ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.

Changed the field from autonumber to number, reduced the number of columns to only the primary key and hardcoded and inserted a primary key value via code. Added one more column and passed two values.

Both of them worked.

Any ideas as to why an column set with autonumber (Access) won't work!

Is there something else that needs to be sent along?

Code:

The Bean

public void insertDOBUserRequest(int int_reqtype) throws SQLException, Exception {
            if (con != null) {
                try {
                    // create a prepared SQL statement
                    PreparedStatement insert_requestdata;
                    String s = new String("insert into tbl_Request_Info values(?, ?);");
                    System.out.println(s);
                    insert_requestdata = con.prepareStatement(s);                    
                    insert_requestdata.setInt(1, 3);
                    insert_requestdata.setInt(1,5);

                    //insert_requestdata.setInt(3, int_reqapp);
                    //insert_requestdata.setInt(4, int_requestor);
                    //insert_requestdata.setString(5, str_description);
                    //insert_requestdata.setDate(5, dte_request_date);

                    insert_requestdata.execute();
            }
              catch (SQLException sqle) {
                    System.out.println(sqle.getMessage());
                    error = sqle.getMessage();
                    //error = "SQLException: update failed, possible duplicate entry";
                    throw new SQLException(error);
            }
            }
            else {
                error = "Exception: Connection to database was lost.";
            throw new Exception(error);
            }
      } // end of insertDOBUserRequest()
} // eof




The calling page (code snippet):

    <SELECT name='emps'>Emp Names
        <% dobemp.connect();
           dobemp.insertDOBUserRequest(7);
           ResultSet rs = dobemp.getDOBEmpNames(); %>    
            <%
           while (rs.next()) {
           String value = rs.getString(2)+", " + rs.getString(3);
                if (value == null) {
                    value = "No Value Found";
                }  %>
               
                <% out.print("<option>"+ value+"</option>");
            }   dobemp.disconnect(); %>            
           
     </SELECT>
0
Comment
Question by:RipTide
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 21

Assisted Solution

by:MogalManic
MogalManic earned 25 total points
ID: 11938232
Try inserting to specific columns
ie
INSERT into tbl_Request_Info(description, request_date) values (?, ?)

0
 
LVL 11

Assisted Solution

by:sompol_kiatkamolchai
sompol_kiatkamolchai earned 175 total points
ID: 11938315
Change

                    insert_requestdata.setInt(1, 3);
                    insert_requestdata.setInt(1,5);


to

                    insert_requestdata.setInt(1, 3);
                    insert_requestdata.setInt(2,5);
0
 
LVL 11

Assisted Solution

by:sompol_kiatkamolchai
sompol_kiatkamolchai earned 175 total points
ID: 11938333
You just set one value to prepare statement. It may be typo error.

String s = new String("insert into tbl_Request_Info values(?, ?);");
insert_requestdata.setInt(1, 3);
insert_requestdata.setInt(1,5); // <-- set to the same column. so the column two does not be set.
0
 
LVL 3

Assisted Solution

by:haobaba1
haobaba1 earned 50 total points
ID: 11941063
I don't know about ms access but I think the general rule with auto_increment and java prepared statements is that you would set the auto_increment column to null using setNull(field_number, Types.Integer); This should take care of your primary key. An example:

String sql="INSERT  INTO TABLENAME (AUTOFIELD, SOMEFIELD1, SOMEFIELD2) VALUES (?,?,?);

ps= createpreparedstatement(sql);
ps.setNull(1,Types.Integer);
ps.setString(2,string);
ps.setInt(3,integer);

......
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:RipTide
ID: 11948100
sompol_kiatkamolchai:

Thanks for feedback. After seeing you suggestion, I realize that I submitted the example wrong.

The code is actually as you suggested:insert_requestdata.setInt(1, 3); insert_requestdata.setInt(2,5);
0
 

Author Comment

by:RipTide
ID: 11948449
Testing the other suggestion.

Please keep them coming!

Although it deals with MYSQL, I'm also investigationg the following that I found at:
http://dev.mysql.com/tech-resources/articles/autoincrement-with-connectorj.html

Before version 3.0 of the JDBC API, there was no standard way of retrieving key values from databases that supported 'auto increment' or identity columns.

...the implementation of version 3.0 of the JDBC API that is available with JDK-1.4.0 and later.

....the new JDBC-3.0 method 'getGeneratedKeys()' which is now the preferred method to use if you need to retrieve AUTO_INCREMENT keys and have access to JDBC-3.0.

(Note he said 'no standard way'. This implies that there may have been options available in SUn SDK 1.3.)

********************************************************

Also, found support for auto-increment with 3.0 API. This is just for information as I am using 1.3.1 which is JDBC 2.0

java.sql
Interface Statement

http://java.sun.com/j2se/1.4.2/docs/api/index.html

getGeneratedKeys
public ResultSet getGeneratedKeys()
                           throws SQLExceptionRetrieves any auto-generated keys created as a result of executing this Statement object. If this Statement object did not generate any keys, an empty ResultSet object is returned.

Returns:
a ResultSet object containing the auto-generated key(s) generated by the execution of this Statement object
Throws:
SQLException - if a database access error occurs
Since:
1.4





0
 
LVL 11

Accepted Solution

by:
sompol_kiatkamolchai earned 175 total points
ID: 11948470
OK. I have another solution for you to investigate problem

Modify SQL Insert statement to have all column.

insert into tbl_Request_Info values(?, ?)

to

insert into tbl_Request_Info(column1,column2,...) values(?, ?,...)

I think if you use SQL without specify column name. Database engine will assume that you mean all column in the table and you set the values(?,?) just two field. If in the table have more than 2 fields, It will generate error.

Note. There is no need to include the field auto-number since database will do automatically.


0
 
LVL 11

Assisted Solution

by:sompol_kiatkamolchai
sompol_kiatkamolchai earned 175 total points
ID: 11948539
Yes. There is no standard way but JDBC3.0 provide api to retrieve. Another way is use SQL

select @@identity

After insert, it will return key that it just generates.
0
 
LVL 3

Assisted Solution

by:haobaba1
haobaba1 earned 50 total points
ID: 11951936
also SELECT LAST_INSERT_ID() will do the same with mysql as the @@identity and is connection specific as well.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Use of TCL script on Cisco devices:  - create file and merge it with running configuration to apply configuration changes
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

705 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

18 Experts available now in Live!

Get 1:1 Help Now