I’m coding on a java Swing sql project. I’m stuck at deleting and updating an information record. I can add a new record normally but can’t update or delete one. When I delete, it will notify ‘can’t find column number’. Here is my code and a picture of the error message. Thanks in advance.
private void JtbDeleteActionPerformed(java.awt.event.ActionEvent evt) {
int row = jtbStudent.getSelectedRow();
String cell = jtbStudent.getModel().getValueAt(row, 0).toString();
try {
stm = cnn.createStatement();
if (JOptionPane.showConfirmDialog(null, "Are you sure?", "WARNING",
JOptionPane.YES_NO_OPTION) == JOptionPane.YES_OPTION) {
stm.execute("DELETE FROM Student where RollNo = " + cell);
loadTable();
JOptionPane.showMessageDialog(null, "Delete Successfully");
} else {
JOptionPane.showMessageDialog(null, "Delete Unsuccesfully!");
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
finally{
try {
stm.close();
rs.close();
} catch (Exception e) {
}
}
}
Update button
private void jbtUpdateActionPerformed(java.awt.event.ActionEvent evt) {
try {
int row = jtbStudent.getSelectedRow();
String cell = jtbStudent.getModel().getValueAt(row, 0).toString();
stm = cnn.createStatement();
String value1 = jtxName.getText();
String value2 = jtxMark.getText();
String sql="UPDATE Student set Name='"+value1+"' ,mark='"+value2+"'
,where RollNo = '" + cell + "'";
stm.execute(sql);
System.out.println("Update success!");
loadTable();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
Advertisement
Answer
You are using an invalid SQL-statement. The line you are writing (and sending to the database) is DELETE FROM Student where RollNo = A02
.
What you expect (in valid SQL) would be: DELETE FROM Student where RollNo = 'A02'
.
Now for simplicity, you could use
stm.execute("DELETE FROM Student where RollNo = '" + cell + "'");
But that would be open to SQL injection, meaning that if the String in the variable cell
could read something like "'; drop table student;"
, your database would drop the students table.
Better use the prepared statement (https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html):
PreparedStatement stm = cnn.prepareStatement("DELETE FROM Student where RollNo = ?");
stm.setString(1, cell);
boolean result = stm.execute();
This way, the String
is properly escaped. Please refere to jdbc’s documentation for details.
PreparedStatement – https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html Connection – https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String)