count field incorrect error

[Werner Fletcher]

I have created an Access database, with a "Users" table with fields: "personname", "surname", "email", "username", "password", "secretQuestion", "answer".
I am getting a count field incorrect error when i process this:

String name1 = nameField.getText();
String surname1 = surnameField.getText();
String email1 = emailField.getText();
String username1 = usernameField.getText();
char[] password1 = passField1.getPassword();
String passString = new String(password1);
String question1 = (String)secQCombo.getSelectedItem();
String answer1 = answerField.getText();

try
      {
      st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      String update = "INSERT INTO Users VALUES "+name1+","+surname1+","+email1+","+username1+","+passString+","+question1+","+answer1;
      st.executeUpdate(update);
      }
 catch (SQLException sqle)
      {
       Toolkit.getDefaultToolkit().beep();
       JOptionPane.showMessageDialog(null, sqle.toString(), "Error", JOptionPane.ERROR_MESSAGE);
       }

Please tell me if I did something wrong here, because I don't know and I am ready to

[Paul Clapham]

You did two things wrong, actually.

First, your SQL INSERT statement doesn't use the correct syntax. Look here for a page which describes the correct syntax. (You didn't use parentheses.)

Second, constructing an SQL statement that way is error-prone and risky. For one thing, if any of the string values contain quotes -- and they could -- then the result is not valid SQL. And for another thing, it's possible for evil users to uses that statement for "SQL Injection" attacks; humorous example here.

The cure for both of those problems is to use PreparedStatement; you should find that in any reputable JDBC tutorial.

[Werner Fletcher]

Thanks alot guys, I appreciate the help alot. I am using PreparedStatement now aswell as MySQL server. I was having endless issues with MS Access and Win 7 64-bit aswell. Everything is working 100%.