Android SQLiteDatabase inserting error

Question

some problems with my simple accountbook code.

I want to insert a row when I click the save button.

I checked my table created successfully.

save.setOnClickListener(new Button.OnClickListener(){

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub

            mDbOpenHelper.open();

            try{
            mDbOpenHelper.insertColumn(
                    rButton, 
                    mEditTexts[1].getText().toString().trim(), 
                    spinnerSelected, 
                    mEditTexts[0].getText().toString().trim());
            }catch(Exception e){
                Log.d("TAG","error");
            }

            mDbOpenHelper.close();
            mCursor.close();

        }

    });

insertColumn( )

// Insert DB
        public long insertColumn(String cashORcard, String amount, String category, String detail){
            mDB = mDBHelper.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(Database.CreateDB.CASH, cashORcard);
            values.put(Database.CreateDB.AMOUNT, amount);
            values.put(Database.CreateDB.CATEGORY, category);
            values.put(Database.CreateDB.DETAIL, detail);

            return mDB.insert(Database.CreateDB.TABLENAME, null, values);

        }

define of my database table

public class Database {

public static final class CreateDB implements BaseColumns{
    public static String firstCash="0";
    public static String firstAccount="0";
    public static String allMoney="0";
    public static final String CASH="cash";
    public static final String CATEGORY="category";
    public static final String DETAIL="detail";
    public static final String AMOUNT="0";
    public static final String ID="0";
    public static final String TABLENAME="Accountbook";
    public static final String CREATE=
            "CREATE TABLE "+TABLENAME
            +" ( _id integer primary key autoincrement, "
            +" CASH text not null , "
            +" AMOUNT text not null , "
            +" CATEGORY text not null , "
            +" DETAIL text , "
            +" firstCash text not null , "
            +" firstAccount text not null , "
            +" allMoney text not null );";  
    }
}

log

04-17 02:53:11.471: E/SQLiteLog(877): (1) near "0": syntax error
04-17 02:53:11.501: E/SQLiteDatabase(877): Error inserting cash=cash detail=asdf category=salary 0=567
04-17 02:53:11.501: E/SQLiteDatabase(877): android.database.sqlite.SQLiteException: near "0": syntax error (code 1): , while compiling: INSERT INTO Accountbook(cash,detail,category,0) VALUES (?,?,?,?)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at com.android.accountbook_edit.DbOpenHelper.insertColumn(DbOpenHelper.java:82)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at com.android.accountbook_edit.Income$3.onClick(Income.java:99)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.view.View.performClick(View.java:4204)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.view.View$PerformClick.run(View.java:17355)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.os.Handler.handleCallback(Handler.java:725)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.os.Handler.dispatchMessage(Handler.java:92)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.os.Looper.loop(Looper.java:137)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at android.app.ActivityThread.main(ActivityThread.java:5041)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at java.lang.reflect.Method.invokeNative(Native Method)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at java.lang.reflect.Method.invoke(Method.java:511)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
04-17 02:53:11.501: E/SQLiteDatabase(877):  at dalvik.system.NativeStart.main(Native Method)

need more code, plz let me know! Thanks !

Are you sure you want to insert column into your table ? Because the logic which you have written is for the inserting records into the table not for inserting column in database. — GrIsHu, Apr 17 '13 at 04:26
Check the definition of your statics AMOUNT and ID -- they should be `"amount"` and "`_id`", not `"0"`. — 323go, Apr 17 '13 at 04:28
Take a look to this answer...[this][1] [1]: http://stackoverflow.com/questions/8291673/how-to-add-new-column-to-android-sqlite-database Hope it help you.. — AndiM, Apr 17 '13 at 05:00

score 1 · Answer 1 · answered Apr 17 '13 at 04:58

First of all, you are inserting rows not columns.

Then, the error in your log is very clear

android.database.sqlite.SQLiteException: near "0": ... INSERT INTO Accountbook(cash,detail,category,0) VALUES (?,?,?,?) ...

You are trying to insert a value into an invalid column name: 0

score 0 · Answer 2 · edited Apr 17 '13 at 04:42

0

public static final String AMOUNT="0"

This should be

public static final String AMOUNT="AMOUNT"

The result of what you are trying to insert into a column named "0"

edited Apr 17 '13 at 04:42

Pragnani

20,075
6
49
74

answered Apr 17 '13 at 04:26

Gabe Sechan

90,003
9
87
127

1

May I know what is wrong with this answer? Why it was down voted? – Gunaseelan Apr 17 '13 at 05:27
I'm fairly sure its a person who dislikes me and votes me down when he sees my posts. Happens whenever we post in the same thread. – Gabe Sechan Apr 17 '13 at 05:33

score 0 · Answer 3 · answered Apr 17 '13 at 05:14

See error is 0=567 . Here your column name is in integer type. So this is error. Colomn name should be valid. So Just try the following

public static final String CREATE=
        "CREATE TABLE "+TABLENAME
        +" (_id integer primary key autoincrement, "
        + CASH +" text not null , "
        + AMOUNT+" text not null , "
        + CATEGORY+" text not null , "
        + DETAIL+" text , "
        + firstCash+" text not null , "
        + firstAccount+" text not null , "
        + allMoney+" text not null );";

Instead of this

public static final String CREATE=
        "CREATE TABLE "+TABLENAME
        +" ( _id integer primary key autoincrement, "
        +" CASH text not null , "
        +" AMOUNT text not null , "
        +" CATEGORY text not null , "
        +" DETAIL text , "
        +" firstCash text not null , "
        +" firstAccount text not null , "
        +" allMoney text not null );";

Also change your

firstCash,firstAccount,allMoney,AMOUNT varibales values to be valid string.

Like

public static String firstCash="firstCash";
public static String firstAccount="firstAccount";
public static String allMoney="allMoney";
public static final String AMOUNT="AMOUNT";

Why because column names should be valid.

I hope this will help you.

score 0 · Answer 4 · edited Apr 17 '13 at 05:34

0

You have passed value of mEditTexts[0].getText().toString().trim() to string detail. Check that the value of string detail and ID Database.CreateDB.DETAIL. If it's showing "0" then it shoud be "detail".

edited Apr 17 '13 at 05:34

von v.

16,868
4
60
84

answered Apr 17 '13 at 05:15

Dipak Sonnar

193
2
7

score 0 · Answer 5 · answered Apr 17 '13 at 06:03

Try to change your insertion query as below :

You need to also pass the other remaining columns firstCash,firstAccount,allMoney values also because in your Create Table query you have defined its property as NOT NULL.

public long insertColumn(String cashORcard, String amount, String category, String detail,String cashValue,String AccountVal,String money){
        mDB = mDBHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(Database.CreateDB.CASH, cashORcard);
        values.put(Database.CreateDB.AMOUNT, amount);
        values.put(Database.CreateDB.CATEGORY, category);
        values.put(Database.CreateDB.DETAIL, detail);
        values.put(Database.CreateDB.firstCash, cashValue);
        values.put(Database.CreateDB.firstAccount, AccountVal);
        values.put(Database.CreateDB.allMoney, money);
        return mDB.insert(Database.CreateDB.TABLENAME, null, values);
    }

Android SQLiteDatabase inserting error

5 Answers5