Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 995
  • Last Modified:

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

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
RipTide
Asked:
RipTide
  • 4
  • 2
  • 2
  • +1
7 Solutions
 
MogalManicCommented:
Try inserting to specific columns
ie
INSERT into tbl_Request_Info(description, request_date) values (?, ?)

0
 
sompol_kiatkamolchaiCommented:
Change

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


to

                    insert_requestdata.setInt(1, 3);
                    insert_requestdata.setInt(2,5);
0
 
sompol_kiatkamolchaiCommented:
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
Independent Software Vendors: 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!

 
haobaba1Commented:
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
 
RipTideAuthor Commented:
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
 
RipTideAuthor Commented:
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
 
sompol_kiatkamolchaiCommented:
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
 
sompol_kiatkamolchaiCommented:
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
 
haobaba1Commented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now