Java & SQL: ExecuteUpdate()/UPDATE not updating a record correctly

Question

I run my code with the setup done correctly. All the Strings are indeed strings, and all the ints are ints. There's no error here - the program executes the update without error and the input values are what they should be. For instance, I take the record for the person with the name "First Last", and tell the code to change it to "Work Please", but after updating, the database shows that it has been changed to "-1 Last". Nothing but the FirstName changes - and FirstName changes to -1 regardless of what I put in.

Relevant snippet: I've debugged it to the point where it executes it, and all the values are there - even "Joe". But instead, it decides to update the whole record with no changes except for FName being "0" or "-1".

sql = "UPDATE Member SET FName = ? and LName = ? and Email = ? and "
                                + "Year = ? and Shirt = ? and Comm = ? and Phone = ? and PPhone = ? and Events = ? where MemberID = ? ";
pstmt = dbConnection.prepareStatement(sql);
pstmt.setString(1, "Joe");
pstmt.setString(2, tempm.getName().split(" ")[1]);
pstmt.setString(3, tempm.getEmail());
pstmt.setInt(4, tempm.getYear());
pstmt.setInt(5, tempm.getShirt());
pstmt.setInt(6, tempm.getComm());
pstmt.setString(7, phonea[0]);
pstmt.setString(8, phonea[1]);
pstmt.setString(9, tempm.getEvents());
pstmt.setInt(10, tempm.getmID());
pstmt.executeUpdate();

Changing from and to , gives

sql = "UPDATE Member SET FName = ?, LName = ?, Email = ?, "
                                + "Year = ?, Shirt = ?, Comm = ?, Phone = ?, PPhone = ?, Events = ? where MemberID = ?";

Which leads to the same result.

Ha, My bad. I had a problem in testing the individual things, I called the item itself (testing the individual, not the code above, btw)... LName changes fine by itself, as well as FName by itself... Testing both at the same time - They work.. I'll go check the rest in this fashion, I'll probably get it with this sort of debugging. Thank you. — Ktk
– Ktk, Commented Mar 20, 2011 at 21:32

Mark Byers · Accepted Answer · 2011-03-20 21:12:07Z

3

You should use commas rather than and in your update statement:

sql = "UPDATE Member SET FName = ?, LName = ?, ... WHERE MemberID = ?";

Your current query is being interpreted as:

sql = "UPDATE Member SET FName = (? AND LName = ? AND ...) WHERE MemberID = ?";

The part in parentheses is a boolean expression, where the = is the equality comparison operator rather than assignment as you intended.

edited Mar 20, 2011 at 21:12

answered Mar 20, 2011 at 21:06

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ktk Over a year ago

Thanks for the tip - I thought the repetition of "and" was a bit odd. But I still get the same result, "-1 Lastname"...

Austin · Accepted Answer · 2011-03-20 21:35:11Z

1

Maybe try surrounding your answers with ' '

I remember having a similar problem and once I did that it fixed it, no promises though.

answered Mar 20, 2011 at 21:35

Austin

4,9596 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Java & SQL: ExecuteUpdate()/UPDATE not updating a record correctly

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related