Reputation: 478
I am creating an app to record assignments for different classes. Each class has it's own unique ID so the assignments listed for each class don't overlap into other classes. Here is a method I made to find the rowid for a certain class.
public int getIdFromClassName(String className){
String query = "SELECT rowid FROM " + CLASSES_TABLE_NAME + " WHERE " + CLASSES_COLUMN_NAME + " = '" + className + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery(query, null);
return res.getColumnIndex("id");
}
However, this always returns a value of -1.
Any thoughts on what to change to return the proper rowid value?
EDIT:
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(
"create table " + CLASSES_TABLE_NAME + " " +
"(id integer primary key, " + CLASSES_COLUMN_NAME + " text)"
);
db.execSQL(
"create table " + ASSIGNMENTS_TABLE_NAME + " " +
"(id integer primary key, " + ASSIGNMENTS_COLUMN_NAME + " text, " + ASSIGNMENTS_COLUMN_TOTAL_POINTS
+ " INTEGER, " + ASSIGNMENTS_COLUMN_CLASS_ID + " INTEGER)");
}
Upvotes: 1
Views: 11511
Reputation: 1021
Try this query below to create a new column name rowID
:
Cursor cursor= db.rawQuery("SELECT *,"+CLASSES_TABLE_NAME +".rowid AS rowID"+" FROM "+CLASSES_TABLE_NAME , null);
And after this query you can fetch the real rowid
from the rowID
column :
while (cursor.moveToNext()) {
long chatRow=cursor.getLong(
cursor.getColumnIndexOrThrow("rowID"));
}
Upvotes: 2
Reputation: 1386
public int getIdFromClassName(String className) {
String query = "SELECT rowid FROM " + CLASSES_TABLE_NAME + " WHERE "
+ CLASSES_COLUMN_NAME + " = '" + className + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery(query, null);
if (res != null) {
if (res.moveToFirst()) {
do {
return Integer.parseInt(res.getString(res.getColumnIndex("id"))); // if your column name is rowid then replace id with rowid
} while (res.moveToNext());
}
} else {
Toast.makeText(context, "cursor is null", Toast.LENGTH_LONG).show();
}
}
Check your Cursor if its null check your Log that your table is created successfully and if its not null make sure your table has column id
in it.
Upvotes: 0
Reputation: 180250
The column returned by your query is called rowid
, so you will not find a column called id
.
Ensure that you use the same column name in the query and in the call to getColumnIndex
.
And the index of this column is always zero; you also need to read the actual value from the column:
int colIndex = res.getColumnIndexOrThrow("rowid"); // = 0
if (res.moveToFirst()) {
return res.getInt(colIndex);
} else {
// not found
}
However, Android has an helper function that makes it much easier to read a single value:
public int getIdFromClassName(String className){
String query = "SELECT rowid" +
" FROM " + CLASSES_TABLE_NAME +
" WHERE " + CLASSES_COLUMN_NAME + " = ?;";
SQLiteDatabase db = this.getReadableDatabase();
return DatabaseUtils.longForQuery(db, query, new String[]{ className });
}
Upvotes: 1
Reputation: 539
You can try this:
public int getIdFromClassName(String className){
String query = "SELECT id FROM " + CLASSES_TABLE_NAME + " WHERE " + CLASSES_COLUMN_NAME + " = '" + className + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery(query, null);
int id=-1;
If(res!=null&&res.moveToFirst())
id=res.getInt(res.getColumnIndex("id"));
return id;
}
Upvotes: 0
Reputation: 8992
res.getColumnIndex("id") is going to get you the column index of the column with the name "id". and since you are getting -1, it cant find it.. are you sure your id column is not "_id"?
To get this to work you should do something like this..
res.getLong(res.getColumnIndex("_id"));
(I would recommend you use getLong() rather than getInt() because SQLite database ID's can get larger than int's).
Upvotes: 0