Unable to add data to SQLite table in android

Question

I'm working on a simple SQLite CRUD application and I want to add data to manually created database in SQLite. But when I'm adding data, the app stops and shows the below error message

E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.example.sqlitecrudexample, PID: 14124
    android.database.sqlite.SQLiteConstraintException: NOT NULL constraint failed: employees.id (code 1299 SQLITE_CONSTRAINT_NOTNULL)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:890)
        at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:756)
        at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:66)
        at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1920)
        at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1897)
        at com.example.sqlitecrudexample.MainActivity.addEmployee(MainActivity.kt:70)
        at com.example.sqlitecrudexample.MainActivity.access$addEmployee(MainActivity.kt:13)
        at com.example.sqlitecrudexample.MainActivity$onCreate$1.onClick(MainActivity.kt:30)
        at android.view.View.performClick(View.java:7448)
        at com.google.android.material.button.MaterialButton.performClick(MaterialButton.java:992)
        at android.view.View.performClickInternal(View.java:7425)
        at android.view.View.access$3600(View.java:810)
        at android.view.View$PerformClick.run(View.java:28305)
        at android.os.Handler.handleCallback(Handler.java:938)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:223)
        at android.app.ActivityThread.main(ActivityThread.java:7656)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:947)
I/Process: Sending signal. PID: 14124 SIG: 9

Here is my code for creating and adding data to the table

private fun addEmployee(){
        var name:String = editTextName.text.toString().trim()
        var salary:String = editTextSalary.text.toString().trim()
        var dept = spinnerDepartment.selectedItem.toString()

        var calendar = Calendar.getInstance()
        var simpleDateFormat = SimpleDateFormat("yyyy-mm-dd hh:mm:ss")
        var joiningDate = simpleDateFormat.format(calendar.time)

        if(inputsAreCorrect(name,salary)){
            val insertSQL = """
                INSERT INTO employees 
                (name, department, joiningdate, salary)
                VALUES 
                (?, ?, ?, ?);
                """.trimIndent()
            mDatabase.execSQL(insertSQL, arrayOf(name, dept, joiningDate, salary))
            Toast.makeText(this,"Employee Added Successfully",Toast.LENGTH_SHORT).show()
        }
    }

    private fun createEmployeeTable() {
        mDatabase.execSQL(
            """CREATE TABLE IF NOT EXISTS employees (
                    id int PRIMARY KEY AUTOINCREMENT NOT NULL,
                    name varchar(200) NOT NULL,
                    department varchar(200) NOT NULL,
                    joiningdate datetime NOT NULL,
                    salary double NOT NULL
                );"""
        )
    }

And this is my data class

data class Employee(
    var id: Int,
    var name: String,
    var dept: String,
    var joiningDate: String,
    var salary: Double
)

Hari Haran Swamy · Accepted Answer · 2020-11-27 11:44:30Z

1

Change SQL statement to 'INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL` for id row. You need to use primitive type

answered Nov 27, 2020 at 11:44

Hari Haran Swamy

3242 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

forpas Over a year ago

There are no primitive types in SQLite: sqlite.org/datatype3.html

karan · Accepted Answer · 2020-11-27 13:01:19Z

1

You are using incorrect syntax while defining id row, You have to use Integer as keyword AUTOINCREMENT can be used with INTEGER field only. Change your create table syntax as below and it will work

id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

edited Nov 27, 2020 at 13:01

answered Nov 27, 2020 at 11:43

karan

8,8734 gold badges45 silver badges81 bronze badges

2 Comments

forpas Over a year ago

int is not incorrect data tyoe for SQLite: sqlite.org/datatype3.html#affinity_name_examples

karan Over a year ago

@forpas thanks for pointing out. have corrected this.

forpas · Accepted Answer · 2020-11-27 12:07:07Z

If you really tried to create the table with this statement:

CREATE TABLE IF NOT EXISTS employees (
  id int PRIMARY KEY AUTOINCREMENT NOT NULL,
  ..........................
);

the result would be this error:

AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

So, my guess is that the table was not created by that statement, but by a previous statement on which you made changes later. These changes though were not actually reflected to the database, because the table already existed.

What you have to do is either uninstall the app from the device so the db is deleted, or change the version of the db in your SQLiteOpenHelper class so the onUpgrade() method is called which will delete and recreate the table.

So change the CREATE statement to:

CREATE TABLE IF NOT EXISTS employees (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  ..........................
);

Also, don't use execSQL() to insert rows in the table.
The recommended method is insert():

val cv = ContentValues()
cv.put("name", name)
cv.put("department", dept)
cv.put("joiningdate", joiningDate)
cv.put("salary", salary)
mDatabase.insert("employees", null, cv)

Collectives™ on Stack Overflow

Unable to add data to SQLite table in android

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related