CODEWITHSUNDEEP
mysqlcalculated-columns
McDuck4
McDuck4

Reputation: 662

QLException caught: Column count doesn't match value count at row

Suddenly I am having a problem with my MySQL database. I have in my database the following columns:

P_id INT NOT NULL AUTO_INCREMENT,
Date DATE NOT NULL,
Name VARCHAR(32) NOT NULL,
Address VARCHAR(32) NOT NULL,
Day_hours INT NOT NULL,
Day_minutes INT NOT NULL,
Km_to_address INT NOT NULL,
Time_to_address INT NOT NULL,
Allday_hours INT
PRIMARY KEY(P_id)

In My servlet I send the following request:

Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(connectionURL, "root", ""); 
            String sql = "INSERT INTO Workdata VALUES (?,?,?,?,?,?,?)"; 
            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setString(1, Date);
            pst.setString(2, Name);
            pst.setString(3, Address);
            pst.setString(4, Day_hours);
            pst.setString(5, Day_minutes);
            pst.setString(6, Km_to_address);
            pst.setString(7,  Time_to_address);

            pst.executeUpdate();
            pst.close();

So the point is that I have my P_id as primary key, which just should count automatically up every time I add some data. As you can see I don't have a setString to Allday_hours. I am using this column to calculate the Day_hours and Day_minutes. I do this with the query:

SELECT *, (Day_hours + (Day_minutes / 100)) as Allday_hours FROM Workdata

The purpose of this is that my girlfriend work different places during a day. Therefore she can put in fx 5 different places, and then Allday_hours calculate how much she has been working during a day. But when I put in my data in the JSP site I get the error:

QLException caught: Column count doesn't match value count at row 1

I would guess that it has something to do with my Servlet, where I have my setString(1, Date). I have tried to set it to setString(2, date) and the change the others to 3, 4, 5 etc.

Can somebody see what I am doing wrong here? Best Regards and good day to you all From Mads

Upvotes: 1

Views: 54

Answers (2)

Fabio
Fabio

Reputation: 23510

I think you get the error because your table actually has 9 columns but in your query you are only inserting 7 values without put columns which are involved in the insert query so better to specify in the query which column will be used. Also your column Allday_hoursi think shouldn't be in the table because since will be created by your selecting query as fictional. Your P_id column can be avoided in the insert query because it's an autoincrement column so it will insert its value by itself

String sql = "INSERT INTO Workdata (date, name, address...) VALUES (?,?,?,?,?,?,?)";

Upvotes: 1

Mr47
Mr47

Reputation: 2655

Allday_hours should not be defined as a real column in your data model; you return a fictional column with your select query...

Upvotes: 0

Related Questions