CODEWITHSUNDEEP
javasqlsql-updaterecord
Ktk
Ktk

Reputation: 13

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

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.

Upvotes: 1

Views: 4328

Answers (2)

Austin
Austin

Reputation: 4929

Maybe try surrounding your answers with ' '

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

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 837936

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.

Upvotes: 3

Related Questions