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

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,781 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
The decision to migrate to the cloud is not a simple one—many factors, such a cost, ease of use, and ongoing maintenance come into play. The goal is always for cloud platforms to make storage and backups more seamless and effective. Here’s a look at…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

879 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