Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-30
9
Medium Priority
?
983 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 21

Assisted Solution

by:MogalManic
MogalManic earned 50 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 350 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 350 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Assisted Solution

by:haobaba1
haobaba1 earned 100 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
 

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 350 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 350 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 100 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article, I’ll show how research, determination, and use of modern technology helped me solve a DNA mystery.
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 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