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

x
?
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
Medium Priority
?
2,757 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
[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
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:bobbit31
bobbit31 earned 800 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 1000 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 1000 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 200 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Files go missing when using DFS (Distributed File System) Replication and how to recover them and fix it.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
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…
Suggested Courses

704 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