Android Database Insert Error

Question

I have a Java class for creating and populating a database. I also have an activity from which I am providing information to add to the database. I have a method for inserting data into the database but it's not working even though from my point of view it should work. Here is the code:

public class AccountDatabase extends SQLiteOpenHelper {
private final static String DB_Name="ACCOUNT_DATABASE";
private final static int DB_VERSION=1;
private final static String TABLE_NAME="ACCOUNT";
private final static String col_1="ID";
private final static String col_2="USERNAME";
private final static String col_3="PASSWORD";
private final static String col_4="RETYPED PASSWORD";
private final static String col_5="EMAIL";
private final static String col_6="PASSPHRASE";
AccountDatabase(Context context) {
    super(context, DB_Name, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE "+ TABLE_NAME+"(ID INTEGER PRIMARY KEY AUTOINCREMENT,USERNAME TEXT,PASSWORD TEXT,RETYPED PASSWORD TEXT,EMAIL TEXT,PASSPHRASE TEXT)");
}
@Override
public void onUpgrade(SQLiteDatabase db,int oldVersion, int newVersion){
    db.execSQL("DROP TABLE IF EXISTS"+TABLE_NAME);
    onCreate(db);
}
public boolean insertData(String username,String password,String retypedPassword,String email,String passphrase){
    SQLiteDatabase db=this.getWritableDatabase();
    ContentValues profileValues=new ContentValues();
    profileValues.put(col_2,username);
    profileValues.put(col_3,password);
    profileValues.put(col_4,retypedPassword);
    profileValues.put(col_5,email);
    profileValues.put(col_6,passphrase);
    long result=db.insert(TABLE_NAME,null,profileValues);//this produces result -1 and I don't know why
    if(result==-1)
        return false;
    else
        return true;
}

And this is the code for the Java activity from which I am calling the method:

public void submitAccount(View view){
    EditText info1=(EditText)(findViewById(R.id.username_edit_text));
    String message1=info1.getText().toString();
    EditText info2=(EditText)(findViewById(R.id.password_edit_text));
    String message2=info2.getText().toString();
    EditText info3=(EditText)(findViewById(R.id.retype_password_edit_text));
    String message3=info3.getText().toString();
    EditText info4=(EditText)(findViewById(R.id.email_edit_text));
    String message4=info4.getText().toString();
    EditText info5=(EditText)(findViewById(R.id.passphrase_edit_text));
    String message5=info5.getText().toString();
    if(!message1.isEmpty())
        counter++;
    if(!message2.isEmpty())
        counter++;
    if(!message3.isEmpty())
        counter++;
    if(!message4.isEmpty())
        counter++;
    if(!message5.isEmpty())
        counter++;
    if(counter==5) {
        mydb.insertData(message1,message2,message3,message4,message5);//this is where I call the method
        Toast.makeText(CreateAccountActivity.this, "Data inserted:" + message1 + "\n" + message2 + "\n" + message3 + "\n" + message4 + "\n" + message5, Toast.LENGTH_LONG).show();
        Intent send = new Intent(this, WelcomeActivity.class);
        String value = "true";
        send.putExtra(WelcomeActivity.EXTRA_MESSAGE, value);
        counter = 0;
        startActivity(send);

    }
    else{
        Intent send=new Intent(this,CreateAccountActivity.class);
        Log.i(msg,"Still in CreateAccountActivity");
        counter=0;
        startActivity(send);
    }
}

I am getting the following error,

SQL(query) error or missing database. (near "PASSWORD": syntax error (code 1): , while compiling: INSERT INTO ACCOUNT(EMAIL,PASSPHRASE,USERNAME,RETYPED PASSWORD,PASSWORD) VALUES (?,?,?,?,?))

Caused By : SQL(query) error or missing database. (near "PASSWORD": syntax error (code 1): , while compiling: INSERT INTO ACCOUNT(EMAIL,PASSPHRASE,USERNAME,RETYPED PASSWORD,PASSWORD) VALUES (?,?,?,?,?)) THIS IS THE ERROR I GET — Roberto Chirila, Jun 14 '16 at 11:58
Looks like you have a space between RETYPED and PASSWORD - your 4th parameter If you need a separator, use underscores "_" — mrkernelpanic, Jun 14 '16 at 12:02

score 1 · Answer 1 · edited May 23 '17 at 12:22

private final static String col_4="RETYPED PASSWORD";

If you want to have whitespace in your SQL column names, you would need to put it in e.g. "\"double quotes\"" but I would not recommend that - the Android sqlite API behaves inconsistently with such quoted column names.

Instead, just make the column name a single word such as retypedPassword.

I have fixed the column name and now I got this error. Caused By : SQL(query) error or missing database. (no such table: ACCOUNT (code 1): , while compiling: INSERT INTO ACCOUNT(EMAIL,PASSPHRASE,RETYPEDPASSWORD,USERNAME,PASSWORD) VALUES (?,?,?,?,?))

Uninstall your app to recreate the database. Incrementing the db version won't work unless you fix the missing whitespace problem in onUpgrade().

When is SQLiteOpenHelper onCreate() / onUpgrade() run?

Thank you it worked. Re-installing the app was the solution. — Roberto Chirila, Jun 14 '16 at 12:23

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

The error is in space inside of the column name RETYPED PASSWORD. Just add quoting for the name: 'RETYPED PASSWORD'. You can write about it here.

edited May 23 '17 at 12:32

Community

1
1

answered Jun 14 '16 at 12:03

Alex

617
4
15

score 0 · Answer 3 · answered Jun 14 '16 at 12:24

you made a silly mistake simply change the value of

private final static String col_4 = "RETYPED PASSWORD";

into

private final static String col_4 = "RETYPEDPASSWORD";

and copy this and replace your onCreate method

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,USERNAME TEXT,PASSWORD TEXT,RETYPEDPASSWORD TEXT,EMAIL TEXT,PASSPHRASE TEXT)");
}

and run it once again

Android Database Insert Error

3 Answers3