No such column error while inserting in SQLite Android Studio

Question

CODE:

//creating an sqlite query and setting column names 
along with data types.
String query = "CREATE TABLE tableMon (notes TEXT)";

//calling a exec sql method to execute sql query
db.execSQL(query);

//after adding....passing content values to the table
String insert = "INSERT INTO tableMon (notes) VALUES ( 
" + values + " )";
db.execSQL(insert);

ERROR: tableMon has no column named notes in "INSERT INTO tableMon (notes) VALUES ( notes=hello )"

I have tried adding and removing spaces near the column name and adding variables instead direct use of table name and column name. Though! didn't get any expected result.

Answer 1

Your issue is that the value being inserted is non-numeric and thus must be enclosed in single quotes. So:-

String insert = "INSERT INTO tableMon (notes) VALUES ('" + values + "' )";

However, the above code is susceptible to SQL Injection So it would be better to use:-

String insert = "INSERT INTO tableMon (notes) VALUES (?)";
db.execSQL(insert,new String[]{values};

In this case the value will be suitable enclosed when it is bound by SQLite binding.

However, another approach is to use the SQLiteDatabse insert method. This has the advantages that

• it builds the underlying SQL, and
• binds values,
• and returns the rowid of the inserted row or -1 if the row could not be inserted.

In your case you could use:-

ContentValues cv = new ContentValues();
cv.put("notes",values);
long result = db.insert("tableMon",null,cv);

Here is a working demo:-

public class MainActivity extends AppCompatActivity {
    SQLiteDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = SQLiteDatabase.openOrCreateDatabase(this.getDatabasePath("example.db").getPath(),null);
        db.execSQL("CREATE TABLE IF NOT EXISTS tableMon (notes TEXT)");
        String values = "notes=hello";
        db.execSQL("INSERT INTO tableMon (notes) VALUES('" + values + "')");
        db.execSQL("INSERT INTO tableMon (notes) VALUES(?)",new String[]{values});
        ContentValues cv = new ContentValues();
        cv.put("notes",values);
        db.insert("tableMon",null,cv);
    }
}

• Note the use of CREATE TABLE IF NOT EXISTS, which will circumvent the error if the table already exists.

The result, via Android Studio's App Inspection ,when installed and run (first time, subsequent runs would add another set of 3 rows):-

i.e. 3 rows have been inserted using each of the fixes.