CODEWITHSUNDEEP
androiddatabaseinsert
Scamparelli
Scamparelli

Reputation: 744

Android INSERT statement problems

I am getting syntax error when trying to insert data into my database when it is created.

I am using the following code:

//DatabaseHelper class
private static class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_INSPECTION_TABLE);
        db.execSQL(CREATE_AREA_TABLE);
        db.execSQL(CREATE_RUN_TABLE);
        db.execSQL(CREATE_LOCATION_TABLE);
        db.execSQL(CREATE_INSPECTORS_TABLE);
        db.execSQL(CREATE_ISSUE_LOCATION_TABLE);
        db.execSQL(CREATE_ISSUE_TYPE_TABLE);
        db.execSQL(CREATE_RACKING_SYSTEM_TABLE);
        db.execSQL(CREATE_COMPONENT_TABLE);
        db.execSQL(CREATE_RISK_TABLE);
        db.execSQL(CREATE_ACTION_TABLE);
        db.execSQL(CREATE_MANUFACTURER_TABLE);

        db.rawQuery("INSERT INTO " + INSPECTORS_TABLE + "(" + INSPECTOR_NAME + ") " + 
                "SELECT 'Joe Bloggs' AS " + INSPECTOR_NAME +
                " UNION SELECT 'John Major' " +
                " UNION SELECT 'Dan the man'"
                , null);

        db.rawQuery("INSERT INTO " + ACTION_TABLE + "(" + ACTION_REQUIRED + ") " + 
                "SELECT 'Red Risk' AS " + ACTION_REQUIRED +
                " UNION SELECT 'Amber Risk' " +
                " UNION SELECT 'Green Risk'"
                , null);

Edited again following RedFliter's great advice. I'm sure the query is now right, but although I don't get any errors no records get inputed into the tables (deleted the database each time using the DDMS).

Am I putting this code in the right place (i.e. onCreate)?

Not sure where I'm going wrong..

Many thanks.

Upvotes: 1

Views: 404

Answers (3)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171579

You need to add single quotes around your strings:

db.rawQuery("INSERT INTO " + INSPECTIONS_TABLE + " (" + INSPECTOR_NAME + ")          VALUES " + "('Joe Blogs', 'John Smith', 'Franky Hollywood')", null);

This approach is subject to SQL injections. You will need to guard against this.

Update:

For multiple row inserts, you need to do:

db.rawQuery("INSERT INTO " + INSPECTIONS_TABLE + "(" + INSPECTOR_NAME + ") " + "select 'Joe Bloggs' as " + INSPECTOR_NAME + " UNION ALL select 'John Major' UNION ALL select 'Dan the man'", null);

Upvotes: 3

Martin Vandzura
Martin Vandzura

Reputation: 3127

Just put string values into quotes 

db.rawQuery("INSERT INTO " + 
        INSPECTIONS_TABLE + "(" + INSPECTOR_NAME + ") 
        VALUES " + "('Joe Blogs', 'John Smith', 'Franky Hollywood')", null);

Upvotes: -1

Arpit Patel
Arpit Patel

Reputation: 1571

Use like the following you can defenetly insert the date into the database...

 SQLiteDatabase db = this.getWritableDatabase();
 ContentValues values = new ContentValues();
 values.put(POST_ID,"Your ID here");
 values.put(POSTER_ID,"Poster id " );
 values.put(POSTER_NAME, msg.poster_name);
 values.put(POSTER_IMAGE_PATH,msg.poster_image_path==null ||msg.poster_image_path.length()==0?"":msg.poster_image_path);
 values.put(PLAYER_TYPE, msg.player_type);
 values.put(POST_TEXT, msg.post_text);
 db.insert(TABLE_NAME, null, values);
 db.close();

Upvotes: -1

Related Questions