CODEWITHSUNDEEP
javaandroideclipsesqlite
vibhor vaish
vibhor vaish

Reputation: 163

android.database.sqlite.SQLiteException: no such table :

On running the application I am getting above error although i checked the sqlite file and it contains the watchlist table although the table has no entries .

DBAdapter.java

package hp.vamazon;

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

public class DBAdapter {

    private static final String TAG = "DBAdapter"; //used for logging database version changes

    // Field Names:
    public static final String bookID = "bookid";//==KEYROW_ID
    public static final String bookName = "bookname";//==KEY_TASK
    public static final String bookPrice = "bbp";//==KEY_DATE
    public static final String storeName = "storename";

    public static final String[] ALL_KEYS = new String[] {bookID,bookPrice,bookName,storeName};

    // Column Numbers for each Field Name:
    public static final int COL_ROWID = 0;
    public static final int COL_TASK = 1;
    public static final int COL_DATE = 2;
    public static final int COL_STORE = 3;

    // DataBase info:
    public static final String DATABASE_NAME = "bookstore";
    public static final String DATABASE_TABLE = "watchlist";
    public static final int DATABASE_VERSION = 2; // The version number must be incremented each time a change to DB structure occurs.

    //SQL statement to create database
    private static final String DATABASE_CREATE_SQL = 
            "CREATE TABLE " + DATABASE_TABLE 
            + " (" + bookID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + bookName + " TEXT NOT NULL, "
            + bookPrice + " TEXT, "
            + bookPrice + " TEXT "
            + ");"; 

    private final Context context;
    private DatabaseHelper myDBHelper;
    private SQLiteDatabase db;


    public DBAdapter(Context ctx) {
        this.context = ctx;
        myDBHelper = new DatabaseHelper(context);
    }

    // Open the database connection.
    public DBAdapter open() {
        db = myDBHelper.getWritableDatabase();
        return this;
    }

    // Close the database connection.
    public void close() {
        myDBHelper.close();
    }

    // Add a new set of values to be inserted into the database.
    public long insertRow(String bookname, String price,String id ,String store) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(bookID,id);   
        initialValues.put(bookPrice,price); 
        initialValues.put(bookName,bookname);
        initialValues.put(storeName,store);
        // Insert the data into the database.
        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    // Delete a row from the database, by rowId (primary key)
    public boolean deleteRow(long rowId) {
        String where = bookID + "=" + rowId;
        return db.delete(DATABASE_TABLE, where, null) != 0;
    }

    public void deleteAll() {
        Cursor c = getAllRows();
        long rowId = c.getColumnIndexOrThrow(bookID);
        if (c.moveToFirst()) {
            do {
                deleteRow(c.getLong((int) rowId));              
            } while (c.moveToNext());
        }
        c.close();
    }

    // Return all data in the database.
    public Cursor getAllRows() {
        String where = null;
        Cursor c =  db.query(true, DATABASE_TABLE, ALL_KEYS, where, null, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    // Get a specific row (by rowId)
    public Cursor getRow(long rowId) {
        String where = bookID + "=" + rowId;
        Cursor c =  db.query(true, DATABASE_TABLE, ALL_KEYS, 
                        where, null, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    // Change an existing row to be equal to new data.
    public boolean updateRow(long rowId, String task, String date) {
        String where = bookID + "=" + rowId;
        ContentValues newValues = new ContentValues();
        newValues.put(bookName, task);
        newValues.put(bookPrice, date);
        // Insert it into the database.
        return db.update(DATABASE_TABLE, newValues, where, null) != 0;
    }


    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase _db) {  


db.execSQL(DATABASE_CREATE_SQL);
        }

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


}

Watchlist.java

package hp.vamazon;

import java.sql.SQLException;  
import java.util.ArrayList;  
import java.util.List;  

import android.app.Activity;   
import android.database.Cursor;   
import android.os.Bundle;   
import android.support.v4.widget.SimpleCursorAdapter;   
import android.view.Menu;   
import android.view.MenuItem;   
import android.view.View;   
import android.widget.AdapterView;    
import android.widget.ListView;   

public class Watchlist extends Activity {


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

        openDb();
        populatListViewFromDB();
    }
    private void openDb() {
        myDb=new DBAdapter(this);
        myDb.open();        
    }

    private void populatListViewFromDB() {


        Cursor cursor=myDb.getAllRows();

        startManagingCursor(cursor);

        String []fromFieldNames=new String[]{DBAdapter.bookName,DBAdapter.bookPrice,DBAdapter.storeName};
        int []toViewIDs=new int[]{R.id.item_name,R.id.item_price,R.id.item_store};

        SimpleCursorAdapter myCursorAdapter=new SimpleCursorAdapter(this,R.layout.cartitem_view
                ,cursor,fromFieldNames,toViewIDs);


        ListView myList=(ListView)findViewById(R.id.listView1);
        myList.setAdapter(myCursorAdapter);

    }
    private void registerClickCallback()
    {
        ListView myList=(ListView)findViewById(R.id.listView1);
        myList.setOnItemClickListener(new AdapterView.OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
            Cursor cursor=myDb.getRow(id);
            if(cursor.moveToFirst())
            {
                myDb.deleteRow(id);
                populatListViewFromDB();
            }
            cursor.close(); 
            }
        });
    }

}

at hp.vamazon.DBAdapter.getAllRows(DBAdapter.java:93)
at hp.vamazon.Watchlist.populatListViewFromDB(Watchlist.java:37)

Upvotes: 0

Views: 2335

Answers (4)

Shree Krishna
Shree Krishna

Reputation: 8562

Your class which extends SQLiteOpenHelper should execute the sql command in onCreate method to create the table, like

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(DATABASE_CREATE_SQL);
}

Yes It's for sure you have created command as final String DATABASE_CREATE_SQL to create table But forgot to execute this so that table is not created. To make you notice your both onCreate and onUpgrade methods are empty.

See here how DatabaseHelper created by extending SQLiteOpenHelper.

Upvotes: 3

vibhor vaish
vibhor vaish

Reputation: 163

I solved the question myself although thanks for your efforts guys. The problem was I didn't define a new instance of SQLite database in every function which was causing the code to throw no database exception as we had no database as it is.

Upvotes: 0

NIkhar Bhavsar
NIkhar Bhavsar

Reputation: 102

You don't have any code in OnCreate and OnUpgrade method which are in DatabaseHelper class. First You need to execute execSQL command to Create table :

@Override
        public void onCreate(SQLiteDatabase _db) {    
_db.execSQL(DATABASE_CREATE_SQL);  
        }

And for OnUpgrade methode refer following code:

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

        String Query = "DROP TABLE IF EXISTS Your_Table_Name"
        _db.execSQL(Query);
        onCreate(_db);

                }

Upvotes: 0

Skizo-ozᴉʞS ツ
Skizo-ozᴉʞS ツ

Reputation: 20616

android.database.sqlite.SQLiteException: no such table

On your case this is ocurring because you've not created any table. On your DatabaseHelper you should include the onCreate().

From the docs :

Called when the database is created for the first time. This is where the creation of tables and the initial population of the tables should happen.

You should create the tables on there as follows : 

db.execSQL(DATABASE_CREATE_SQL); //It will create the table

Upvotes: 1

Related Questions