Error while inserting data into database in android

Question

I'm getting error like this while inserting item details :

09-28 16:30:32.558 9471-9471/com.example.android.inventoryapp E/SQLiteDatabase: Error inserting quantity=1 name=Chocolate phone num=9112 price=5 supp name=Venu
    android.database.sqlite.SQLiteException: near "num": syntax error (code 1): , while compiling: INSERT INTO inventory(quantity,name,phone num,price,supp name) VALUES (?,?,?,?,?)

DATABASE HELPER is:

public void onCreate(SQLiteDatabase sqLiteDatabase) {
    String SQL_CREATE_PRODUCTS_TABLE =  "CREATE TABLE " + InventoryContract.InventoryEntry.TABLE_NAME + " ("
            + InventoryContract.InventoryEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + InventoryContract.InventoryEntry.COLUMN_Product_Name + " TEXT NOT NULL, "
            + InventoryContract.InventoryEntry.COLUMN_PRICE + " INTEGER NOT NULL, "
            + InventoryContract.InventoryEntry.COLUMN_Quantity + " INTEGER NOT NULL DEFAULT 0, "
            + InventoryContract.InventoryEntry.COLUMN_SUPPLIER_NAME + " TEXT NOT NULL, "
            + InventoryContract.InventoryEntry.COLUMN_SUPPLIER_Phno + " INTEGER NOT NULL);";
    // Execute the SQL statement
    //COLUMN_USERNAME +  " TEXT "
    sqLiteDatabase.execSQL(SQL_CREATE_PRODUCTS_TABLE);
}

Contract class is:

 public final static class InventoryEntry implements BaseColumns{
    public final static String TABLE_NAME="inventory";
    public final static String _ID = BaseColumns._ID;
    public final static String COLUMN_Product_Name="name";
    public final static String COLUMN_PRICE="price";
    public final static String COLUMN_Quantity="quantity";
    public final static String COLUMN_SUPPLIER_NAME="supp name";
    public final static String COLUMN_SUPPLIER_Phno="phone num";

}

logcat response is:

com.example.android.inventoryapp E/SQLiteLog: (1) near "num": syntax error
09-28 16:30:32.558 9471-9471/com.example.android.inventoryapp E/SQLiteDatabase: Error inserting quantity=1 name=Chocolate phone num=9112 price=5 supp name=Venu
        android.database.sqlite.SQLiteException: near "num": syntax error (code 1): , while compiling: INSERT INTO inventory(quantity,name,phone num,price,supp name) VALUES (?,?,?,?,?)
         at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
         at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
         at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
         at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
         at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
         at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
         at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
         at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
         at com.example.android.inventoryapp.CatalogActivity.insertpet(CatalogActivity.java:104)
         at com.example.android.inventoryapp.CatalogActivity.onOptionsItemSelected(CatalogActivity.java:118)
         at android.app.Activity.onMenuItemSelected(Activity.java:2885)
         at android.support.v4.app.FragmentActivity.onMenuItemSelected(FragmentActivity.java:407)
         at android.support.v7.app.AppCompatActivity.onMenuItemSelected(AppCompatActivity.java:195)`

Mureinik · Accepted Answer · 2018-09-28 11:21:13Z

3

Column names can't have spaces in them. If you want to use spaces, like you do with phone num, you should escape the names by surrounding them with double quotes ("):

INSERT INTO inventory("quantity", "name", "phone num", "price", "supp name") 
VALUES (?, ?, ?, ?, ?)

answered Sep 28, 2018 at 11:21

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

1 Comment

trivedh nani Over a year ago

Thanks. It works.. :) I changed my data base version to two and changed the column names...

Ambilpura Sunil Kumar · Accepted Answer · 2018-09-28 12:13:32Z

        Create a method and write the code to insert the data into table as below...

         public boolean insertPetition1(String cap_date, String cap_time, String image_str, String latitude, String longitude,
                                           String upload_status) {

                SQLiteDatabase db = getWritableDatabase();

                 try {
                    ContentValues contentValues = new ContentValues();
                    contentValues.put(COLUMN_IMAGE_PATH, image_str);
                    contentValues.put(COLUMN_CAPTURE_DATE, cap_date);
                    contentValues.put(COLUMN_CAPTURE_TIME, cap_time);
                    contentValues.put(COLUMN_LATITUDE, latitude);
                    contentValues.put(COLUMN_LONGITUDE, longitude);
                    contentValues.put(COLUMN_UPLOAD_STATUS, upload_status);

                    if (db.insert(MAIN_TABLE, null, contentValues) > 0) {
                        Log.e("Parameters", "Parameters:" + contentValues);
                        isInserted = true;
                    }
                } catch (Exception e) {
                    Log.v("TAG", e.toString());
                }
                return isInserted;
            }


    Call this method where ever you want to insert data in the tabel ....

    public void dbChanges() {
       DatabaseHelper1 databaseMain = new 
       DatabaseHelper1(OfflineAttendanceChildWithOutAuto.this);
       boolean status = false;

       status = databaseMain.insertPetition1(date, currentTime, encodedImage, latitude, longitude, "", "", "", awccode, "", "", mainaddress, usercode, "", "", "1");

                   databaseMain.close();
                }

It will work for sure try like this....

Collectives™ on Stack Overflow

Error while inserting data into database in android

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related