Auto increment is not working in SQLite database

Question

I am working in sqlite database. I have a product table which has a product id, name, price, and a company id I made to be the primary key and autoincrement when I add products to the database, but productid seems to be empty

Here's a screenshot of my database

Here's my database class

package com.example.abdelmagied.myapplication;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by AbdELMagied on 7/24/2017.
 */

public class database extends SQLiteOpenHelper {


    private static final String DATABASE_NAME = "product.db";
    private static final String TABLE_NAME    = "myproduct";

  /// columns in the database
    private static final String COL1_NAME     = "productId";
    private static final String COL2_NAME     = "productSalary";
    private static final String COL3_NAME     = "companyname";
    private static final String COL4_NAME     = "productName";


    // constructor
    public database(Context context) {

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


    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL("create table myproduct ("+COL1_NAME+" integer primary key  autoincrement, "+COL2_NAME+" text , "+COL3_NAME+" text , "+COL4_NAME+" text)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
           sqLiteDatabase.execSQL("drop database " + DATABASE_NAME );
           onCreate(sqLiteDatabase);
    }
}

This is the activity at which I insert in database

package com.example.abdelmagied.myapplication;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

public class addproduct extends AppCompatActivity {
    public Button AddToDatabase;
    public EditText productName , productSalary , companyMade;

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

        // get the button id
         AddToDatabase = (Button) findViewById(R.id.AddDatabase);

        // handle the action of the click
        AddToDatabase.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                // get the entered info from the edittexts
                productName   = (EditText) findViewById(R.id.PNameId);
                productSalary =(EditText) findViewById(R.id.PSalaryId);
                companyMade   = (EditText) findViewById(R.id.PSalaryId);

                //add to database productName , productSalary , companyMade...
                  database mydata = new database(getApplicationContext());
                  SQLiteDatabase db = mydata.getWritableDatabase();
                  ContentValues content = new ContentValues();
                  content.put("productSalary"  , productSalary.getText().toString());
                  content.put("companyname" , companyMade.getText().toString());
                  content.put("productName" , productName.getText().toString());
                  db.insert("myproduct" , null , content);

            }
        });

    }
}

Answer 1

Using your SQL for the table creation i.e.

`sqLiteDatabase.execSQL("create table myproduct ("+COL1_NAME+" integer primary key  autoincrement, "+COL2_NAME+" text , "+COL3_NAME+" text , "+COL4_NAME+" text)");`

And then using the following to test :-

            dbhlp = new SO45290855(this);

            SQLiteDatabase dbx = dbhlp.getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.put(SO45290855.COL2_NAME,"xxxx");
            cv.put(SO45290855.COL3_NAME,"yyyy");
            cv.put(SO45290855.COL4_NAME,"zzzz");
            dbx.insert(SO45290855.TABLE_NAME,null,cv);
            ContentValues cv2 = new ContentValues();
            cv2.put(SO45290855.COL2_NAME,"xxxx");
            cv2.put(SO45290855.COL3_NAME,"yyyy");
            cv2.put(SO45290855.COL4_NAME,"zzzz");
            dbx.insert(SO45290855.TABLE_NAME,null,cv);


            Cursor csr = dbx.query(SO45290855.TABLE_NAME,null,null,null,null,null,null);

            while (csr.moveToNext()) {
                for (int i=0; i < csr.getColumnCount();i++) {
                    Log.d("CsrPos_" + Integer.toString(csr.getPosition()),
                            "Column=" + csr.getColumnName(i) +
                                    " Data=" + csr.getString(i)
                    );
                }
            }

the output given is :-

07-25 09:30:41.721 2965-2965/? D/CsrPos_0: Column=productId Data=1
07-25 09:30:41.721 2965-2965/? D/CsrPos_0: Column=productSalary Data=xxxx
07-25 09:30:41.721 2965-2965/? D/CsrPos_0: Column=companyname Data=yyyy
07-25 09:30:41.721 2965-2965/? D/CsrPos_0: Column=productName Data=zzzz
07-25 09:30:41.721 2965-2965/? D/CsrPos_1: Column=productId Data=2
07-25 09:30:41.721 2965-2965/? D/CsrPos_1: Column=productSalary Data=xxxx
07-25 09:30:41.721 2965-2965/? D/CsrPos_1: Column=companyname Data=yyyy
07-25 09:30:41.721 2965-2965/? D/CsrPos_1: Column=productName Data=zzzz

SO45290855 being a copy of your database class

As such, due to the first line showing productId has a value of 1 and also that the 5th line shows the porductId, in the 2nd row, has a value of 2, in principle the code you have should create the same.

Therefore it is very likely that you have amended the code to change the structure but have not realised that onCreate basically runs once for the lifetime of the database file and thus unless you have deleted the database file, changes to the structure will not be applied.

The likely solution is to clear the App's data or to uninstall the App and rerun.

Further to this you very likely do not need to code AUTOINCREMENT using column_name INTEGER PRIMARY KEY will result in an incrementing column.

I'd suggest reading SQLite Autoincrement which includes

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.