How can I remove a row from database with SQL and Netbeans?

Question

I have a problem with netbeans. I would like to remove a row from my database after push a jbutton but I can not.

I have like errors:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'car_manufacturing,modelyear,carprice'

Below is my code:

package cargestion;

import javax.swing.table.DefaultTableModel;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 *
 */
public class FenetreListeOutil extends javax.swing.JFrame {

  Statement stmt;
  Statement stmtcarlist;
  Connexion myconnexion = new Connexion();

  /**
   * Creates new form Fenetrecarlist
   */
  public Fenetrecarlist() {

    initComponents();

    DefaultTableModel model = new DefaultTableModel();

    model.addColumn("NAMECAR");
    model.addColumn("CAR MANUFACTURING");
    model.addColumn("Year");
    model.addColumn("PRICE");

    String requetecarlist = "select *from car";
    try {

      stmtcarlist = myconnexion.ObtenirConnexion().createStatement();
      ResultSet resultat = stmtcarlist.executeQuery(requetecarlist);
      while (resultat.next()) {
        model.addRow(new Object[]{resultat.getString("namecar"), resultat.getString("car_manufacturing"), resultat.getString("Modelyear"), resultat.getString("carprice")});
      }
    } catch (SQLException ex) {
      System.out.println(ex);

    }

    Tablecar.setModel(model);
  }

