Error My SQL in Java Language (Netbeans)

Question

I have code:

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

    try {
        con = Connect.ConnectDB();
        if (PatientID.getText().equals("")) {
            JOptionPane.showMessageDialog(this, "Please retrieve Patient ID", "Error", JOptionPane.ERROR_MESSAGE);
            return;
        }
        if (txtNoOfDays.getText().equals("")) {
            JOptionPane.showMessageDialog(this, "Please enter no. of days", "Error", JOptionPane.ERROR_MESSAGE);
            return;
        }
        if (txtServiceCharges.getText().equals("")) {
            JOptionPane.showMessageDialog(this, "Please retrieve service charges", "Error", JOptionPane.ERROR_MESSAGE);
            return;
        }

        if (txtBillingDate.getText().equals("")) {
            JOptionPane.showMessageDialog(this, "Please enter billing date", "Error", JOptionPane.ERROR_MESSAGE);
            return;
        }
        if (txtTotalPaid.getText().equals("")) {
            JOptionPane.showMessageDialog(this, "Please enter total paid", "Error", JOptionPane.ERROR_MESSAGE);
            return;
        }
        double add1 = Double.parseDouble(txtRoomCharges.getText());
        double add = Double.parseDouble(txtNoOfDays.getText());
        double add2 = add * add1;
        txtTotalRoomCharges.setText(String.valueOf(add2));
        double add3 = Double.parseDouble(txtServiceCharges.getText());
        double add5 = ((add * add1) + add3);
        txtTotalCharges.setText(String.valueOf(add5));
        double paid = Double.parseDouble(txtTotalPaid.getText());
        txtTotalPaid.setText(String.valueOf(paid));
        if (add5 > paid) {
            double datadue = add5 - paid;
            txtDueCharges.setText(String.valueOf(datadue));
        }
        //double add1 = Double.parseDouble(txtTotalCharges.getText());
        //double add2 = Double.parseDouble(txtTotalPaid.getText());

        Statement stmt;
        stmt = con.createStatement();
        String sql1 = "Select DischargeID from bill_room where DischargeID= " + txtDischargeID.getText() + "";
        rs = stmt.executeQuery(sql1);
        if (rs.next()) {
            JOptionPane.showMessageDialog(this, "Record already exists", "Error", JOptionPane.ERROR_MESSAGE);
            return;
        }

        String sql = "insert into bill_room(DischargeID,BillingDate,RoomCharges,ServiceCharges,PaymentMode,PaymentModeDetails,ChargesPaid,DueCharges,TotalCharges,NoOfDays,TotalRoomCharges) values(" + txtDischargeID.getText() + ",'" + txtBillingDate.getText() + "'," + txtRoomCharges.getText() + "," + txtServiceCharges.getText() + ",'" + cmbPaymentMode.getSelectedItem() + "','" + txtPaymentModeDetails.getText() + "'," + txtTotalPaid.getText() + "," + txtDueCharges.getText() + "," + txtTotalCharges.getText() + "," + txtNoOfDays.getText() + "," + txtTotalRoomCharges.getText() + ")";
        pst = con.prepareStatement(sql);
        pst.execute();
        JOptionPane.showMessageDialog(this, "Successfully saved", "Record", JOptionPane.INFORMATION_MESSAGE);
        btnSave.setEnabled(false);

    } catch (HeadlessException | SQLException ex) {
        JOptionPane.showMessageDialog(this, ex);
    }

I have this code, when the execution runs (sometimes goes well, sometimes error.) When error happen:

com.mysql.jdbc.exceptions.jbdc4.MySQLSyntaxErrorException" You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for right sysntax to use near '110800.0,9,10800.0); at line 1

When Run clearly

What is not quite right here? I need your help or your suggest

Using PreparedStatements won't only solve this error, but also defend you from sql-injection attacks — Mureinik
– Mureinik, Commented Jun 3, 2017 at 15:36
As the error message tells you, your SQL syntax is broken. You're dynamically building SQL based on input at runtime (which, by the way, is wide open to SQL injection attacks), so we don't know what SQL code you're actually executing. When you debug, what's the actual SQL code? — David
– David, Commented Jun 3, 2017 at 15:37

Graham · Accepted Answer · 2017-09-27 20:19:11Z

1

You already use PreparedStatement why you don't use it properly, so to avoid syntax error and SQL injection you can use :

String sql = "insert into bill_room(DischargeID, BillingDate, "
        + "RoomCharges, ServiceCharges, PaymentMode, PaymentModeDetails, "
        + "ChargesPaid, DueCharges, TotalCharges, NoOfDays, TotalRoomCharges) "
        + "values(?, ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?)";

try (PreparedStatement insert = connection.prepareStatement(sql)) {

    insert.setInt(1, txtDischargeID.getText());
    insert.setString(2, txtBillingDate.getText());

    //...Set the the right type if int use setInt if string use setString ...

    insert.setDouble(11, Double.parseDouble(txtTotalRoomCharges.getText()));

    insert.executeUpdate();
}

same thing with select.

edited Sep 27, 2017 at 20:19

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Jun 3, 2017 at 15:43

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

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

Collectives™ on Stack Overflow

Error My SQL in Java Language (Netbeans)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related