Java SQL syntax error trying to delete from jtable

Question

i'm trying to delete selected row from jtable and database together but i'm getting syntax error. Might be cause i'm using varchar or really the syntax is just wrong in this case?

    btnNewButton_2 = new JButton("Dzēst");
    btnNewButton_2.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
            Statement statement = null;
            try {
                int tableRow = table_1.getSelectedRow();
                Object Kods = table_1.getValueAt(tableRow, 0);
                Object Nosaukums = table_1.getValueAt(tableRow, 1);
                Object Inventara = table_1.getValueAt(tableRow, 2);
                Object Uzskaites = table_1.getValueAt(tableRow, 3);
                Object Iegad = table_1.getValueAt(tableRow, 4);
                Statement stmt = null;
                Connection connection = ConnectDB();


                String sql = "DELETE FROM users " +
                       "WHERE Kods = " + Kods + " AND Nosaukums = '" + Nosaukums + "' AND Inventara Nr = " + 
                        Inventara  + " AND Uzskaites vertiba = '" + Uzskaites  + "' AND Iegades vertiba = " + Iegad;
                stmt = connection.createStatement();
               stmt.executeUpdate(sql);

            } catch (SQLException ex) {
                Logger.getLogger(dddddddd.class.getName()).log(Level.SEVERE, null, ex);
                  JOptionPane.showMessageDialog(null, "Nav ievadita visa informacija");
            }

ERROR:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Nr = ttttt AND Uzskaites vertiba = '3.0' AND Iegades vertiba = 3.0' at line 1

Database variables:

Kods -Index -int(255)       
Nosaukums - varchar(255)         
Inventara Nr - varchar(255)  
Uzskaites vertiba - float                
Iegades vertiba - float

I'd also recommend you have a look at PreparedStatements

MadProgrammer
– MadProgrammer

2017-10-14 22:36:15 +00:00
Commented Oct 14, 2017 at 22:36 — MadProgrammer
– MadProgrammer, Commented Oct 14, 2017 at 22:36

peterulb · Accepted Answer · 2017-10-14 22:53:33Z

1

Note that your columns have space-separated names. Normally it isn't best-practice to use such a naming convention, but snake_case or CamelCase.

In your case, you need to properly quote your column names, e.g.

WHERE `Inventara Nr` = '33'

Be aware though that your code is vulnerable to SQL injections. I recommend reading about them. You should always use PreparedStatements.

edited Oct 14, 2017 at 22:53

answered Oct 14, 2017 at 22:16

peterulb

3,05317 silver badges22 bronze badges

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

9 Comments

Krichukzz Over a year ago

Thanks, will keep in note. Did change everything with snake_case but now there is new error. Unknown column 'ttetetetetete' in 'where clause'.

peterulb Over a year ago

@Krichukzz Everything character like has to be put in quotes as well. Example: DELETE FROM docs WHERE content = 'Hello World'. But as mentioned, PreparedStatements will do that for you when using setString for your parameter. In your case 'Inventara Nr' was indeed a varchar, so it should be WHERE 'Inventara Nr' = 'ttetetetetete'. This might help you using PreparedStatements: docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Krichukzz Over a year ago

String sql = "DELETE FROM users " + 		                   "WHERE Kods = " + Kods + " AND Nosaukums = '" + Nosaukums + "' AND Inventara_Nr = '" +  		                    Inventara  + "' AND Uzskaites_vertiba = " + Uzskaites  + " AND Iegades_vertiba = " + Iegad; 		            stmt = connection.createStatement(); 		           stmt.executeUpdate(sql);

Still does not want to delete anything. Even if i put 'Nosaukums' and 'Inventara_Nr' in quoutes.

peterulb Over a year ago

Can you put a System.out.println(sql) right after you assigned it?

Krichukzz Over a year ago

DELETE FROM users WHERE Kods = 6 AND Nosaukums = 'Zirgs' AND Inventara_Nr = 'ZirgsTT' AND Uzskaites_vertiba = 65.66 AND Iegades_vertiba = 4444.4

|

Collectives™ on Stack Overflow

Java SQL syntax error trying to delete from jtable

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related