CODEWITHSUNDEEP
androidsqlite
bMon
bMon

Reputation: 962

SQLite Exception no such column when trying to select

I have the following DB helper class:

public int studentExists(String studid) {
    Cursor dataCount = mDb.rawQuery("select count(*) from usertable where " + KEY_STUDID + "=" + studid, null);
    dataCount.moveToFirst();
    int count = dataCount.getInt(0);
    dataCount.close();
    return count;
}

I use this in my app to see if a student id has been previously entered.

This works fine when the student id's are ints (346742) but whenever I try to add an alpha-numeric id (PB3874) it force closes the app.

Error:

06-13 18:22:20.554: ERROR/AndroidRuntime(8088): android.database.sqlite.SQLiteException: no such column: pb3874: , while compiling: select count(*) from usertable where studid=pb3874

I don't think its a data type issue (because I use the text type):

    private static final String DATABASE_CREATE =
    "create table usertable (_id integer primary key autoincrement, "
    + "studid text not null);";

But I'm confused why the error is saying no such column: pb3874 as I'm trying to simply select that value from the studid column. And also why this works perfectly for any int value. Anyone have any problem solving advice?

Upvotes: 28

Views: 73505

Answers (5)

Deepali Maniyar
Deepali Maniyar

Reputation: 149

Error was in trigger being fired on database update

Upvotes: -1

saigopi.me
saigopi.me

Reputation: 14918

for STRING you should place that in between '' ('sasas')

select * from bio where email = 'sasas'

Upvotes: 0

p.campbell
p.campbell

Reputation: 100587

What's happening here is that SQLite thinks that 'pb3874' is actually a column name, rather than a string/text literal.

To specify that it's a text literal, you'll want to ensure your text value is wrapped in the appropriate single quotes:

To prevent SQL injection attacks, whenever you're taking input from the user, use a parameterized query:

("select count(*) from usertable where " + KEY_STUDID + "=?", studid);

Without parameterization (very much discouraged when taking user input):

("select count(*) from usertable where " + KEY_STUDID + "='" + studid + "'", null);

The reason your numeric values didn't produce this: SQLite converted those numeric literals for you.

Upvotes: 73

Matthieu
Matthieu

Reputation: 16407

I think you got the answer to what's wrong from Antlersoft or p.campbell, to format the query correctly, I would suggest, you do it like this :

mDb.rawQuery("select count(*) from usertable where " + KEY_STUDID + "=?", studid);

That should (1) solve your problem and (2) protect you from SQL injections

Also, I am not sure that 

dataCount.getInt(0);

is the best thing to do... you should probably use getColumnIndex to make sure you are getting the right data...

Upvotes: 2

antlersoft
antlersoft

Reputation: 14786

You need to quote 'pb3874' (single quotes) if you are including it in the SQL string.

Upvotes: 7

Related Questions