Table not being created in Android studio

Question

I've been stuck on this problem for a while now, and I can't seem to figure out what's wrong. I'm trying to create a database table, insert values into the table, and check to see if the email already exists. At first, it was at least telling me that the values were being inserted, now, the app only stops.

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "People.db";
public static final String TABLE_NAME = "user";
public static final String COL1 = "email";
public static final String COL2 = "password";


public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1); 
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_NAME + "(" +
    COL1 + "TEXT PRIMARYKEY," + 
    COL2 + "TEXT)");
    }

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}
/*Inserting into database*/

  public boolean add(String email, String password) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();  
    to database
    contentValues.put(COL1, email);
    contentValues.put(COL2, password);
    long ins = db.insert(TABLE_NAME, null, contentValues);

    if (ins == -1) return false;
    else return true;
    }

    /*checking if email exist*/
    public Boolean chkemail(String email) {

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * from TABLE_NAME where email = ?", 
    new String[]{email});
    if (cursor.getCount() > 0) return false;
    else return true;

    }
  }

This is the SignUp activity that inserts and checks the information.

import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import com.powell.randomeats.MainActivity;
import com.powell.randomeats.R;

public class SignUp extends AppCompatActivity {

DatabaseHelper db;
EditText email, pass, pass1;
Button sign;
boolean optionsSwitch;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_sign_up);

    db = new DatabaseHelper(this);
    email = (EditText) findViewById(R.id.Email);
    pass = (EditText) findViewById(R.id.Password);
    pass1 = (EditText) findViewById(R.id.Confirm);
    sign = (Button) findViewById(R.id.Signup);


    sign.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            String s1 = email.getText().toString();
            String s2 = pass.getText().toString();
            String s3 = pass1.getText().toString();
            if (s1.equals("") || s2.equals("") || s3.equals("")) {
                Toast.makeText(getApplicationContext(), "Fields are empty", 
            Toast.LENGTH_SHORT).show();

            } else {
                if (s2.equals(s3)) {
                    Boolean chkemail = db.chkemail(s1);
                    if (chkemail == true) {
                        Boolean insert = db.add(s1, s2);
                        if (insert == true) {
                            Toast.makeText(getApplicationContext(), 
                            "Registered Succesfully", 
                            Toast.LENGTH_SHORT).show();
                            Log();
                            if (optionsSwitch == true) {
                                openLog();
                            }

                        }
                    } else {
                        Toast.makeText(getApplicationContext(), "Email 
                        Already exists,", Toast.LENGTH_SHORT).show();

                    }

                } else {
                    Toast.makeText(getApplicationContext(), "Passwords do 
                not match", Toast.LENGTH_SHORT).show();
                }
            }
        }
    });


 }

public void Log() {
    optionsSwitch = true;
}

public void openLog() {
    Intent intent = new Intent(this, MainActivity.class);
    startActivity(intent);
  }

}

Answer 1

Your issue(s)

I believe that the table is being created. However, not with the expected column names.

That is due to spaces being admitted the create statement resolves to :-

CREATE TABLE user (emailTEXT PRIMARYKEY, passwordTEXT);

As such the table will have columns emailTEXT rather than email and passwordText rather than password.

This will cause issues when attempt to use columns email and password as those columns do not exist.

Additional PRIMARYKEY is not a valid keyword so changing the create statement to

CREATE TABLE user (email TEXT PRIMARYKEY, password TEXT);

Will create the table with the correct columns BUT the email column would not reject duplicate values.

So you'd need to add a space between PRIMARY and KEY thus the create should be :-

CREATE TABLE user (email TEXT PRIMARY KEY, password TEXT);

Suggested Fix

Your code could be changed to :-

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_NAME + "(" +
    COL1 + " TEXT PRIMARY KEY," + 
    COL2 + " TEXT)");
    }

Note that onCreate will not be invoked unless the database is deleted or it is forced to be invoked, so you could do one of the following :-

1. Delete the App's data (deletes the database).
2. uninstall the App (deletes the database).
3. Increase the database version (4th parameter of the call to the super e.g. use super(context, DATABASE_NAME, null, 2); (1 changed to 2)) (causes the onUpgrade method to be invoked and thus the table is dropped and then onCreate is invoked.).

Answer 2

Change this line:

Cursor cursor = db.rawQuery("SELECT * from TABLE_NAME where email = ?", new String[]{email});

to

Cursor cursor = db.rawQuery("SELECT * from " + TABLE_NAME + " where email = ?", new String[]{email});

so that the name of the table in the sql statement is users and not TABLE_NAME.