Solved

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

Posted on 2004-08-30
9
938 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
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.

 

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

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

With more and more people tethered to the internet, advertisers can reach you in new and creative ways. Push notifications on your smartphone, customized advertisements based on your previous searches, and email inboxes filled with promotional pitch…
Facing problems with you memory card? Cannot access your memory card? All stored data, images, videos are lost? If these are your questions...than this small article might help you out in retrieving your lost or inaccessible data.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

948 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

20 Experts available now in Live!

Get 1:1 Help Now