Forums Register Login

Trouble with Update statement in MySQL

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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.

+Pie Number of slices to send: Send
Thank you! I went back over the code and found the missing commas.
You know it is dark times when the trees riot. I think this tiny ad is their leader:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 532 times.
Similar Threads
Insert for mysql
SQL formatting question
Prepared SQL Update statement from Java
Problem Using PrintWriter class !
how to insert into 2 different tables
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 13:51:33.