• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Column count doesn't match value count at row 1

 
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, i have certain data which i have to write to the database. Its an insert of records. The problem here is that i dont want to insert into all fields of the database but only those which is needed. I get the error saying "Column count doesn't match value count at row 1" This is a sample of my code.


String query = ("select Name, Leave_Entitlement from tb_employee where emp_ID = '" + userName + "'");
rst = stm.executeQuery(query);


String query1 = "SELECT (A.Leave_Entitlement - sum(B.Duration)) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = '" + userName + "'";
rst1 = stm1.executeQuery(query1);


String Leave_Type =(String) session.getAttribute("select1");
String Date_from =(String) session.getAttribute("startdate");
String Date_to =(String) session.getAttribute("enddate");
String Reason =(String) session.getAttribute("textarea");
String Duration =(String) session.getAttribute("duration");


String query2 = "INSERT into tb_leavemc VALUES('" + Leave_Type+ "','" + Date_from+ "','" + Date_to+ "','" + Reason+ "','" + Duration+ "',)";
stm2.executeUpdate(query2);


The other fields in the table are
emp_ID - the primary key
Leave_Approval_Status
MC_Serial_No
Status
AmPm
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That error means you are trying to insert too few values into a row. If you have nullable columns you don't want to insert values into you've got to specify which values you are inserting. i.e.
 
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi!

Well the correct INSERT statement that you need is:

INSERT INTO table_name(field1, field2, field3) values (val1, val2, val3)

;)
 
vanan saravanan
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi i tried this, but i get the below error.

javax.servlet.ServletException: Unknown column 'LeaveType' in 'field list'


String LeaveType =(String) session.getAttribute("select1");
String Date_from =(String) session.getAttribute("startdate");
String Date_to =(String) session.getAttribute("enddate");
String Reason =(String) session.getAttribute("textarea");
String Duration =(String) session.getAttribute("duration");


String query2 = "INSERT into tb_leavemc (Leave_Type) values (LeaveType)";
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by vanan saravanan:
Hi i tried this, but i get the below error.

javax.servlet.ServletException: Unknown column 'LeaveType' in 'field list'
[...]
String query2 = "INSERT into tb_leavemc (Leave_Type) values (LeaveType)";




Yes. (LeaveType) expresses the name of a column. If LeaveType is a java variable whose value you want to embed in the SQL, then do:


However, in the not-very-long run, you will want to use PreparedStatment, instead of Statement. Some of the many reasons:
1. Cleaner code
2. Faster on some databases (for repeated executions)
3. More secure
4. Don't have to escape embedded quote values
5. Fewer implicit data conversion problems
6. Cleaner code
7. It's what all the cool professional programmers use.
 
vanan saravanan
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you very much it works fine.

Just one small thing.

I get this error for the below code.

javax.servlet.ServletException: Field 'Leave_Type' doesn't have a default value


String query2 = "INSERT into tb_leavemc (emp_ID) values ('"+userName+"')";

emp_ID is the primary key in the database
 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
INSERT into tb_leavemc (emp_ID) values ('"+userName+"')
In the above query statement you trying to insert only emp_ID. May be (Leave_Type) attibute is defined as not nullin database and there is no default value specified for that in database. so you must supply value for Leave_Type whenever you are inserting record in tb_leavemc table.

so your query should be like:

String MyQuery = "INSERT into tb_leavemc (emp_ID,Leave_Type) values ('"+userName+"','"+LeaveType+"')";
 
vanan saravanan
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you very much! It works well now.
 
reply
    Bookmark Topic Watch Topic
  • New Topic