• 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

Trouble with Update statement in MySQL

 
Greenhorn
Posts: 8
Netbeans IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Netbeans IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you! I went back over the code and found the missing commas.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic