How to set up a SQLite database in Android?

Question

I am a beginner to Android Studio and Database. However, after videos and guide I was able to set up a basic one.

After doing so, I wanted to expand it to create 2 additional tables. However later when I am attempting to input data to those latter tables, the application crashes, and I cannot seem to find what is wrong with it. I tried different formatting to the syntax but it keeps crashing at the same event (when I try to submit data onto the table).

 //Following is the DatabaseHelper class that set up the database. 
 public class DatabaseHelper extends SQLiteOpenHelper{

//contacts
private static final String TABLE_CONTACTS = "contacts";
private static final String COLUMN_ID = "id";
private static final String COLUMN_NAME = "name";
private static final String COLUMN_EMAIL = "email";
private static final String COLUMN_PHONENUMBER = "phonenumber";
private static final String COLUMN_UNAME = "uname";
private static final String COLUMN_PASS = "pass";

//inventory
private static final String TABLE_ORGANIZATIONS = "organizations";
private static final String COLUMN_ORGANIZATION_ITEMID = "itemid";
private static final String COLUMN_ORGANIZATION_ITEMNAME = "itemname";
private static final String COLUMN_ORGANIZATION_PRICE = "price";
private static final String COLUMN_ORGANIZATION_QUANTITY = "quantity";
private static final String COLUMN_ORGANIZATION_DESCRIPTION = "description";

//management
private static final String TABLE_MANAGEMENTS = "managements";
private static final String COLUMN_MANAGEMENT_EVENTID = "eventid";
private static final String COLUMN_MANAGEMENT_EVENTNAME = "eventname";
private static final String COLUMN_MANAGEMENT_EVENTDATE = "eventdate";


private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "inventstory.db";

//decare database variable
SQLiteDatabase db;

//create a table for to hold values.
private static final String TABLE_CREATE_CONTACTS = "create table " + TABLE_CONTACTS + "("
        + COLUMN_ID + " integer primary key, "
        + COLUMN_NAME + " text not null , "
        + COLUMN_EMAIL + " text not null , "
        + COLUMN_PHONENUMBER + " text not null , "
        + COLUMN_UNAME + " text not null , "
        + COLUMN_PASS + " text not null" + ");";

//create a table for to hold values.
private static final String TABLE_CREATE_ORGANIZATIONS = "create table " + TABLE_ORGANIZATIONS + "("
        + COLUMN_ORGANIZATION_ITEMID + " integer primary key autoincrement, "
        + COLUMN_ORGANIZATION_ITEMNAME + " text not null , "
        + COLUMN_ORGANIZATION_PRICE + " text not null , "
        + COLUMN_ORGANIZATION_QUANTITY + " text not null , "
        + COLUMN_ORGANIZATION_DESCRIPTION + " text not null " + ");";

//create a table for to hold values.
private static final String TABLE_CREATE_MANAGEMENTS = " create table " + TABLE_MANAGEMENTS + "("
        + COLUMN_MANAGEMENT_EVENTID + " integer primary key, "
        + COLUMN_MANAGEMENT_EVENTNAME + " text not null , "
        + COLUMN_MANAGEMENT_EVENTDATE + " text not null " + ");";

//constructor
public DatabaseHelper(Context context)
{
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

    public void insertOrganization(inventoryorg c){

    //to insert to the database, use 'getWrite...' to make connection
    db = this.getWritableDatabase();
    //create content values
    ContentValues values = new ContentValues();

    // '*' means everything
    // fetch the data


    String query = " select * from 'organizations' ";
    Cursor cursor = db.rawQuery(query, null);
    int count = cursor.getCount(); //what does this do

 //   values.put(COLUMN_ORGANIZATION_ITEMID, count); // should this be changed to 'c.getItemid()'
    values.put(COLUMN_ORGANIZATION_ITEMNAME, c.getItemname());
    values.put(COLUMN_ORGANIZATION_PRICE, c.getPrice());
    values.put(COLUMN_ORGANIZATION_QUANTITY, c.getQuantity());
    values.put(COLUMN_ORGANIZATION_DESCRIPTION, c.getDescription());

    db.insert(TABLE_ORGANIZATIONS, null, values);

}

  @Override
   public void onCreate(SQLiteDatabase db) {
  //change
    //db.execSQL(TABLE_CREATE_CONTACTS);
    //db.execSQL(TABLE_CREATE_ORGANIZATIONS);
    //db.execSQL(TABLE_CREATE_MANAGEMENTS);
    db.execSQL(TABLE_CREATE_CONTACTS+TABLE_CREATE_ORGANIZATIONS+TABLE_CREATE_MANAGEMENTS);
    this.db = db;
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
  //  db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORGANIZATIONS);
    //db.execSQL("DROP TABLE IF EXISTS " + TABLE_MANAGEMENTS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS
            + " DROP TABLE IF EXISTS " + TABLE_ORGANIZATIONS
            + " DROP TABLE IF EXISTS " + TABLE_MANAGEMENTS); //same result
    this.onCreate(db);

}

This following code is the function (from OrgInsert.java) to insert data onto the table (decalred on Databasehelper.java).

public void onClickOrgSubmitButton(View v){

    if(v.getId()==R.id.BSubmitButton){
        //we read data from signup
        EditText itemname = (EditText) findViewById(R.id.TFitemname);
        EditText price = (EditText) findViewById(R.id.TFprice);
        EditText quantity = (EditText) findViewById(R.id.TFquantity);
        EditText description = (EditText) findViewById(R.id.TFdescription);

        //convert them to string values
        String itemnamestr = itemname.getText().toString();
        String pricestr = price.getText().toString();
        String quantitystr = quantity.getText().toString();
        String descriptionstr = description.getText().toString();

        //insert to the database
        inventoryorg inv = new inventoryorg();
        inv.setItemname(itemnamestr);
        inv.setPrice(pricestr);
        inv.setQuantity(quantitystr);
        inv.setDescription(descriptionstr);

               //this one needs to be enforced.
        helper.insertOrganization(inv);

        //popup message.
        Toast pass = Toast.makeText(OrgInsert.this, "Successfully created.", Toast.LENGTH_LONG);
        pass.show();

        //'starting new activity.' To start, we need to make object of Intent class
        //This takes back to the 'Organization' after new data is inserted.
        Intent i = new Intent(OrgInsert.this, Organization.class);
        startActivity(i);
    }
}

Caused by: android.database.sqlite.SQLiteException: no such table: organizations (code 1): , while compiling: select * from 'organizations'

1. table is not being created suspect: 'count' from value.put for ItemID

   //java.lang.IllegalStateException: Could not execute method for android:onClick
   //
   //at com.example.edward.inventstoryreformat.OrgInsert.onSubmitButton(OrgInsert.java:51)

I think :51 is helper.insertOrganization(inv).

Above information is some error messages I got which I tried to work around. This is confusing for me becasue 'contacts' get created without any problem, which is done by 'SignUp.java'. I have checked the database using SQLite Manager and verified that those are created and saved.

Answer 1

In your insertOrganization() method, change 'organization' to organization, in your SELECT statement.

UPDATE (2016/04/20):

As promised, some information about quotes in SQL statements:

[S]ingle quotes are for [S]tring literals in your SQL query. As organizations should already be created, it is already a table object of your DB. Therefore, the single quotes are not used is this case.

[D]ouble quotes are for reserved keywords in your [D]atabase. So, if your table was named insert, you should use SELECT * FROM "insert". You could try double quotes, but it should give the same error. Nonetheless, don't use single quotes when referencing an existing object in your database.

Also, after checking again your code and updates, I've noticed some things you should double check:

1. Your TABLE_CREATE_ORGANIZATIONS states that your COLUMN_ORGANIZATION_ITEMID should be auto-incremented. This means that you shouldn't have to explicitly set your id column for that table. So, you don't need the below lines at all in your INSERT code, which is actually the current error causing point:

   String query = " select * from 'organizations' ";
   Cursor cursor = db.rawQuery(query, null);
   int count = cursor.getCount(); //what does this do
   

2. Also, don't forget to close your database after opening it, with db.close(). In this case, it should be in the last lines of the insertOrganization(), the onCreate() and onUpdate() methods

3. At last, in all your TABLE_CREATE_XXX string constants, use CREATE TABLE IF NOT EXISTS (...), to ensure you are not overriding anything.

Let me know if any of this helps you. If it doesn't, I will provide you with a different way of extending the SQLiteOpenHelper class.

Answer 2

Try this!!

 public void insertOrganization(inventoryorg c){

//to insert to the database, use 'getWrite...' to make connection
db = this.getWritableDatabase();
//create content values
ContentValues values = new ContentValues();
 //   values.put(COLUMN_ORGANIZATION_ITEMID, count); // should this be changed to 'c.getItemid()'
values.put(COLUMN_ORGANIZATION_ITEMNAME, c.getItemname());
values.put(COLUMN_ORGANIZATION_PRICE, c.getPrice());
values.put(COLUMN_ORGANIZATION_QUANTITY, c.getQuantity());
values.put(COLUMN_ORGANIZATION_DESCRIPTION, c.getDescription());

db.insert(TABLE_ORGANIZATIONS, null, values);
db.close();

}