Dr.Easy Dr.Easy - 22 days ago 5
SQL Question

Sqlite cannot see the table..?

I have followed this example (https://www.youtube.com/watch?v=W0UHXTqlwMc) but i added number edit text with the name (2 editexts in the dialog) can anyone help me why I get error ??

LOG

11-18 15:26:07.260 23999-23999/com.example.mike.phonebook3 E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.mike.phonebook3, PID: 23999
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.mike.phonebook3/com.example.mike.phonebook3.MainActivity}: android.database.sqlite.SQLiteException: no such table: hh_TB (code 1): , while compiling: SELECT id, name, number FROM hh_TB
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2184)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:801)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:617)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: no such table: hh_TB (code 1): , while compiling: SELECT id, name, number FROM hh_TB
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
at com.example.mike.phonebook3.mDataBase.DBAdapter.retrieve(DBAdapter.java:60)
at com.example.mike.phonebook3.MainActivity.getSpacecrafts(MainActivity.java:123)
at com.example.mike.phonebook3.MainActivity.onCreate(MainActivity.java:40)
at android.app.Activity.performCreate(Activity.java:5231)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2148)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233) 
at android.app.ActivityThread.access$800(ActivityThread.java:135) 
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196) 
at android.os.Handler.dispatchMessage(Handler.java:102) 
at android.os.Looper.loop(Looper.java:136) 
at android.app.ActivityThread.main(ActivityThread.java:5001) 
at java.lang.reflect.Method.invokeNative(Native Method) 
at java.lang.reflect.Method.invoke(Method.java:515) 
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:801) 
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:617) 
at dalvik.system.NativeStart.main(Native Method) 


MainActivity

ListView lv;
EditText nameEditText,numberEditText;
Button save,retrive;
ArrayList<Spacecraft>spacecrafts=new ArrayList<>();
CustomAdapter adapter;
Boolean forUpdate=true;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
setSupportActionBar(toolbar);

lv=(ListView)findViewById(R.id.lv);
adapter=new CustomAdapter(this,spacecrafts);
this.getSpacecrafts();
// lv.setAdapter(adapter);


FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
fab.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
displayDialog(false);
}
});
}

private void displayDialog (Boolean forUpdate)
{
Dialog d=new Dialog(this);
d.setTitle("SQLite DATA");
d.setContentView(R.layout.dialog_layout);

nameEditText= (EditText) d.findViewById(R.id.nameEditTxt);
numberEditText=(EditText)d.findViewById(R.id.numberEditTxt);
save=(Button)d.findViewById(R.id.saveBtn);
retrive=(Button)d.findViewById(R.id.retriveBtn);


if(!forUpdate) {
save.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
save(nameEditText.getText().toString(),numberEditText.getText().toString());

}
});
retrive.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
getSpacecrafts();
}
});
} else {

// set selected text
nameEditText.setText(adapter.getSelectedItemID());
numberEditText.setText(adapter.getSelectedItemID());
save.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
update(nameEditText.getText().toString(),numberEditText.getText().toString());
}
});
retrive.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
getSpacecrafts();
}

});
}


d.show();
}


//SAVE
private void save(String name , String number){

DBAdapter db= new DBAdapter(this);
db.openDB();
boolean saved=db.add(name,number);

if(saved){
nameEditText.setText("");
getSpacecrafts();
}else {
Toast.makeText(this,"Unable to save",Toast.LENGTH_SHORT).show();
}
}
//Retrive or GetSpaceCrafts
private void getSpacecrafts(){
spacecrafts.clear();
DBAdapter db= new DBAdapter(this);
db.openDB();
Cursor c = db.retrieve();
Spacecraft spacecraft=null;


while (c.moveToNext()){
int id =c.getInt(0);
String name=c.getString(1);
String number=c.getString(1);

spacecraft=new Spacecraft();
spacecraft.setId(id);
spacecraft.setName(name);
spacecraft.setNumber(number);


spacecrafts.add(spacecraft);
}
db.closeDB();
lv.setAdapter(adapter);
}

