Cant insert Values in table on Android App(SQLite)

Question

I am facing a very strange issue and i really can't see what causes it. So i am trying to insert values in a table. Let me first tell you that the table exist(i have checked it Db Browser for SQLite). The issue made me to hardcode the insert query, but nothing changed. For saving space, i will paste only the necessary blocks of code. So let me show you blocks of the code. Inside my DB helper class

public class DBHelper extends SQLiteOpenHelper {

    public static final int DATABASE_VERSION = 3;
    public static final String DATABASE_NAME = "SemsWMS.db";

    public DBHelper(Context context) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }



    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createCompany);
        db.execSQL(createFinDoc);
        db.execSQL(createMtrLines);
        db.execSQL(createTrdr);
        db.execSQL(createTrdBranch);
        db.execSQL(createMtrl);
        db.execSQL(createMtrLot);
        db.execSQL(createMtrSubstitute);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("drop table if exists company");
        db.execSQL("drop table if exists findoc");
        db.execSQL("drop table if exists mtrlines");
        db.execSQL("drop table if exists trdr");
        db.execSQL("drop table if exists trdbranch");
        db.execSQL("drop table if exists mtrl");
        db.execSQL("drop table if exists mtrlot");
        db.execSQL("drop table if exists mtrsubstitute");

        db.execSQL(createCompany);
        db.execSQL(createFinDoc);
        db.execSQL(createMtrLines);
        db.execSQL(createTrdr);
        db.execSQL(createTrdBranch);
        db.execSQL(createMtrl);
        db.execSQL(createMtrLot);
        db.execSQL(createMtrSubstitute);

    }

public static class Company implements BaseColumns{
        public static final String tablename = "company";
        public static final String company = "company";
        public static final String site = "site";
        public static final String webuser = "webuser";
        public static final String password = "password";
        public static final String appid = "appid";
        public static final String webuserid = "webuserid";
        public static final String upddatetrdr = "upddatetrdr";
        public static final String upddatetrdbranch = "upddatetrdbranch";
        public static final String upddatemtrl = "upddatemtrl";
        public static final String upddatemtrlot = "upddatemtrlot";
        public static final String upddatemtrsubstitute = "upddatemtrsubstitute";
    }

public static final String createCompany = "create table " + Company.tablename + "("+Company.company+" integer primary key, "
            +Company.site+" text, "
            +Company.webuser+" text, "
            +Company.password+" text, "
            +Company.appid +" integer, "
            +Company.webuserid+" integer, "
            +Company.upddatetrdr+" numeric, "
            +Company.upddatetrdbranch+" numeric, "
            +Company.upddatemtrl+" numeric, "
            +Company.upddatemtrlot+" numeric, "
            +Company.upddatemtrsubstitute+" numeric)";

the hardcoded block inside DBHelper class

 public static  String insertCompany(){
        String sql = "insert into company(company,site,appid,webuser,password) values(2,rigas,9999,sems,1234)";



        return sql;
    } 

Method inside DBHelper class in order to execute queries in other activities

public void insertInDB(String query){

        SQLiteDatabase db = getWritableDatabase();
        db.execSQL(query);
    }

so in other activity i call insertInDB(insertCompany()); and i get this:

 android.database.sqlite.SQLiteException: no such column: rigas (code 1): , while compiling: insert into company(company,site,appid,webuser,password) values(2,rigas,9999,sems,1234)
    #################################################################
    Error Code : 1 (SQLITE_ERROR)
    Caused By : SQL(query) error or missing database.
        (no such column: rigas (code 1): , while compiling: insert into company(company,site,appid,webuser,password) values(2,rigas,9999,sems,1234))
    #################################################################
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1073)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:638)

It says column "rigas"....but no such column in my insert query. .."rigas" is a value for column "site".

I would be very grateful, if anyone could find something in the code.

Answer 1

In a valid insert statement all the string literals must be enclosed inside single quotes.
So your statement with the hardcoded values should be:

insert into company(company,site,appid,webuser,password) values(2,'rigas',9999,'sems',1234)

If you don't use the single quotes then rigas and sem are treated like column names and since they are not you get the error.

But the correct, safe and recommended way of inserting new rows in a table is with the insert() method, with ContentValues, like this:

public int insertInDB(String site, int appid, String webuser, String password) {
    SQLiteDatabase db = getWritableDatabase();
    ContentValues c = new ContentValues();
    c.put(Company.site, site);
    c.put(Company.appid, appid);
    c.put(Company.webuserid, webuser);
    c.put(Company.password, password);
    int result = db.insert(Company.tablename, null, c);
    db.close();
    return result;
}  

So you pass to insertInDB() 4 column values (no need to pass company since you defined it as integer primary key then it is AUTOINCREMENT).
You can examine the returned value of insertInDB(): if it is -1 then the insert failed. Anything else will be the new row's id (the column company) and means it succeeded.