Solved

JDBC; mySQL; ErrorMessage "Column count doesn't match value count at row 1" ; Cannot enter data into table

Posted on 2004-08-12
4
2,654 Views
Last Modified: 2009-12-16
Entering data into database table fails with following error message:

SQLException: General error,  message from server: "Column count doesn't match value count at row 1"
SQLState: S1000
VendorError: 1136

Shouldn't address_id increment automatically? Here is the description of the table:

mysql> DESCRIBE address;
+--------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null   | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| address_id| bigint(20) unsigned |      | PRI | NULL    | auto_increment |
| country      | varchar(20)         |      |     |         |                |
| district     | varchar(20)         | YES  |     | NULL    |                |
| constituency | varchar(20)         | YES  |     | NULL    |                |
| location     | varchar(20)         | YES  |     | NULL    |                |
| sublocation  | varchar(20)         | YES  |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
 
And here is my DBHandler:

public class DBHandlerAddress extends HttpServlet {
       
      public void doPost (HttpServletRequest request,
      HttpServletResponse response) {
      try { // OBTAIN A DATABASE CONNECTION
         
          // Step 1: Load the JDBC driver          
          Class.forName("com.mysql.jdbc.Driver");
             
          // Step 2: Establish a connection to the database          
       
          Connection conn = DriverManager.getConnection(
          "jdbc:mysql://localhost/rtpmKenyaDB","rmwijage", "nahumCh2");
          String sql = "INSERT INTO address( country, district," +
               "constituency, location, sublocation) VALUES (?, ?, ?, ?, ?)";
          PreparedStatement ps = conn.prepareStatement(sql);
         
          // Retreave the bean properties to be inserted into table rtpm_user
          AddressFormBean f = (AddressFormBean) request.getAttribute("formHandler");
         
          String cntry = f.getCountry();
          String dstrct = f.getDistrict();
          String cnstncy = f.getConstituency();
          String lcn = f.getLocation();
          String sblcn = f.getSublocation();
           
          // Set the parameters in the prepared statement
          ps.setString(1, id);
          ps.setString(2, cntry);
          ps.setString(3, dstrct);
          ps.setString(4, cnstncy);
          ps.setString(5, lcn);
          ps.setString(6, sblcn);
         
          // Insert the row in the address table
          ps.executeUpdate();

          // Close the connection
          ps.close();
          conn.close();    
      }
      catch (SQLException e) {
          // handle any errors
          System.out.println("SQLException: " + e.getMessage());
          System.out.println("SQLState: " + e.getSQLState());
          System.out.println("VendorError: " + e.getErrorCode());
          }
      catch (Exception e) {
      e.printStackTrace();
      }
     
      // Send the request back to success_registration.jsp
      try {
          getServletConfig().getServletContext().
            getRequestDispatcher("/success_address.jsp").
             forward(request, response);
      }
      catch (Exception e){}
     
   }
}


// I do appreciate your kind help.
0
Comment
Question by:Rrugemalira
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:bobbit31
bobbit31 earned 200 total points
ID: 11788006
yes... so remove this:

          ps.setString(1, id);

and update the rest of the indices accordingly
0
 
LVL 3

Accepted Solution

by:
haobaba1 earned 250 total points
ID: 11788242
for the auto increment field you need to call ps.setNull(1, Types.Int);

So for the table you describe you should have 6 parameters to insert a new record. lets say:

          String sql = "INSERT INTO address(address_id, country, district," +
               "constituency, location, sublocation) VALUES (?,?, ?, ?, ?, ?)";
          PreparedStatement ps = conn.prepareStatement(sql);
         
          // Retreave the bean properties to be inserted into table rtpm_user
          AddressFormBean f = (AddressFormBean) request.getAttribute("formHandler");
         
          String cntry = f.getCountry();
          String dstrct = f.getDistrict();
          String cnstncy = f.getConstituency();
          String lcn = f.getLocation();
          String sblcn = f.getSublocation();
           
          // Set the parameters in the prepared statement
          ps.setNull(1,Types.Int); //tell it the type of column that it is dealing with
          ps.setString(2, cntry);
          ps.setString(3, dstrct);
          ps.setString(4, cnstncy);
          ps.setString(5, lcn);
          ps.setString(6, sblcn);

0
 
LVL 3

Assisted Solution

by:haobaba1
haobaba1 earned 250 total points
ID: 11788272
Also to get the id that was just created you can use this query after your insert call:

queryLastInsertId = new String("SELECT LAST_INSERT_ID()");

This query will return the last primary that was created with teh connection that you currently have open so it is thread safe in teh sense that even other records are inserted into other tables with different connections you will still get the last primary key generated by teh connection you are using.

0
 
LVL 11

Assisted Solution

by:sompol_kiatkamolchai
sompol_kiatkamolchai earned 50 total points
ID: 11789884
Yes. bobbit31 is right. you use prepared statement with just 5 parameter but you try to set 6 parameter.
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

Title # Comments Views Activity
Exception creating bean of class 5 167
password protect pdf 11 74
tomcat users xml 7 106
web service Rest Client creation 4 81
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

22 Experts available now in Live!

Get 1:1 Help Now