sqlite database error: sqlite returned: error code = 1, msg = no such column: ph_number

Question

i am developing application using sqlite databse having one table name comment that stores id , name, number and block.

now i get an error every time when i run the application that

sqlite returned: error code = 1, msg = no such column: ph_number

the code is below..

public class MySQLiteHelper extends SQLiteOpenHelper {

  public static final String TABLE_COMMENTS = "comments";
  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_COMMENT = "comment";
  public static final String COLUMN_NUMBER = "ph_number";
  public static final String COLUMN_BLOCK = "block";

  private static final String DATABASE_NAME = "commments.db";
  private static final int DATABASE_VERSION = 1;

  // Database creation sql statement
  private static final String DATABASE_CREATE = "create table "
      + TABLE_COMMENTS + "(" + COLUMN_ID
      + " integer primary key autoincrement, " + COLUMN_COMMENT
      + " TEXT," + COLUMN_NUMBER + " TEXT," 
      + COLUMN_BLOCK +" TEXT," +"UNIQUE(COLUMN_NUMBER) ON CONFLICT REPLACE"+ ");";

  public MySQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase database) {
    database.execSQL(DATABASE_CREATE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(MySQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
    onCreate(db);
  }

} 

this is second class code..

public class CommentsDataSource {

  // Database fields
  private SQLiteDatabase database;
  private MySQLiteHelper dbHelper;
  private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
      MySQLiteHelper.COLUMN_COMMENT,
      MySQLiteHelper.COLUMN_NUMBER,
      MySQLiteHelper.COLUMN_BLOCK};
  private String[] ColNum = {MySQLiteHelper.COLUMN_NUMBER, MySQLiteHelper.COLUMN_BLOCK };
  private String[] num = {MySQLiteHelper.COLUMN_NUMBER };

  public CommentsDataSource(Context context) {
    dbHelper = new MySQLiteHelper(context);
  }
  public CommentsDataSource() {
        //dbHelper = new MySQLiteHelper(context);
      }

  public void open() throws SQLException {
    database = dbHelper.getWritableDatabase();
  }

  public void close() {
    dbHelper.close();
  }

  public Comment createComment(String comment, String number, String b_value) {
    ContentValues values = new ContentValues();
    values.put(MySQLiteHelper.COLUMN_COMMENT, comment);
    values.put(MySQLiteHelper.COLUMN_NUMBER, number);
    values.put(MySQLiteHelper.COLUMN_BLOCK, b_value);
    long insertId = database.insert(MySQLiteHelper.TABLE_COMMENTS, null,
        values);
    Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
        allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
        null, null, null);
    cursor.moveToFirst();
    Comment newComment = cursorToComment(cursor);
    cursor.close();
    return newComment;
  }

  public void deleteComment(Comment comment) {
    long id = comment.getId();
    System.out.println("Comment deleted with id: " + id);
    database.delete(MySQLiteHelper.TABLE_COMMENTS, MySQLiteHelper.COLUMN_ID
        + " = " + id, null);
  }

  public List<Comment> getAllComments() {
        List<Comment> comments = new ArrayList<Comment>();
        try{
        Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
            allColumns, null, null, null, null, null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
          Comment comment = cursorToComment(cursor);
          comments.add(comment);
          cursor.moveToNext();
        }
        // Make sure to close the cursor
        cursor.close();
        }catch (Exception e) {
            // TODO: handle exception
        }

        return comments;
      }
// Updating single contact
    public Comment updateContact(Comment comment, String name ,String number, String b_value) {
        database = dbHelper.getWritableDatabase();
        long id = comment.getId();
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_COMMENT, name);
        values.put(MySQLiteHelper.COLUMN_NUMBER, number);
        values.put(MySQLiteHelper.COLUMN_BLOCK, b_value);
        System.out.println("updated: "+ id);
        // updating row
        long updateId = database.update(MySQLiteHelper.TABLE_COMMENTS, values, 
                MySQLiteHelper.COLUMN_ID + " = " + id, null);
        Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
                allColumns, MySQLiteHelper.COLUMN_ID + " = " + id, null,
                null, null, null);
            cursor.moveToFirst();
            Comment newComment = cursorToComment(cursor);
            cursor.close();
            System.out.println("updated: "+ newComment);
            return newComment;
    }

  private Comment cursorToComment(Cursor cursor) {
    Comment comment = new Comment();
    comment.setId(cursor.getLong(0));
    comment.setName(cursor.getString(1));
    comment.setNumber(cursor.getString(2));
    comment.setB_value(cursor.getString(3));
    return comment;
  }

 public String getValue(String phNum) {
        String value = null;
        String b_value =null ;
        Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
            ColNum, null, null, null, null, null);
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
          value = cursor.getString(0);
          if(PhoneNumberUtils.compare(phNum, value))
          {
              b_value = cursor.getString(1);
          }
          cursor.moveToNext();
        }
        // Make sure to close the cursor
        cursor.close();
        return b_value;
      }
 public boolean findNum(String phNum) {
        String value = null;
        boolean find =false ;
        Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
            num, null, null, null, null, null);
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
          value = cursor.getString(0);
          if(PhoneNumberUtils.compare(phNum, value))
          {
              find = true;
          }
          cursor.moveToNext();
        }
        // Make sure to close the cursor
        cursor.close();
        return find;
      }
} 

i have declared the column ph_number as.

 public static final String COLUMN_NUMBER = "ph_number";

but i still i am getting the error that sqlite returned: error code = 1, msg = no such column: ph_number

i have waste alot of time on this but cannot correct it. please help me ..

Answer 1

Try this

  // Database creation sql statement
  private static final String DATABASE_CREATE = "create table "
  + TABLE_COMMENTS + "(" + COLUMN_ID
  + " integer primary key autoincrement, " + COLUMN_COMMENT
  + " TEXT," + COLUMN_NUMBER + " TEXT," 
  + COLUMN_BLOCK +" TEXT," +"UNIQUE("+ COLUMN_NUMBER +") ON CONFLICT REPLACE"+ ");";

instead of

  // Database creation sql statement
  private static final String DATABASE_CREATE = "create table "
  + TABLE_COMMENTS + "(" + COLUMN_ID
  + " integer primary key autoincrement, " + COLUMN_COMMENT
  + " TEXT," + COLUMN_NUMBER + " TEXT," 
  + COLUMN_BLOCK +" TEXT," +"UNIQUE(COLUMN_NUMBER) ON CONFLICT REPLACE"+ ");";

Answer 2

I think the error sqlite returned: error code = 1, msg = no such column: ph_number is because,

In your database code the public static final String COLUMN_NUMBER = "ph_number"; contains the column name as ph_number.

And as per my understanding you stated that the database contains the one table name comment that stores id , name, number and block. so might be your column name is number.

so change this line like this public static final String COLUMN_NUMBER = "number" and check it out now. Because the column in table and program should be same. so may be you will get no such column error