com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException Error JAVA

Question

I am getting a error that I do not fully understand, why is it showing up ?
I have a form where I enter details into relevant JTextBoxes and data from those textboxes should be sent to a database I have set up on my local.

Full error message

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 ')VALUES'asd','asd',",",",",",",'Home','Female','White  
British' at line 1

Code

conn = (Connection) SQLConnect.ConnectDb();
    //inserting all values to database
    String sql = "INSERT INTO w1283057.criminalrecords "
            + "(FName, MName, Sname, DOB, Address, HPhone, BPhone, MPhone "
            + "ResidentStatus, Sex, Race, IncidentLocation, Zone, PremiseType "
            + "DateRecorded, TimeRecorded, Weapons, CrimeOffences) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    try{
        //Declaring all variable that wiil be collected from JTextField
        pst = conn.prepareStatement(sql);
        //coverting JText to String
        String fname = FName.getText();
        String mname = MName.getText();
        String surname = Surname.getText();
        String dob = DOB.getText();
        String fullAddress = FullAddress.getText();
        String hPhone = HPhone.getText();
        String bPhone = BPhone.getText();
        String mPhone = MPhone.getText();
        String incidentLocation = IncidentLocation.getText();
        String incidentZone = IncidentZone.getText();
        String incidentPrType = IncidentPrType.getText();
        String incidentDate = IncidentDate.getText();
        String incidentTime = IncidentTime.getText();
        String incidentWeapons = IncidentWeapons.getText();
        String crimeOffences = CrimeOffences.getText();
        String radioText = "";

        //collecting input data and assign them to variables            
            pst.setString(1, fname);
            pst.setString(2, mname);
            pst.setString(3, surname);
            pst.setString(4, dob);
            pst.setString(5, fullAddress);
            pst.setString(6, hPhone);
            pst.setString(7, bPhone);
            pst.setString(8, mPhone);
            //radio buttons Resident Status
            if(RHome.isSelected())
            {
                radioText = RHome.getText();
                pst.setString(9, radioText);
            }
            else if(RForeign.isSelected())
            {
                radioText = RForeign.getText();
                pst.setString(9, radioText);
            }
            //radio buttons Sex
            if(Male.isSelected())
            {
                radioText = Male.getText();
                pst.setString(10, radioText);

            }
            else if(Female.isSelected())
            {
                radioText = Female.getText();
                pst.setString(10, radioText);
            }
            //radio button Race
            if(WhiteBritish.isSelected())
            {
                radioText = WhiteBritish.getText();
                pst.setString(11, radioText);

            }
            else if(WhiteOther.isSelected())
            {
                radioText = WhiteOther.getText();
                pst.setString(11, radioText);
            }
            else if(Black.isSelected())
            {
                radioText = Black.getText();
                pst.setString(11, radioText);
            }
            else if(Asian.isSelected())
            {
                radioText = Asian.getText();
                pst.setString(11, radioText);
            }
            else if(Indian.isSelected())
            {
                radioText = Indian.getText();
                pst.setString(11, radioText);
            }
            /////////////////////////////////////
            pst.setString(12, incidentLocation);
            pst.setString(13, incidentZone);
            pst.setString(14, incidentPrType);
            pst.setString(15, incidentDate);
            pst.setString(16, incidentTime);
            pst.setString(17, incidentWeapons);
            pst.setString(18, crimeOffences);
            pst.executeUpdate();
            System.out.println("records added sucessfully");

    }
    catch (SQLException a)
        {
            JOptionPane.showMessageDialog(null, a);
        }

enter image description here

The prepared statement will take care of the quotation requirement — Bryan
– Bryan, Commented Jan 9, 2013 at 20:29
@BryanMoyles I know Its does not make much difference, but it provides easy reading. — Smit
– Smit, Commented Jan 9, 2013 at 20:31
The error message shows a missing opening bracket (after values) , yet your code includes it. Is it possible you are not showing us the code you actually run? — user330315
– user330315, Commented Jan 9, 2013 at 20:35

Bryan · Accepted Answer · 2013-01-09 20:27:22Z

5

You have a comma at the end of your value list, get rid of it, or populate it with an appropriate value.

+ "DateRecorded, TimeRecorded, Weapons, CrimeOffences,) " <---
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,)"; <----

answered Jan 9, 2013 at 20:27

Bryan

6,7522 gold badges19 silver badges21 bronze badges

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

13 Comments

Maciej Cygan Over a year ago

thank you for your reply, i have done as you said but i still get this error

Smit Over a year ago

@MaciejCygan Check you have same column name listed as those of ? in prepared statement. I mean you should have 18 column names and ? as stated in your code.

Bryan Over a year ago

Can you please update your code to show what you currently have? I'll have to agree with @smit and make the assumption that your column count isn't matching, or that you haven't removed the ending comma from both of the lines I have in my answer

Smit Over a year ago

This is kinda nice typo mistake . You are missing , at the end of sql at MPhone and PremiseType . Just add those , after these two words and I think you are good to go.

Smit Over a year ago

@MaciejCygan Better take a look at my previous comment. I pointed a mistake which could cause problem.

|

MG_Bautista · Accepted Answer · 2013-01-09 20:54:53Z

4

Your code

 String sql = "INSERT INTO w1283057.criminalrecords "
        + "(FName, MName, Sname, DOB, Address, HPhone, BPhone, MPhone "
        + "ResidentStatus, Sex, Race, IncidentLocation, Zone, PremiseType "
        + "DateRecorded, TimeRecorded, Weapons, CrimeOffences) "
        + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Try this... After the fields (MPhone, PremiseType) lack the comma

 String sql = "INSERT INTO w1283057.criminalrecords "
        + "(FName, MName, Sname, DOB, Address, HPhone, BPhone, MPhone, "
        + "ResidentStatus, Sex, Race, IncidentLocation, Zone, PremiseType, "
        + "DateRecorded, TimeRecorded, Weapons, CrimeOffences) "
        + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Greetings.

answered Jan 9, 2013 at 20:54

MG_Bautista

2,6812 gold badges21 silver badges33 bronze badges

1 Comment

Maciej Cygan Over a year ago

We have already figured out the right answer :) i was just waiting for someone to submit it, for everyone reference :) so thank you

Collectives™ on Stack Overflow

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException Error JAVA

2 Answers 2

13 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related