Onered Onered - 28 days ago 11
Android Question

Data entry into SQLite Database table not showing up

I'm trying to setup an SQLite database containing some info about food and its calorific value. However when I key in the info, and click on button to view, the table just showing nothing. The info that I key in just now is not showing up.
I can't get to see where the problem is. Need help to look on the code and let me know what's wrong in there.

Creating the database:

public class FormDatabase
{
public static final String KEY_ROWID = "_id";
public static final String KEY_FOOD = "food name";
public static final String KEY_CALORIE = "food_calories";

private static final String DATABASE_NAME = "Calories";
private static final String DATABASE_TABLE = "FoodTable";
private static final int DATABASE_VERSION = 1;

private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

private static class DbHelper extends SQLiteOpenHelper
{

public DbHelper(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
// TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
KEY_FOOD + " TEXT NOT NULL, " +
KEY_CALORIE + " TEXT NOT NULL);"
);
}

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

public FormDatabase(Context c){
ourContext = c;
}

public FormDatabase open() throws SQLException {
ourHelper = new DbHelper(ourContext);
ourDatabase = ourHelper.getWritableDatabase();
return this;
}

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

public long createEntry(String food, String calorie) {
// TODO Auto-generated method stub
ContentValues cv = new ContentValues();
cv.put(KEY_FOOD, food);
cv.put(KEY_CALORIE, calorie);
return ourDatabase.insert(DATABASE_TABLE, null, cv);
}

public String getData() {
// TODO Auto-generated method stub
String [] columns = new String[]{ KEY_ROWID, KEY_FOOD, KEY_CALORIE};
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null, null);
String result = "";

int iRow = c.getColumnIndex(KEY_ROWID);
int iFood = c.getColumnIndex(KEY_FOOD);
int iCalorie = c.getColumnIndex(KEY_CALORIE);

for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
result = result + c.getString(iRow) + " " + c.getString(iFood) + " " + c.getString(iCalorie) + "\n";
}
return result;
}
}


Update and view the database:

public class DatabaseMain extends Activity implements OnClickListener{

Button sqlUpdate, sqlView;
EditText sqlFood, sqlCalorie;

@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.database_main);
sqlUpdate = (Button) findViewById(R.id.bSQLUpdate);
sqlFood = (EditText) findViewById(R.id.etSQLFood);
sqlCalorie = (EditText) findViewById(R.id.etSQLCalorie);

sqlView = (Button) findViewById (R.id.bSQLopenView);
sqlView.setOnClickListener(this);
sqlUpdate.setOnClickListener(this);

}

public void onClick(View arg0)
{
// TODO Auto-generated method stub
switch (arg0.getId())
{
case R.id.bSQLUpdate:

boolean didItWork = true;
try
{
String food = sqlFood.getText().toString();
String calorie = sqlCalorie.getText().toString();

FormDatabase entry = new FormDatabase(DatabaseMain.this);
entry.open();
entry.createEntry(food, calorie);
entry.close();
}
catch (Exception e)
{
didItWork = false;
String error = e.toString();
Dialog d = new Dialog(this);
d.setTitle("This is an error!");
TextView tv = new TextView(this);
tv.setText(error);
d.setContentView(tv);
d.show();
}
finally
{
if (didItWork)
{
Dialog d = new Dialog(this);
d.setTitle("Notice");
TextView tv = new TextView(this);
tv.setText("Update success");
d.setContentView(tv);
d.show();
}
}
break;

case R.id.bSQLopenView:
Intent i = new Intent("com.example.setupdatabase.SQLVIEW");
startActivity(i);
break;
}
}
}


SQLiteView:

public class SQLView extends Activity
{
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.sqlview);
TextView tv = (TextView) findViewById(R.id.tvSQLinfo);
FormDatabase info = new FormDatabase(this);
info.open();
String data = info.getData();
info.close();
tv.setText(data);
}
}


the SQLView layout xml:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="wrap_content">

<TableLayout
android:id="@+id/tableLayout1"
android:layout_width="match_parent"
android:layout_height="match_parent">

<TableRow>
<TextView
android:text="Food"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_weight="1">
</TextView>

<TextView
android:text="Calories"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_weight="1">
</TextView>
</TableRow>
</TableLayout>

<TextView
android:text="get info from db"
android:id="@+id/tvSQLinfo"
android:layout_width="match_parent"
android:layout_height="match_parent"></TextView>

<EditText
android:id="@+id/editText1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="numberSigned" >

<requestFocus />
</EditText>

</LinearLayout>

Answer

First: You should be logging your exceptions so you'll get helpful error messages and stack trace in your logcat. It's also useful to include the stack trace in questions you post.

There some errors in the code you posted, for example:

public static final String KEY_FOOD = "food name";

You cannot have spaces in column names, it will lead to SQL syntax errors.

db.execSQL("DROP TABLE IF EXIST " + DATABASE_TABLE);

This is also invalid SQL. Should be EXISTS not EXIST.