  /**
   * This method is called from within the constructor to
   * initialize the form.
   * WARNING: Do NOT modify this code. The content of this method is
   * always regenerated by the Form Editor.
   */
  @SuppressWarnings("unchecked")
  // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
  private void initComponents() {

    jLabel1 = new javax.swing.JLabel();
    jScrollPane1 = new javax.swing.JScrollPane();
    Tablecar = new javax.swing.JTable();
    jButton1 = new javax.swing.JButton();

    setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
    setName("Form"); // NOI18N

    org.jdesktop.application.ResourceMap resourceMap = org.jdesktop.application.Application.getInstance(cargestion.cargestionApp.class).getContext().getResourceMap(Fenetrecarlist.class);
    jLabel1.setFont(resourceMap.getFont("jLabel1.font")); // NOI18N
    jLabel1.setText(resourceMap.getString("jLabel1.text")); // NOI18N
    jLabel1.setName("jLabel1"); // NOI18N

    jScrollPane1.setName("jScrollPane1"); // NOI18N

    TableOutil.setModel(new javax.swing.table.DefaultTableModel(
        new Object[][]{
            {null, null, null, null},
            {null, null, null, null},
            {null, null, null, null},
            {null, null, null, null}
        },
        new String[]{
            "Title 1", "Title 2", "Title 3", "Title 4"
        }
    ));
    TableOutil.setName("TableOutil"); // NOI18N
    jScrollPane1.setViewportView(TableOutil);

    jButton1.setText(resourceMap.getString("jButton1.text")); // NOI18N
    jButton1.setName("jButton1"); // NOI18N
    jButton1.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        jButton1ActionPerformed(evt);
      }
    });

    javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
    getContentPane().setLayout(layout);
    layout.setHorizontalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGap(77, 77, 77)
                        .addComponent(jLabel1))
                    .addGroup(layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 375, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(layout.createSequentialGroup()
                        .addGap(125, 125, 125)
                        .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 117, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(15, Short.MAX_VALUE))
    );
    layout.setVerticalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 225, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addComponent(jButton1)
                .addContainerGap(20, Short.MAX_VALUE))
    );

    pack();
  }// </editor-fold>                        

  private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
    // TODO add your handling code here:
    String sql = "DELETE  FROM `car` " + "WHERE namecar=,car_manufacturing,Modelyear,carprice";

    try {
      stmt = myconnexion.ObtenirConnexion().createStatement();
      stmt.executeUpdate(sql);
    } catch (SQLException ex) {
      System.err.println(ex);
    }
  }

  /**
   * @param args the command line arguments
   */
  public static void main(String args[]) {
            /* Set the Nimbus look and feel */
    //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
            /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
             * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
             */
    try {
      for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
        if ("Nimbus".equals(info.getName())) {
          javax.swing.UIManager.setLookAndFeel(info.getClassName());
          break;
        }
      }
    } catch (ClassNotFoundException ex) {
      java.util.logging.Logger.getLogger(Fenetrecarlist.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    } catch (InstantiationException ex) {
      java.util.logging.Logger.getLogger(Fenetrecarlist.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    } catch (IllegalAccessException ex) {
      java.util.logging.Logger.getLogger(Fenetrecarlist.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    } catch (javax.swing.UnsupportedLookAndFeelException ex) {
      java.util.logging.Logger.getLogger(Fenetrecarlist.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    }
    //</editor-fold>

    /* Create and display the form */
    java.awt.EventQueue.invokeLater(new Runnable() {

      public void run() {
        new Fenetrecarlist().setVisible(true);
      }
    });
  }

  // Variables declaration - do not modify                     
  private javax.swing.JTable TableOutil;
  private javax.swing.JButton jButton1;
  private javax.swing.JLabel jLabel1;
  private javax.swing.JScrollPane jScrollPane1;
// End of variables declaration }

first of all where you are getting all thee column `(namecar,car_manufacturing,Modelyear,carprice)` values in your `jButton1ActionPerformed()`. @Goku45 — SatyaTNV, Jul 25 '15 at 17:49
it work but Why I must to write respective columns values every times. — Goku45, Jul 25 '15 at 18:14
in your delete query you only mentioned multiple columns. In `where` you can also use one column.That is based on your requirement. — SatyaTNV, Jul 25 '15 at 18:16

score 0 · Answer 1 · answered Jul 25 '15 at 16:54

0

You have a colon after equal simbol, also usually in a sentence delete the condition is the primary key of the table.

Try this

DELETE FROM car WHERE <primary_key_column>=<value>

Regards.

answered Jul 25 '15 at 16:54

santiago92

297
2
18

can I have an example please? – Goku45 Jul 25 '15 at 17:33
@Goku45 example of that? the delete statement, primary key,? – santiago92 Jul 25 '15 at 17:39
1

@Goku45 it's a example http://stackoverflow.com/questions/18378190/how-to-delete-certain-row-from-mysql-table – santiago92 Jul 25 '15 at 17:44

SatyaTNV · Answer 2 · 2015-07-25T18:25:27.380

0

Query syntax is invalid (, after each column)

String sql = "DELETE  FROM `car` " +"WHERE namecar=,car_manufacturing,Modelyear,carprice";

If you want to delete based on multiple column values use and operator in where clause.

String sql = "DELETE  FROM `car` WHERE namecar='some1' and car_manufacturing='some2' and Modelyear='some3' and carprice='some4'";

Edit:

String sql = "DELETE  FROM `car` WHERE namecar='some1';//single column in where clause

But you must use unique id in the where clause to delete a record. Otherwise some other records means duplicate also be deleted.

edited Jul 25 '15 at 18:25

answered Jul 25 '15 at 17:06

SatyaTNV

4,137
3
15
31

I don't understand ,what do I need to write in 'some1','some2'? can I have an example? – Goku45 Jul 25 '15 at 17:31
1

in place of `some1`, etc fill your respective columns values – SatyaTNV Jul 25 '15 at 17:38
it work but there is no general method without being forced to write respective columns values every times. – Goku45 Jul 25 '15 at 18:18
I would like select a row in my table with my mouse and click on delete button for delete the row. – Goku45 Jul 25 '15 at 21:22
so use mouse onclick listener to get a row. Under delete button, get the above selected row details here. pass those values to your query. @Goku45 – SatyaTNV Jul 26 '15 at 05:18

How can I remove a row from database with SQL and Netbeans?

2 Answers2