0

This question has been answered many time but still unable to find my mistake. Am creating a database and a table to insert some value into SQLite database. but it shows SQLite Exception.

This is my code that i have tried.

Main activity:

when i click Register button it should create the database, table and then enter the data into table.

private static final int DATABASE_VERSION = 2;
private static final String DATABASE_NAME = "userDB";
private static final String TABLE_USER = "users";

private static final String KEY_ID = "id";
private static final String KEY_USERNAME = "username";
private static final String KEY_PASSWORD = "password";

db = new DataBaseHandler(RegisterActivity.this); // my database handler

    username = (EditText) findViewById(R.id.usernameRegister);
    password = (EditText) findViewById(R.id.passwordRegister);
    registerSave = (Button) findViewById(R.id.saveRegister);

    registerSave.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub

            usernameInput = username.getText().toString();
            passwordInput = password.getText().toString();

            //createDB();
            Log.d("Insert: ", "Inserting ..");
            db.addUsers(new Users(""+usernameInput,""+passwordInput));
        //  db.addUsers(new Users("password",passwordInput));

            Log.d("inserted", ""+db);

            Log.d("inputs", "" + usernameInput + ":" + passwordInput);

        }
    });

This is my Database Handler class

public DataBaseHandler(Context context) {

    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS "+TABLE_USER+" (" + KEY_ID
            + "INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_USERNAME + " TEXT,"
            + KEY_PASSWORD + " TEXT" + ")";
    db.execSQL(CREATE_USER_TABLE);
    Log.d("sqlite DB:","Created"+db.toString());

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS "+ TABLE_USER);
    onCreate(db);
}

// Adding new contact
void addUsers(Users users) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_USERNAME, users.getUsername()); // User Name
    values.put(KEY_PASSWORD, users.getPassword()); // Password

    // Inserting Row
    db.insert(TABLE_USER, null, values);
    db.close(); // Closing database connection
}

Users getUser(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_USER, new String[] { KEY_ID,
            KEY_USERNAME, KEY_PASSWORD }, KEY_ID + "=?",
            new String[] { String.valueOf(id) }, null, null, null, null);
    if (cursor != null)
        cursor.moveToFirst();

    Users users = new Users(Integer.parseInt(cursor.getString(0)),
            cursor.getString(1), cursor.getString(2));
    // return contact
    return users;
}

and this is userclass to get the user data.

public class Users {

int _id;
String _username;
String _password;

public Users() {

}

public Users(int id, String username, String password) {
    this._id = id;
    this._username = username;
    this._password = password;
}

public Users(String username, String password) {
    this._username = username;
    this._password = password;
}

public int getId() {
    return this._id;
}

public void setId(int id) {
    this._id = id;
}

public String getUsername() {
    return this._username;
}

public void setUsername(String username) {
    this._username = username;
}

public String getPassword() {
    return this._password;
}

public void setPassword(String password) {
    this._password = password;
}

}

am also posting the logcat of the output:

Logcat:

01-07 07:38:19.613: E/SQLiteDatabase(3467): Error inserting username=sagar password=asdfg
01-07 07:38:19.613: E/SQLiteDatabase(3467): android.database.sqlite.SQLiteException: no such table: users (code 1): , while compiling: INSERT INTO users(username,password) VALUES (?,?)
01-07 07:38:19.613: E/SQLiteDatabase(3467):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
`

Am struck. need some help.

12
  • value of TABLE_USER ? Commented Jan 7, 2016 at 12:49
  • problem is here String CREATE_USER_TABLE Commented Jan 7, 2016 at 12:49
  • @AnandSingh: i have updated my post Commented Jan 7, 2016 at 12:51
  • @IntelliJAmiya : can you explain please what to do? Commented Jan 7, 2016 at 12:51
  • try with database version=1 Commented Jan 7, 2016 at 12:55

3 Answers 3

2

You are suffering from WHITE SPACE in your CREATE TABLE IF NOT EXISTS section .

YOU CAN USE This

String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_USER + "("
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_USERNAME + " TEXT, " + KEY_PASSWORD + " TEXT )";

Uninstall & Run your app .Happy coding .

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

2 Comments

Thanks for help. its working. a whitespace was missing from the query.
@SagarPawar yes yes . Move ahead .
1

First of all, search your create table query for missing whitespaces:

String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS "+TABLE_USER+" (" + KEY_ID
        + "INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_USERNAME + " TEXT,"
        + KEY_PASSWORD + " TEXT" + ")";

Maybe you find a missing space between KEY_ID and INTEGER PRIMARY KEY AUTOINCREMENT,.... You will have to increase your database version to have public void onCreate(SQLiteDatabase db) called again.

You could log your database creation sql and try it in the console for yourself.

Comments

1

Try this query

String CREATE_USERS_TABLE= java.lang.String.format("CREATE TABLE IF NOT EXISTS %s (%s INTEGER PRIMARY KEY AUTOINCREMENT, %s TEXT, %s TEXT);", TABLE_USER,KEY_ID, KEY_USERNAME,KEY_PASSWORD);

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.