₦airaland Forum

Please can anyone help me on how to check if a column in sqlite table is empty before inserting data into that column.

I've tried this but it gives this error: java.lang.NullPointerException

// Database
SQLiteDatabase db = openOrCreateDatabase("NeamLS.db", MODE_PRIVATE, null);

try {
// Create table

db.execSQL("CREATE TABLE IF NOT EXISTS NeamLoggedin (Status);";

// Select

String count = "SELECT COUNT (*) FROM NeamLoggedin";
Cursor result = db.rawQuery(count,null);
result.moveToFirst();
int icount = result.getInt(0);
if(icount>0) {
String status = result.getString(0);
}
// insert
else {
db.execSQL("INSERT INTO NeamLoggedin Values('newuser');";
String status = result.getString(0);
}
Toast.makeText (getBaseContext(), "Data: " +status.toString(), Toast.LENGTH_SHORT).show();

db.close();
}
catch (Exception e) { Toast.makeText (getBaseContext(), "Error: " +e.toString(), Toast.LENGTH_LONG).show(); }

$

jinf, I think you have to confirm that your table was created successfully first.

seems as if your create table query is wrong, should be something like

create table if not exists namedloggedin(status varchar(32));

lordZOUGA:
jinf, I think you have to confirm that your table was created successfully first.

seems as if your create table query is wrong, should be something like

create table if not exists namedloggedin(status varchar(32));

Thanks. i did this now and it gives the same error

Jinf:
Thanks. i did this now and it gives the same error

Your select query is missing a semi-colon. should be

String count = "SELECT COUNT (*) FROM NeamLoggedin;"; 

lordZOUGA:
Your select query is missing a semi-colon. should be

String count = "SELECT COUNT (*) FROM NeamLoggedin;"; 

Thanks lordZOUGA i really appreciate your help. i've corrected that but still the error persists.

I think the error is from the if statement downwards. I just removed the if & else statement & i didn't get any error.

Jinf:
I think the error is from the if statement downwards. I just removed the if & else statement & i didn't get any error.

did your code work? were you able to insert an item into the database?

lordZOUGA:
did your code work? were you able to insert an item into the database?

I've fixed the error. The if(icount > 0) should hv been if(+icount > 0). Thanks for your help i couldn't have fixed it without your help. It's working now. But please i need help with adding WHERE to the statement. I want it to be WHERE status is loggedin

Jinf:
I've fixed the error. The if(icount > 0) should hv been if(+icount > 0).

I don't really understand how that fixed your problem. icount and +icount should be the same and if you did if(++icount > 0), it should always evaluate to true.

Thanks for your help i couldn't have fixed it without your help. It's working now. But please i need help with adding WHERE to the statement. I want it to be WHERE status is loggedin

select count(*) from namedloggedin where status = "loggedin"

visit here for more on sqlite: http://www.tutorialspoint.com/sqlite/

I also think you are doing sqlite databases on android in a "hacky" way, you should at least go through the doc and understand why things are the way they are before proceeding. http://developer.android.com/training/basics/data-storage/databases.html

Jinf:
Please can anyone help me on how to check if a column in sqlite table is empty before inserting data into that column.

-You need to declare the variable status outside the if/else statement. So that it can be within the scope of this line Toast.makeText (getBaseContext(), "Data: " +status.toString(), Toast.LENGTH_SHORT).show(); or else you'll keep getting a NullPointerException.
-Secondly, the String status = result.getString(0); in the else will return the same result as the in the if because you didn't re-execute db.rawQuery. For you to get the new result after the db.execSQL("INSERT INTO NeamLoggedin Values('newuser')" statement you need run the count sql script again. Otherwise, it won't give you any error but you won't get the desired result.

Happy coding.

lordZOUGA:
I don't really understand how that fixed your problem. icount and +icount should be the same and if you did if(++icount > 0), it should always evaluate to true.

select count(*) from namedloggedin where status = "loggedin"

visit here for more on sqlite: http://www.tutorialspoint.com/sqlite/

I also think you are doing sqlite databases on android in a "hacky" way, you should at least go through the doc and understand why things are the way they are before proceeding. http://developer.android.com/training/basics/data-storage/databases.html

Thanks bro. I'm actually new to android programming.

lordZOUGA:
did your code work? were you able to insert an item into the database?

Did you got my mail?

I know, it's too late to reply but it will help anyone looking for detailed info on sqlite select statement. The following sqlite topics will help better to understand select statement in sqlite.

SQLite Select Statement

To learn more about sqlite, refer the following

SQLite Tutorial