java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; when inserting data into SQL database through JAVA

Question

I am designing a JFrame where I will accept data for Name, Username, Password, Phone, No. and an image location 'img' from a JFileChooser. Impractically, I am inserting passwords without hashing and the Phone No. column has data type as VarChar(45). That should be overlooked as I am pretty new to SQL programming using JAVA.

The Sign-Up button is used to insert data into an SQL row. The ActionListener is as follows:

signupbtn.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                try
                {
                    Statement st=conn.createStatement();
                    ResultSet rs=null;
                    String name=namef.getText(),usnm=usnmf.getText(),pswd=pswdf.getText(),ph=phf.getText();
                    String sql="insert into data (name,username,password,ph.no.,profile) values("+name+","+usnm+","+pswd+","+ph+","+img+")";
                    st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
                    int key = 0;
                    rs = st.getGeneratedKeys();
                    if (rs.next()) 
                    {
                        key = rs.getInt(1);
                    }
                    JOptionPane.showMessageDialog(null, "Key : "+key);
                }
                catch(Exception ex)
                {
                    System.out.println(ex);
                }
            }
        });

When the data is given and the button is clicked, an Exception is catched and printed:

java.sql.SQLSyntaxErrorException: 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 ',profile) values(Jyotirmay,Usnm,Pswd,1122334455,E:\1.jpg' at line 1

Why is the error showing up? I have tried several methods like using PreparedStatement instead of Statement with ? but still getting the error.

P.S.

Using PreparedStatement:

btnNewButton_1.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                try
                {
                    String sql="INSERT into data (name, username, password, ph.no., profile) VALUES (?,?,?,?,?);";
                    PreparedStatement st=conn.prepareStatement(sql);
                    String name=namef.getText(),usnm=usnmf.getText(),pswd=pswdf.getText(),ph=phf.getText();
                    st.setString(1, name);
                    st.setString(2, usnm);
                    st.setString(3, pswd);
                    st.setString(4, ph);
                    st.setString(5, img);
                    st.executeUpdate();

                    JOptionPane.showMessageDialog(null, "SUCESS! Data inserted. Try Logging In.");

                }
                catch(Exception ex)
                {
                    System.out.println(ex);
                }
            }
        });

Note: img is already declared and initialised. Also, the asolute image location is correctly printed in the console.

Assuming all the table fields are string (TEXT, VARCHAR, etc): String sql = "INSERT into data (name, username, password, ph.no., profile) VALUES ('" + name + "','" + usnm + "','" + pswd + "','" + ph + "','" + img + "');"; Read This. — DevilsHnd - 退した
– DevilsHnd - 退した, Commented Apr 1, 2020 at 6:54
@DevilsHnd Thanks for your response but it surely didn't work and I'm still getting the error. — Jyotirmay
– Jyotirmay, Commented Apr 1, 2020 at 6:57
you can not write a column name such as ph.no. without quoted within the column list of the insert statement. — Barbaros Özhan
– Barbaros Özhan, Commented Apr 1, 2020 at 7:21
It doesn't seem to like the table field name ph.no.. Enclose all field names in apostrophes as well. — DevilsHnd - 退した
– DevilsHnd - 退した, Commented Apr 1, 2020 at 7:21
String sql = "INSERT INTO data ('name', 'username', 'password', 'ph.no.', 'profile') VALUES (?, ?, ?, ?, ?);"; — DevilsHnd - 退した
– DevilsHnd - 退した, Commented Apr 1, 2020 at 7:26

Seeseemelk · Accepted Answer · 2020-04-01 07:28:44Z

You cannot use a dot in a column name without escaping it. It's probably best to rename the column so it is not using dots anymore, although you can also try to use backticks in the column name like this:

btnNewButton_1.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                try
                {
                    String sql="INSERT into data (name, username, password, `ph.no.`, profile) VALUES (?,?,?,?,?);";
                    PreparedStatement st=conn.prepareStatement(sql);
                    String name=namef.getText(),usnm=usnmf.getText(),pswd=pswdf.getText(),ph=phf.getText();
                    st.setString(1, name);
                    st.setString(2, usnm);
                    st.setString(3, pswd);
                    st.setString(4, ph);
                    st.setString(5, img);
                    st.executeUpdate();

                    JOptionPane.showMessageDialog(null, "SUCESS! Data inserted. Try Logging In.");

                }
                catch(Exception ex)
                {
                    System.out.println(ex);
                }
            }
        });

Collectives™ on Stack Overflow

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; when inserting data into SQL database through JAVA

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