CODEWITHSUNDEEP
android
JProgrammer
JProgrammer

Reputation: 95

SQLite database insert error when inserting an item into it

I have a method for inserting an item into database but there is a problem when I use it


    private static final int DB_VERSION = 1 ;
    private static final String DB_NAME = "db-colors";
    private static final String TABLE_COLORS = "tbl-colors";

    private static final String CMD_CREATE_COLOR_TABLE =
            "CREATE TABLE IF NOT EXISTS '" + TABLE_COLORS +"' ('" +
                    Colors.KEY_COLORNAME + "' TEXT, '" +
                    Colors.KEY_COLORIMAGE + "' TEXT, '" +
                    Colors.KEY_COLORRGB + "' TEXT )";


    public ColorDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CMD_CREATE_COLOR_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_COLORS );
    }
    public void insertColor(Colors color){

        ContentValues values = new ContentValues();
        values.put(Colors.KEY_COLORNAME,color.getColorName());
        values.put(Colors.KEY_COLORIMAGE,color.getColorImage());
        values.put(Colors.KEY_COLORRGB,color.getColorRgb());
        SQLiteDatabase database = getWritableDatabase();
        database.insert(TABLE_COLORS,null,values);
        Log.i("database","Database add an item : " + color.getColorName());
        if (database.isOpen())
            database.close();
    }


    public class Colors {

    private String colorName ;
    private String colorImage ;
    private String colorRgb ;

    public static final String KEY_COLORNAME = "colorName";
    public static final String KEY_COLORIMAGE = "colorImage";
    public static final String KEY_COLORRGB = "colorRgb";

    public String getColorName() {
        return colorName;
    }

    public void setColorName(String colorName) {
        this.colorName = colorName;
    }

    public String getColorImage() {
        return colorImage;
    }

    public void setColorImage(String colorImage) {
        this.colorImage = colorImage;
    }

    public String getColorRgb() {
        return colorRgb;
    }

    public void setColorRgb(String colorRgb) {
        this.colorRgb = colorRgb;
    }

    @Override
    public String toString() {

        return colorName;
    }
}

E/SQLiteLog: (1) near "-": syntax error E/SQLiteDatabase: Error inserting colorName=carrot colorImage=carrot.png colorRgb=#e67e22 android.database.sqlite.SQLiteException: near "-": syntax error (code 1 SQLITE_ERROR): , while compiling: INSERT INTO tbl-colors(colorName,colorImage,colorRgb) VALUES (?,?,?) at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1562) at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1433) at com.mycompany.myapplication.ColorDBHelper.insertColor(ColorDBHelper.java:42) at com.mycompany.myapplication.MainActivity.importJson(MainActivity.java:42) at com.mycompany.myapplication.MainActivity.access$000(MainActivity.java:16) at com.mycompany.myapplication.MainActivity$1.onClick(MainActivity.java:36) at android.view.View.performClick(View.java:6597) at android.view.View.performClickInternal(View.java:6574) at android.view.View.access$3100(View.java:778) at android.view.View$PerformClick.run(View.java:25885) at android.os.Handler.handleCallback(Handler.java:873) at android.os.Handler.dispatchMessage(Handler.java:99) at android.os.Looper.loop(Looper.java:193) at android.app.ActivityThread.main(ActivityThread.java:6669) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)

Upvotes: 0

Views: 607

Answers (2)

Muhammad Usman Butt
Muhammad Usman Butt

Reputation: 545

try this query 

private static final String CMD_CREATE_COLOR_TABLE =
        "CREATE TABLE IF NOT EXISTS " + TABLE_COLORS +" ( " +
                Colors.KEY_COLORNAME + "  TEXT, " +
                Colors.KEY_COLORIMAGE + " TEXT, " +
                Colors.KEY_COLORRGB + " TEXT )";

Upvotes: 0

Mohd Faizan
Mohd Faizan

Reputation: 110

You need to change TABLE_COLORS = "tbl-colors"; with TABLE_COLORS = "tbl_colors";

becuase "-" is will be treated as minus in sqlite so in place of "-" you need to use "_"

Table name validation

"xyz123" - valid
"123xyz" - not valid
"xyz_123" - valid
"_123xyz" - valid
"xyz-xyz" - not valid (looks like an expression)
"xyz.xyz" - not valid (looks like a database.table notation)

Upvotes: 1

Related Questions