//Update or Edit
private void update(String newName , String newNum)
{
//Get Id of SpaceCraft
int id=adapter.getSelectedItemID();


//Update in DB
DBAdapter db = new DBAdapter(this);
db.openDB();
boolean updated=db.update(newName,newNum,id);
db.closeDB();


if(updated){
nameEditText.setText(newName);
numberEditText.setText(newNum);
getSpacecrafts();
}else {
Toast.makeText(this,"unable to update",Toast.LENGTH_SHORT).show();
}
}
private void delete()
{
//Get id
int id = adapter.getSelectedItemID();

//delete from db
DBAdapter db=new DBAdapter(this);
db.openDB();
boolean deleted = db.delete(id);
db.closeDB();

if(deleted)
{
getSpacecrafts();
}else {
Toast.makeText(this,"unable to delete",Toast.LENGTH_SHORT).show();

}
}

@Override
public boolean onContextItemSelected(MenuItem item) {
CharSequence title = item.getTitle();
if(title=="New")
{
displayDialog(!forUpdate);

}else if (title=="Edit")
{
displayDialog(forUpdate);
}else if (title=="Delete"){
delete();
}
return super.onContextItemSelected(item);
}


Constants

static final String ROW_ID="id";
static final String NAME="name";
static final String NUMBER="number";


//properties
static final String DB_NAME="hh_DB";
static final String TB_NAME="hh_TB";
static final int DB_VERSION=1;

//creating tb
static final String CREATE_TB="CREATE TABLE hh_TB(id INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "name TEXT NOT NULL" + "number TEXT NOT NULL);";



//DROP TB
static final String DROP_TB="DROP TABLE IF EXISTS " +TB_NAME;


}


DBHELPER

public class DBHelper extends SQLiteOpenHelper{
public DBHelper(Context context) {
super(context, Constants.DB_NAME, null, Constants.DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL(Constants.CREATE_TB);
} catch (SQLException e) {
e.printStackTrace();
}
}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
db.execSQL(Constants.DROP_TB);
onCreate(db);
}
}


dbadapter

Context c;
SQLiteDatabase db;
DBHelper helper;

public DBAdapter(Context c) {
this.c = c;
helper = new DBHelper(c);
}


public void openDB() {
try {
db = helper.getWritableDatabase();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void closeDB() {
try {
helper.close();
} catch (SQLException e) {
e.printStackTrace();
}
}


//save
public boolean add(String name, String number) {
try {
ContentValues cv = new ContentValues();
cv.put(Constants.NAME, name);
cv.put(Constants.NUMBER, number);
long result = db.insert(Constants.TB_NAME, Constants.ROW_ID, cv);
if (result > 0) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}

//Select
public Cursor retrieve() {
String[] columns = {Constants.ROW_ID, Constants.NAME, Constants.NUMBER};
Cursor c = db.query(Constants.TB_NAME, columns, null, null, null, null, null);
return c;
}

//Update/edit
public boolean update(String newName, String newNumber, int id) {
try {
int result = db.delete(Constants.TB_NAME, Constants.ROW_ID + " =?", new String[]{String.valueOf(id)});
if (result > 0) {
return true;
}
} catch (SQLException e) {

}
return false;

}

//Delete///Reomve

public boolean delete(int id) {
try {
int result = db.delete(Constants.TB_NAME, Constants.ROW_ID + " =?", new String[]{String.valueOf(id)});
if (result > 0) {
return true;
}
} catch (SQLException e) {

}
return false;

}
}


I can provide u with more classes if you want i cant lunch the app

Answer

You have a syntax error in your SQL:

static final String CREATE_TB="CREATE TABLE hh_TB(id INTEGER PRIMARY KEY AUTOINCREMENT,"
    + "name TEXT NOT NULL" + "number TEXT NOT NULL);";

missing , between name and number columns.

  1. Add the missing ,.
  2. Remove the catch block. onCreate() must throw if there's a problem and not hide them.
  3. Uninstall your app to make onCreate() run again. When is SQLiteOpenHelper onCreate() / onUpgrade() run?