How to insert a new row of table of sqlite in android?

Question

I am working on a attendance apps for teacher. I have created database and trying to insert a new row but I am getting errors.I am giving few necessary code below. Database creation is as:

String aquery="CREATE TABLE "+ATTENDANCE_TABLE_NAME+" ( COLUMN_DATE TEXT PRIMARY KEY );";
            db.execSQL(aquery);

And ALTER the table as:

String aquery="ALTER TABLE "+ATTENDANCE_TABLE_NAME+" ADD COLUMN '"+new_col_name+"' TEXT;";
db.execSQL(aquery);

now I am inserting a row a new row as:

ContentValues Values=new ContentValues();
        SQLiteDatabase db=getWritableDatabase();
        // finding all column
        Cursor dbCursor = db.query(ATTENDANCE_TABLE_NAME, null, null, null, null, null, null);
        String[] columnNames = dbCursor.getColumnNames();
        String zero="0";

        for (int i=0;i<columnNames.length;i++)
        {
            // saving values to each specific column
            if(i==0)
                Values.put("COLUMN_DATE", date );
            else
                Values.put(columnNames[i],"'"+zero+"'");
        }
            check = db.insert(ATTENDANCE_TABLE_NAME, null , Values);

But I am getting this error:

05-25 18:52:23.023 19480-19480/com.example.vikas.scannerproject E/SQLiteLog: (1) near "14202": syntax error
05-25 18:52:23.023 19480-19480/com.example.vikas.scannerproject E/SQLiteDatabase: Error inserting COLUMN_DATE=2017-05-25 14202='0'
android.database.sqlite.SQLiteException: near "14202": syntax error (code 1): , while compiling: INSERT INTO CSE_OS_2017_ATTENDANCE(COLUMN_DATE,14202) VALUES (?,?)
                                                                                  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                                  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
                                                                                  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
                                                                                  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.vikas.scannerproject.Index_DBHandler.takingAttendance(Index_DBHandler.java:148)
                                                                                  at com.example.vikas.scannerproject.ScanresultActivity.takeAttendance(ScanresultActivity.java:44)
                                                                                  at com.example.vikas.scannerproject.ScanresultActivity.onCreate(ScanresultActivity.java:32)
                                                                                  at android.app.Activity.performCreate(Activity.java:6303)
                                                                                  at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1108)
                                                                                  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2376)
                                                                                  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2483)
                                                                                  at android.app.ActivityThread.access$900(ActivityThread.java:153)
                                                                                  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1349)
                                                                                  at android.os.Handler.dispatchMessage(Handler.java:102)
                                                                                  at android.os.Looper.loop(Looper.java:148)
                                                                                  at android.app.ActivityThread.main(ActivityThread.java:5441)
                                                                                  at java.lang.reflect.Method.invoke(Native Method)
                                                                                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:738)
                                                                                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:628)

Please help me in insertion of new row.

`Values.put(columnNames[i],"'"+zero+"'");` leave the quotation marks, just put zero like Values.put(columnNames[i],zero); — Opiatefuchs, May 23 '17 at 13:51
@Opiatefuchs I have tried both ways but same error is coming. — user7534168, May 23 '17 at 13:53
your `aquery` is also not correct, leave the quotation marks and put a bracket at the end `String aquery="ALTER TABLE "+ATTENDANCE_TABLE_NAME+" ADD COLUMN "+new_col_name+" TEXT);";` — Opiatefuchs, May 23 '17 at 13:53
And uninstall your app, install again. Everytime you make some changes which affects columns (add or sub) , you can´t use the existing database anymore. Also, by adding a column to an existing table, you have to upgrade your database. see: https://stackoverflow.com/questions/8291673/how-to-add-new-column-to-android-sqlite-database — Opiatefuchs, May 23 '17 at 13:55
@Opiatefuchs I have checked but still syntax error (code 1) is coming — user7534168, May 23 '17 at 15:09
please update the error code. Have upgraded your database after adding a column? have you uninstalled your app? — Opiatefuchs, May 23 '17 at 16:44
if it´s the same error, then you haven´t removed the quotation marks.... — Opiatefuchs, May 23 '17 at 16:54

score 2 · Answer 1 · answered May 23 '17 at 18:48

2

Column Names in SqLite may not start with a number[0-9]. Change the column name to something like "A14202".

BTW Adding a column for each assignment may not be the best design. SqLite will only allow 2000 columns.

answered May 23 '17 at 18:48

fishjd

1,617
1
18
31

score 2 · Answer 2 · answered May 24 '17 at 01:18

2

Column names in sqlite should be start with the Alphabet letter{A-Z,a-z}. It should not start with numeric value. Try this:

String aquery="ALTER TABLE "+ATTENDANCE_TABLE_NAME+" ADD COLUMN '"+"A"+new_col_name+"' TEXT;";
db.execSQL(aquery);

answered May 24 '17 at 01:18

Enigma

179
2
12

How to insert a new row of table of sqlite in android?

2 Answers2