sqlite no such table
- by Graham B
can anyone please help. I've seen many people with the same problem and looked at all suggestions but still cannot get this to work. I have tried to unistall the application and install again, I have tried to change the version number and start again. I've debugged the code and it does go into the onCreate function, but when I go to make a select query it says the users table does not exist. Any help would greatly be appreciated. Thanks guys
DatabaseHandler Class
public class DatabaseHandler extends SQLiteOpenHelper
{
// Variables
protected static final int DATABASE_VERSION = 1;
protected static final String DATABASE_NAME = "MyUser.db";
// Constructor
public DatabaseHandler(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db)
{
// Create the Users table
// NOTE: I have the column variables saved above
String CREATE_USERS_TABLE = "CREATE TABLE IF NOT EXISTS Users("
+ KEY_PRIMARY_ID + " " + INTEGER + " " + PRIMARY_KEY + " " + AUTO_INCREMENT + " " + NOT_NULL + "," + USERS_KEY_EMAIL + " " + NVARCHAR+"(1000)" + " " + UNIQUE + " " + NOT_NULL + "," + USERS_KEY_PIN + " " + NVARCHAR+"(10)" + " " + NOT_NULL + ")";
db.execSQL(CREATE_USERS_TABLE);
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSQL("DROP TABLE IF EXISTS Users");
onCreate(db);
}
UserDataSource class
public class UserDataSource
{
private SQLiteDatabase db;
private DatabaseHandler dbHandler;
public UserDataSource(Context context)
{
dbHandler = new DatabaseHandler(context);
}
public void OpenWriteable() throws SQLException
{
db = dbHandler.getWritableDatabase();
}
public void Close()
{
dbHandler.close();
}
// Validate the user login with the username and password provided
public void ValidateLogin(String username, String pin) throws CustomException
{
Cursor cursor = db.rawQuery( "select * from Users where " + DatabaseHandler.USERS_KEY_EMAIL + " = '" + username + "'" + " and " + DatabaseHandler.USERS_KEY_PIN + " = '" + pin + "'" , null);
........
}
Then in the activity class, I'm calling
UserDataSource uds = new UserDataSource (this);
uds.OpenWriteable();
uds.ValidateLogin("name", "pin");
Any help would be great, thanks very much
Graham
The following is the attached log from the error report
11-23 17:47:46.414: I/SqliteDatabaseCpp(26717): sqlite returned: error code = 1, msg = no such table: Users, db=/data/data/prometric.myitemwriter/databases/MyUser.db
11-23 17:47:57.085: D/AndroidRuntime(26717): Shutting down VM
11-23 17:47:57.085: W/dalvikvm(26717): threadid=1: thread exiting with uncaught exception (group=0x40bec1f8)
11-23 17:47:57.171: D/dalvikvm(26717): GC_CONCURRENT freed 575K, 8% free 8649K/9351K, paused 2ms+6ms
11-23 17:47:57.179: E/AndroidRuntime(26717): FATAL EXCEPTION: main
11-23 17:47:57.179: E/AndroidRuntime(26717): java.lang.IllegalStateException: Could not execute method of the activity
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.view.View$1.onClick(View.java:3091)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.view.View.performClick(View.java:3558)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.view.View$PerformClick.run(View.java:14152)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.os.Handler.handleCallback(Handler.java:605)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.os.Handler.dispatchMessage(Handler.java:92)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.os.Looper.loop(Looper.java:137)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.app.ActivityThread.main(ActivityThread.java:4514)
11-23 17:47:57.179: E/AndroidRuntime(26717): at java.lang.reflect.Method.invokeNative(Native Method)
11-23 17:47:57.179: E/AndroidRuntime(26717): at java.lang.reflect.Method.invoke(Method.java:511)
11-23 17:47:57.179: E/AndroidRuntime(26717): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:790)
11-23 17:47:57.179: E/AndroidRuntime(26717): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:557)
11-23 17:47:57.179: E/AndroidRuntime(26717): at dalvik.system.NativeStart.main(Native Method)
11-23 17:47:57.179: E/AndroidRuntime(26717): Caused by: java.lang.reflect.InvocationTargetException
11-23 17:47:57.179: E/AndroidRuntime(26717): at java.lang.reflect.Method.invokeNative(Native Method)
11-23 17:47:57.179: E/AndroidRuntime(26717): at java.lang.reflect.Method.invoke(Method.java:511)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.view.View$1.onClick(View.java:3086)
11-23 17:47:57.179: E/AndroidRuntime(26717): ... 11 more
11-23 17:47:57.179: E/AndroidRuntime(26717): Caused by: android.database.sqlite.SQLiteException: no such table: Users: , while compiling: select * from Users where email = '' and pin = ''
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:68)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:127)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:94)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:53)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1685)
11-23 17:47:57.179: E/AndroidRuntime(26717): at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1659)
11-23 17:47:57.179: E/AndroidRuntime(26717): at projectname.database.UserDataSource.ValidateLogin(UserDataSource.java:73)
11-23 17:47:57.179: E/AndroidRuntime(26717): at projectname.LoginActivity.btn_login_Click(LoginActivity.java:47)
11-23 17:47:57.179: E/AndroidRuntime(26717): ... 14 more
