CODEWITHSUNDEEP
androidsqlite
Unice
Unice

Reputation: 1

Android SQLiteDatabase inserting error

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 !

Upvotes: 0

Views: 10367

Answers (5)

GrIsHu
GrIsHu

Reputation: 23638

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);
    }

Upvotes: 0

Dipak Sonnar
Dipak Sonnar

Reputation: 203

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".

Upvotes: 0

Gunaseelan
Gunaseelan

Reputation: 15525

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.

Upvotes: 0

Diego Torres Milano
Diego Torres Milano

Reputation: 69248

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

Upvotes: 1

Gabe Sechan
Gabe Sechan

Reputation: 93614

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"

Upvotes: 0

Related Questions