Android: Database handler SQLiteException

Question

Hello all i have this method in the database handler class and what this class do is to return the ID of the product from the product table. However, i am receiving this sqliteexception which i do not know why. Please advice thank you.

    private static final String TABLE_PRODUCT = "product"
    private static final String KEY_PRODUCTNAME = "productname";

public String getProductId(String productName) {
   String selectQuery = "SELECT productid FROM " + TABLE_PRODUCT+ " WHERE " +KEY_PRODUCTNAME +" = " + productName;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);
    String productid = cursor.toString();
    cursor.close();
    db.close();

    return productid;
}

Errors:

E/AndroidRuntime(1884): FATAL EXCEPTION: main
E/AndroidRuntime(1884): android.database.sqlite.SQLiteException: unrecognized token: "Bluedress34.50" (code 1): , while compiling: SELECT productid FROM product WHERE productname = "Bluedress34.50"

my activity class:

add.setOnClickListener(new View.OnClickListener() {

            public void onClick(View view) {

    DatabaseHandler db = new DatabaseHandler(getApplicationContext());

                    String productname = pname.getText().toString();
                    String productQTY = pqty.getText().toString();

                    String productnameid = db.getUProductId(productname);


                    JSONObject json = userFunction.addSales(productnameid, productQty);

}

}

my userfunction class:

public JSONObject addSales(productnameid, productQty){
    // Building Parameters

    List<NameValuePair> paramsfile = new ArrayList();

    paramsfile.add(new BasicNameValuePair("productnameid", productnameid));
    paramsfile.add(new BasicNameValuePair("productQty", productQty));


    JSONObject jsonfileName  =  jsonParser.getJSONFromUrl(addFileURL, paramsfile);

   Log.e("JSON", jsonfileName.toString());
    return jsonfileName;
}

1: How comes that you have KEY_PRODUCTNAME = "productname"; and your sql string produces SELECT productid FROM product WHERE uid = "Bluedress34.50"? So, I assume that you changed to KEY_PRODUCTNAME = "uid"; 2: Is this "Bluedress34.50" correct (is this the value in 1 field)? Because it really seems the concatenation of 2 field values... — Phantômaxx
– Phantômaxx, Commented Apr 3, 2014 at 9:49

Lucifer · Accepted Answer · 2014-04-03 08:47:57Z

5

Since product name is in String Format use ' in query, like below,

String selectQuery = "SELECT productid FROM " + TABLE_PRODUCT 
      + " WHERE " + KEY_PRODUCTNAME +" ='" + productName  +"'";

answered Apr 3, 2014 at 8:47

Lucifer

29.7k25 gold badges93 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user3306996 Over a year ago

thanks for your answer! i am now not facing the error. But i have another question to consult you. I am getting the productid however when i try to get the productid from the activity class, it is always 0. i have added the codes used in my activity above.

Lucifer Over a year ago

@user3306996, can you upload more code ? Because Using this code, you should get proper result. I think problem is somewhere else.

Lucifer Over a year ago

@user3306996, Cursor cursor = db.rawQuery(selectQuery, null); after this line write cursor.moveToFirst(); and see what happens.

Lucifer Over a year ago

Ok, then may be problem is here String productname = pname.getText().toString(); String productQTY = pname.getText().toString(); why these two variables using same EditText's value ? Should they use different EditText's value ?

user3306996 Over a year ago

my mistake!! i have corrected it but still the value obtained is 0

|

Phantômaxx · Accepted Answer · 2014-04-03 08:58:25Z

4

I'd rather write it so:

String selectQuery =
    "SELECT productid FROM " + TABLE_PRODUCT+ " WHERE " + KEY_PRODUCTNAME + " =  ?";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, new String[]{productName});

edited Apr 3, 2014 at 8:58

answered Apr 3, 2014 at 8:52

Phantômaxx

38.1k21 gold badges88 silver badges122 bronze badges

1 Comment

laalto Over a year ago

This is good, just fix the string array constructor syntax new String[] { productName }

GrIsHu · Accepted Answer · 2014-04-03 08:50:07Z

1

Any string value in database transactions should be accessed in ' single quote only.

Just write your product value in single quote ' as below: " = '" + productName +"'";

Query:

"SELECT productid FROM " + TABLE_PRODUCT+ " WHERE " +KEY_PRODUCTNAME +" = '" + productName +"'";

answered Apr 3, 2014 at 8:50

GrIsHu

23.6k9 gold badges62 silver badges96 bronze badges

Comments

Sagar Maiyad · Accepted Answer · 2014-04-03 08:54:39Z

1

it just a simple mistake. you forgot to add single qoute like below:

String selectQuery = "SELECT productid FROM " + TABLE_PRODUCT+ " WHERE " +KEY_PRODUCTNAME +" = '" + productName +"'";

answered Apr 3, 2014 at 8:54

Sagar Maiyad

12.7k9 gold badges66 silver badges102 bronze badges

Collectives™ on Stack Overflow

Android: Database handler SQLiteException

4 Answers 4

8 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related