missing database (near "where": syntax error)

Question

I am trying to update my sqlite3 database but its giving this error.I was able to successfully insert data to same database but I am not able to update it.Please help.

[SQLITE_ERROR] SQL error or missing database (near "where": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:909)
at org.sqlite.core.DB.newSQLException(DB.java:921)
at org.sqlite.core.DB.throwex(DB.java:886)
at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
at org.sqlite.core.DB.prepare(DB.java:227)
at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>

my code

btnUpdate = new JButton("Update");
    btnUpdate.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {
            try {
                String query = "update students set ID='"+ id.getText() +"' , username='"+ username.getText() + "', password='"+ pass.getText() +"', firstname='"+ fname.getText() +"','"+ lname.getText() +"' WHERE ID='"+ id.getText() +"'  ";
                PreparedStatement pst = connection.prepareStatement(query);
                pst.execute();

                JOptionPane.showMessageDialog(null,"data updated successfully");

                pst.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    });
    btnUpdate.setFont(new Font("Tahoma", Font.PLAIN, 20));
    btnUpdate.setBounds(199, 246, 126, 43);
    contentPane.add(btnUpdate);

Important side note: the way you build your query is vunerable to SQL injection. Since you're already using a PreparedStatement build it like ... set ID=?, username=?, ... and then call pst .setParameter(index, value). That way PreparedStatement will take care of escaping the value, type conversions etc. and the query will be much easier to read and to spot errors. — Thomas
– Thomas, Commented Oct 18, 2018 at 12:42
Print out query, copy and try to run that. It should give you some direction. — Nicholas K
– Nicholas K, Commented Oct 18, 2018 at 12:44
Another a little less important side note: learn about the event dispatch thread. Right now you're executing your query directly in the action which, if the query takes a while to execute, could block any further ui updates or events (such as input events) until that query is finished. — Thomas
– Thomas, Commented Oct 18, 2018 at 12:48

Leviand · Accepted Answer · 2018-10-18 12:48:07Z

1

The error is here you added the lastname value without the field:

... +"','"+ lname.getText() +"' ...

You missed the lastname parameter before the value, something like:

... +"', lastname='"+ lname.getText() +"' ...

which become

String query = "update students set ID='"+ id.getText() +"' , username='"+ username.getText() + "', password='"+ pass.getText() +"', firstname='"+ fname.getText() +"', lastname='"+ lname.getText() +"' WHERE ID='"+ id.getText() +"'  ";

edited Oct 18, 2018 at 12:48

answered Oct 18, 2018 at 12:43

Leviand

2,8034 gold badges33 silver badges46 bronze badges

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

1 Comment

Thomas Over a year ago

I'd add what that portion of the query would look like in both cases to make it clearer, i.e. ..., firstname='bobby','tables' WHERE ID='... vs ..., firstname='bobby', lastname='tables' WHERE ID='....

Collectives™ on Stack Overflow

missing database (near "where": syntax error)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related