Delete Record in Java using SQL

Question

Update: I tried the below suggestions, however it is still not working. When trying employeeid=1, it does say successful, however went going into mysql command line, it still shows the record there.

I tried looking at different posts and my insert works but for some reason i am unable to the delete function to work.

I am trying to delete a record but keep getting an error. Running mysql & netbeans. Please help, thank you.

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    // TODO add your handling code here:

    try {

        statement.executeUpdate("INSERT INTO `abcinc`.`employees`"
                + "(`EmployeeID`,`First_Name`,`Last_Name`,`Birthday`,`Dept_Name`)VALUES("
                + "'" + jTextField1.getText() + "'," 
                + "'" + jTextField2.getText() + "'," 
                + "'" + jTextField3.getText() + "'," 
                + "'" + jTextField4.getText() + "',"
                + "'" + jTextField5.getText() + "');");

        JOptionPane.showMessageDialog(null, "Successful Update!!!", "Database Messages",
                      JOptionPane.OK_OPTION);
    }
    catch(SQLException error){
        JOptionPane.showMessageDialog( null, error.getMessage(), "Database Messages",
                      JOptionPane.ERROR_MESSAGE );
    }
}                                        

private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    // TODO add your handling code here:
    try {

        statement.executeUpdate("DELETE FROM `abcinc`.`employees`"
                + "WHERE (`EmployeeID`,`First_Name`,`Last_Name`,`Birthday`,`Dept_Name`)VALUES("
                + "'" + jTextField1.getText() + "'," 
                + "'" + jTextField2.getText() + "'," 
                + "'" + jTextField3.getText() + "'," 
                + "'" + jTextField4.getText() + "',"
                + "'" + jTextField5.getText() + "');");

        JOptionPane.showMessageDialog(null, "Successful Update!!!", "Database Messages",
                      JOptionPane.OK_OPTION);
    }
    catch(SQLException error){
        JOptionPane.showMessageDialog( null, error.getMessage(), "Database Messages",
                      JOptionPane.ERROR_MESSAGE );
    }
}

You SQL syntax for DELETE is not correct (there's nothing like VALUES for delete). — qqilihq
– qqilihq, Commented Aug 12, 2013 at 13:09
Thanks everyone. This code was from my professor who created this. I was trying to follow it but did not understand. The fields are blank where a user can input information. The primary key is the SS(employee id) of the person being entered. I will try the below suggestions — user2675015
– user2675015, Commented Aug 12, 2013 at 13:12
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) { // TODO add your handling code here: try { statement.executeUpdate("DELETE FROM abcinc.employees" + "WHERE employeeid=1") So I am guessing it would/should look something like this? — user2675015
– user2675015, Commented Aug 12, 2013 at 13:21
AFAIK DELETE returns always successfull (when syntax correct) even when the WHERE clause matches no record. — PeterMmm
– PeterMmm, Commented Aug 12, 2013 at 13:47

pstanton · Accepted Answer · 2013-08-12 13:09:19Z

2

Delete does not follow that syntax.

try something like

DELETE FROM mytable WHERE mycolumn='X' AND myothercolumn='Y';

see docs here

answered Aug 12, 2013 at 13:09

pstanton

36.9k28 gold badges130 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2675015 Over a year ago

thanks, i tried this but not working for some reason: statement.executeUpdate("DELETE FROM abcinc.employees" + "WHERE 'employeeID'= jTextField1.getText()");

pstanton Over a year ago

probably your quoting of values. i suggest reading the error message you get and the documentation posted in my answer.

pstanton Over a year ago

also, try without the jtextfield integration and just get a query working first. your quoting is wrong and it would be clear if you focused on one bit at a time.

Gospel · Accepted Answer · 2013-08-12 13:10:56Z

0

i think the delete data sql syntax is like 'DELETE FROM TABLE_NAME WHERE COLUMN = ?',it's not contain the 'values'

answered Aug 12, 2013 at 13:10

Gospel

812 silver badges10 bronze badges

Comments

Robin Chander · Accepted Answer · 2013-08-12 13:11:38Z

0

Your SQL delete statement is incorrect.

I guess Employee number is unique in your table so you should try

DELETE FROM yourtable WHERE EmployeeID='someid';

answered Aug 12, 2013 at 13:11

Robin Chander

7,4753 gold badges30 silver badges42 bronze badges

2 Comments

user2675015 Over a year ago

Thanks, i tried that, but for some reason it is not taking it statement.executeUpdate("DELETE FROM abcinc.employees" + "WHERE 'employeeID'= jTextField1.getText()");

Robin Chander Over a year ago

note that you are placing jTextField1.getText inside " " making it a string and not function call

piet.t · Accepted Answer · 2013-08-12 13:13:20Z

0

The VALUES-clause is only for use in insert-statements.

Inside a WHERE-clause you'll have to use single predicates like

WHERE EmployeeID = ...
  AND First_Name = ...

You don't have to specify al attributes of the row you want to delete but only those that are needed to identify the row(s).

answered Aug 12, 2013 at 13:13

piet.t

11.9k21 gold badges45 silver badges56 bronze badges

1 Comment

user2675015 Over a year ago

Thanks, i tried that, but for some reason it is not taking it statement.executeUpdate("DELETE FROM abcinc.employees" + "WHERE 'employeeID'= jTextField1.getText()");

Jaydee · Accepted Answer · 2013-08-12 13:15:42Z

0

WHERE (EmployeeID,First_Name,Last_Name,Birthday,Dept_Name)VALUES

Needs to be a condition such as

WHERE EmployeeID=1

Secondly,

"'" + jTextField1.getText() + "'," + "'" + jTextField2.getText() + "'," + "'" + jTextField3.getText() + "'," + "'" + jTextField4.getText() + "'," + "'" + jTextField5.getText() +

is a very poor way of constructing the query as it leaves you wide open to SQL injection attacks.

You should use a prepared statement with parameters and then insert the parameters.

PreparedStatement ps = null;

try {

ps = conection.prepareStatement("insert into mytable (ID) values (?)");

ps.setString(1, jTextField5.getText());

ps.execute();

edited Aug 12, 2013 at 13:15

answered Aug 12, 2013 at 13:10

Jaydee

4,1481 gold badge21 silver badges20 bronze badges

1 Comment

user2675015 Over a year ago

Thanks, for now i rather not change it to a preparedstatement as i not that familar with java yet. I rather keep it as how my "insert" works. I will try the where employeeID=1. Also when i use employeeid=1, where is the "1" from?

Collectives™ on Stack Overflow

Delete Record in Java using SQL

5 Answers 5

3 Comments

Comments

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related