Solved

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

Posted on 2004-08-30
9
967 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 

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

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!

Question has a verified solution.

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

Why do some people recommend buying business VoIP from an ISP? What are the benefits to my company? What are the costs?
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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