How to delete a record (string) JAVA and MYSQL

Question

I successfully can delete an integer but when I tried to make it a STRING it says "unknown column itemtodelete in where clause but my ITEMTODELETE is a STRING declared in the database not an integer how much It doesn't delete a STRING?

below is my code:

 private void DeleteButtonActionPerformed(java.awt.event.ActionEvent evt) {                                            
        int del = (prompt):
        if (del == JOptionPane.YES_OPTION){
        DelCurRec();
        }

    }     


   public void DelCurRec() {

        String id = field.getText();
        String SQL = "DELETE FROM inventory WHERE ItemCode = "+id+" ";

        try {
           Class.forName(connectio);
       }  catch (Exception e) {
           JOptionPane.showMessageDialog(null,""+e.getMessage(),"JDBC Driver Error",JOptionPane.WARNING_MESSAGE);
       }

        Statement stmt = null;
        Connection con = null;

        //Creates connection to database
        try {
            con = DriverManager.getConnection("Connection");
            stmt = con.createStatement();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null,""+e.getMessage(),"Connection Error",JOptionPane.WARNING_MESSAGE);
        }

        //Execute the SQL statment for deleting records
        try {
            stmt.executeUpdate(SQL);
            //This closes the connection to the database
            con.close();
            //This closes the dialog
            JOptionPane.showMessageDialog(null,"Deleted Succesfully","Delete Successful",JOptionPane.WARNING_MESSAGE);
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null,""+e.getMessage(),"Communication Error",JOptionPane.WARNING_MESSAGE);
        }
    }

Emil L · Accepted Answer · 2012-02-25 07:20:14Z

7

Do NOT use a Statement use a PreparedStatement instead, otherwise your application will be vulnerable to SQL injections. E.g. someone enters a string like: "'; drop table inventory; --"

The corresponding prepared statment would look something like:

String SQL = "DELETE FROM inventory WHERE ItemCode = ? ";
PreparedStatement pstmt = null;

// get a connection and then in your try catch for executing your delete...

pstmt = con.prepareStatement(SQL); 
pstmt.setString(1, id);
pstmt.executeUpdate();

answered Feb 25, 2012 at 7:20

Emil L

21.2k3 gold badges48 silver badges71 bronze badges

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

Comments

Vikram · Accepted Answer · 2012-02-25 07:01:18Z

6

try changing the line:

String SQL = "DELETE FROM inventory WHERE ItemCode = "+id+" ";

to

String SQL = "DELETE FROM inventory WHERE ItemCode = '"+id+"' ";

answered Feb 25, 2012 at 7:01

Vikram

8,32335 silver badges47 bronze badges

Comments

Shashank Kadne · Accepted Answer · 2012-02-25 07:01:52Z

0

I think you need to pass Integer.parseInt(id) and not id...assuming your id is int

answered Feb 25, 2012 at 7:01

Shashank Kadne

8,1017 gold badges43 silver badges54 bronze badges

Comments

Procrastinator · Accepted Answer · 2022-01-26 06:59:54Z

0

This worked for me:

Statement stmt=con.createStatement();
 
stmt.executeUpdate("DELETE FROM student WHERE reg_number='R18854';");

edited Jan 26, 2022 at 6:59

Procrastinator

2,67445 gold badges31 silver badges37 bronze badges

answered Jan 22, 2022 at 4:11

Ravindu Sathsara

1352 silver badges5 bronze badges

Collectives™ on Stack Overflow

How to delete a record (string) JAVA and MYSQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related