• Post Reply Bookmark Topic Watch Topic
  • New Topic

Trouble with Update statement in MySQL  RSS feed

 
Nynaeve Ojeda
Greenhorn
Posts: 8
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am having trouble getting this update statement to work:
****
@Override
public void update(){
Statement stmt;

try{
ConnectIt conn = new ConnectIt();
stmt=conn.makeStatement();

String thesisTitle;
String thesisAdvisor;
String company;

Scanner in = new Scanner(System.in);

System.out.println("Update the record");
System.out.println("Please enter the Student ID for the record you want to update >");
studentID = Integer.parseInt(in.next());
System.out.println("Please enter the new GPA>");
gpa = in.nextFloat();
System.out.println("Please enter new status, must be resident or nonresident>");
status = in.next();
System.out.println("Please enter new mentor");
mentor = in.next();
System.out.println("Enter new level, must be freshman, sophmore, junior, or senior>");
level = in.next();
thesisTitle = "n/a";
thesisAdvisor = "n/a";
company = "n/a";

// stmt.executeUpdate("Update student Set firstName='" + firstName + "', lastName='" + lastName + "', gpa='" + gpa + "', status='" + status + "', mentor='" + mentor + "', level='" + level + "' + thesisTitle ='" + thesisTitle + "' + thesisAdvisor ='" + thesisAdvisor + "' WHERE studentID=
//'" + studentID + "'");
stmt.executeUpdate("Update student Set firstName='" + firstName + "', lastName='" + lastName + "', gpa='" + gpa + "', status='" + status + "', mentor='" + mentor + "', level='" + level + "', + thesisTitle ='" + thesisTitle + "', + thesisAdvisor ='" + thesisAdvisor + "' WHERE studentID=
'" + studentID + "'");
stmt.close();
conn.close();

}
catch (SQLException e){
System.err.println("ERROR: Either cannot connect to db" + " or error with SQL statement.");

}
System.out.println("Undergraduate Student record successfully updated");
}
******
the result is that it gives me the SQLException message and the sucess message but does not update the database
 
Paul Clapham
Sheriff
Posts: 22832
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's not surprising that you get the "success" message, because your code does that regardless of whether any exceptions occurred. As for the other message, there's a lot of information in the exception but your code ignores all of it and just prints a "didn't work" message regardless of what the problem actually was.

So to fix those problems:

1. Move the "success" message inside the try-clause (after where you close everything) so it will only be executed if no exceptions are thrown.

2. Change the try-clause to look like this:



That will show you a stack trace and the description of the exception, so you can tell what the problem was and what line of code threw the exception.

And to fix the problem which you're going to find out about when you do that, I suggest that you use a PreparedStatement to do the update. There's a good chance that you've messed up in getting the quotes in the right place, that's very easy to do. Your PreparedStatement would look something like this:



and you'd need to call pstmt.setString() and methods like that to fill in the parameters before executing it. So read up on PreparedStatement.

 
Nynaeve Ojeda
Greenhorn
Posts: 8
Chrome Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you! I went back over the code and found the missing commas.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!