CODEWITHSUNDEEP
javaandroidsqlite
Rudi
Rudi

Reputation: 105

Android application crashing when inserting row to SQlite?

This is the DBAdapter I'm using, and for some reason when I try to insert a row- the application crashes.

public class DBAdapter {

private static final String TAG = "DBAdapter"; //used for logging database version changes

// Field Names:
static final String KEY_ROWID = "Row ID";
static final String NAME_COLUMN = "Name";
static final String EMAIL_COLUMN = "E-mail";
static final String PHONENUMBER_COLUMN = "Phone Number";
static final String ADDRESS_COLUMN = "Street Address";
static final String ZIP_COLUMN = "Zip Code";
static final String ARRIVAL_COLUMN = "Arrival Date";
static final String DEPARTURE_COLUMN = "Departure Date";
static final String ROOM_COLUMN = "Room Number";
static final String NOTES_COLUMN = "Notes Area";
public static final String[] ALL_KEYS = new String[] {NAME_COLUMN, EMAIL_COLUMN, PHONENUMBER_COLUMN, ADDRESS_COLUMN, ZIP_COLUMN, ARRIVAL_COLUMN, DEPARTURE_COLUMN, ROOM_COLUMN, NOTES_COLUMN};

// Column Numbers for each Field Name:
public static final int COL_ROWID = 0;
public static final int COL_NAME = 1;
public static final int COL_EMAIL = 2;
public static final int COL_PHONENUMBER = 3;
public static final int COL_ADDRESS = 4;
public static final int COL_ZIP = 5;
public static final int COL_ARRIVAL = 6;
public static final int COL_DEPARTURE = 7;
public static final int COL_ROOM = 8;
public static final int COL_NOTES = 9;

// DataBase info:
static final String DATABASE_NAME = "Reservations.db";
static final String DATABASE_TABLE = "Reservations";
public static final int DATABASE_VERSION = 2; // The version number must be incremented each time a change to DB structure occurs.

//SQL statement to create database
private static final String DATABASE_CREATE_SQL =
        "CREATE TABLE " + DATABASE_TABLE
                + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + NAME_COLUMN + " TEXT NOT NULL, "
                + EMAIL_COLUMN + " TEXT"
                + PHONENUMBER_COLUMN + " TEXT NOT NULL, "
                + ADDRESS_COLUMN + " TEXT"
                + ZIP_COLUMN + " TEXT NOT NULL, "
                + ARRIVAL_COLUMN + " TEXT NOT NULL, "
                + DEPARTURE_COLUMN + " TEXT"
                + ROOM_COLUMN + " TEXT NOT NULL, "
                + NOTES_COLUMN + " TEXT"
                + ");";

private final Context context;
private DatabaseHelper myDBHelper;
private SQLiteDatabase db;


public DBAdapter(Context ctx) {
    this.context = ctx;
    myDBHelper = new DatabaseHelper(context);
}

// Open the database connection.
public DBAdapter open() {
    db = myDBHelper.getWritableDatabase();
    return this;
}

// Close the database connection.
public void close() {
    myDBHelper.close();
}

// Add a new set of values to be inserted into the database.
public long insertRow(String nameValue, String emailValue, String phoneValue, String addressValue, String zipValue, String arrivalValue, String departureValue, String roomValue, String notesValue) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(NAME_COLUMN, nameValue);
    initialValues.put(EMAIL_COLUMN, emailValue);
    initialValues.put(PHONENUMBER_COLUMN, phoneValue);
    initialValues.put(ADDRESS_COLUMN, addressValue);
    initialValues.put(ZIP_COLUMN, zipValue);
    initialValues.put(ARRIVAL_COLUMN, arrivalValue);
    initialValues.put(DEPARTURE_COLUMN, departureValue);
    initialValues.put(ROOM_COLUMN, roomValue);
    initialValues.put(NOTES_COLUMN, notesValue);

    // Insert the data into the database.
    return db.insert(DATABASE_TABLE, null, initialValues);
}

// Delete a row from the database, by rowId (primary key)
public boolean deleteRow(long rowId) {
    String where = KEY_ROWID + "=" + rowId;
    return db.delete(DATABASE_TABLE, where, null) != 0;
}

public void deleteAll() {
    Cursor c = getAllRows();
    long rowId = c.getColumnIndexOrThrow(KEY_ROWID);
    if (c.moveToFirst()) {
        do {
            deleteRow(c.getLong((int) rowId));
        } while (c.moveToNext());
    }
    c.close();
}

// Return all data in the database.
public Cursor getAllRows() {
    String where = null;
    Cursor c =  db.query(true, DATABASE_TABLE, ALL_KEYS, where, null, null, null, null, null);
    if (c != null) {
        c.moveToFirst();
    }
    return c;
}

// Get a specific row (by rowId)
public Cursor getRow(long rowId) {
    String where = KEY_ROWID + "=" + rowId;
    Cursor c =  db.query(true, DATABASE_TABLE, ALL_KEYS,
            where, null, null, null, null, null);
    if (c != null) {
        c.moveToFirst();
    }
    return c;
}

// Change an existing row to be equal to new data.
public boolean updateRow(long rowId, String name, String email, String phone, String address, String zipcode, String arrival, String departure, String room, String notes) {
    String where = KEY_ROWID + "=" + rowId;
    ContentValues newValues = new ContentValues();
    newValues.put(NAME_COLUMN, name);
    newValues.put(EMAIL_COLUMN, email);
    newValues.put(ADDRESS_COLUMN, address);
    newValues.put(ZIP_COLUMN, zipcode);
    newValues.put(ARRIVAL_COLUMN, arrival);
    newValues.put(DEPARTURE_COLUMN, departure);
    newValues.put(ROOM_COLUMN, room);
    newValues.put(NOTES_COLUMN, notes);
    // Insert it into the database.
    return db.update(DATABASE_TABLE, newValues, where, null) != 0;
}


private static class DatabaseHelper extends SQLiteOpenHelper
{
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase _db) {
        _db.execSQL(DATABASE_CREATE_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading application's database from version " + oldVersion
                + " to " + newVersion + ", which will destroy all old data!");

        // Destroy old database:
        _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);

        // Recreate new database:
        onCreate(_db);
    }
}


}

Here's the code from my new reservation activity

     public void createReservation(View view) {
    db.open();
    EditText nameText = (EditText)findViewById(R.id.nameText);
    String nameValue = nameText.getText().toString();
    EditText emailText = (EditText)findViewById(R.id.emailText);
    String emailValue = emailText.getText().toString();
    EditText phoneNumber = (EditText)findViewById(R.id.phoneNumber);
    String phoneValue = phoneNumber.getText().toString();
    EditText addressText = (EditText)findViewById(R.id.addressText);
    String addressValue = addressText.getText().toString();
    EditText zipNumber = (EditText)findViewById(R.id.zipNumber);
    String zipValue = zipNumber.getText().toString();
    EditText arrivalDate = (EditText)findViewById(R.id.arrivalDate);
    String arrivalValue = arrivalDate.getText().toString();
    EditText departureDate = (EditText)findViewById(R.id.departureDate);
    String departureValue = departureDate.getText().toString();
    EditText notesArea = (EditText)findViewById(R.id.notesText);
    String notesValue = notesArea.getText().toString();
    EditText roomNum = (EditText)findViewById(R.id.roomNum);
    String roomValue = roomNum.getText().toString();

    db.insertRow(nameValue, emailValue, phoneValue, addressValue, zipValue, arrivalValue, departureValue, roomValue, notesValue);
    Toast toast = Toast.makeText(getApplicationContext(), "Creating Reservation", Toast.LENGTH_LONG);
    toast.show();
    db.close();
}

Any and all help is greatly appreciated. I've tried looking things up online, which is where I found the DBAdapter, but when I use this DBAdapter I feel like I may have messed something up. For example in Android Studio, the Column Numbers give me the inspection warning that the field is never used. However they were included in the original DBAdapter. I feel like I may have made a mistake when adjusting the DBAdapter to fit my needs, but I can't seem to figure out where I went wrong with it. I am very new to Android programming, and have only made one other application before. Any help is appreciated

Upvotes: 0

Views: 1542

Answers (1)

Phantômaxx
Phantômaxx

Reputation: 38098

Your CREATE TABLE actually doesn't create any table, because it's wrong:

private static final String DATABASE_CREATE_SQL =
        "CREATE TABLE " + DATABASE_TABLE
                + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + NAME_COLUMN + " TEXT NOT NULL, "
                + EMAIL_COLUMN + " TEXT"
                + PHONENUMBER_COLUMN + " TEXT NOT NULL, "
                + ADDRESS_COLUMN + " TEXT"
                + ZIP_COLUMN + " TEXT NOT NULL, "
                + ARRIVAL_COLUMN + " TEXT NOT NULL, "
                + DEPARTURE_COLUMN + " TEXT"
                + ROOM_COLUMN + " TEXT NOT NULL, "
                + NOTES_COLUMN + " TEXT"
                + ");";

Should be:

private static final String DATABASE_CREATE_SQL =
        "CREATE TABLE " + DATABASE_TABLE
                + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + NAME_COLUMN + " TEXT NOT NULL, "
                + EMAIL_COLUMN + " TEXT, " // Mind the comma
                + PHONENUMBER_COLUMN + " TEXT NOT NULL, "
                + ADDRESS_COLUMN + " TEXT, " // Mind the comma
                + ZIP_COLUMN + " TEXT NOT NULL, "
                + ARRIVAL_COLUMN + " TEXT NOT NULL, "
                + DEPARTURE_COLUMN + " TEXT, " // Mind the comma
                + ROOM_COLUMN + " TEXT NOT NULL, "
                + NOTES_COLUMN + " TEXT"
                + ");";

Please not that you don't need and shouldn't use hardcoded column indexes.
This is because if you do a SELECT * ... nobody can predict the column order inside the row.

So, maybe, on a query run the email field has index 2 and on another run it has index 5.

That's why it's bad to use fixed column indexes, better use column names with getColumnIndex.

This code lets me assume you are going to do something bad:

// Column Numbers for each Field Name:
public static final int COL_ROWID = 0;
public static final int COL_NAME = 1;
public static final int COL_EMAIL = 2;
public static final int COL_PHONENUMBER = 3;
public static final int COL_ADDRESS = 4;
public static final int COL_ZIP = 5;
public static final int COL_ARRIVAL = 6;
public static final int COL_DEPARTURE = 7;
public static final int COL_ROOM = 8;
public static final int COL_NOTES = 9;

Instead, use

cursor.getString(cursor.getColumnIndex(YOUR_COLUMN_NAME)); // I used getString, but you'd use the actual column type

Moreover, don't use spaces or hyphens in your column names!

This 

// Field Names:
static final String KEY_ROWID = "Row ID";
static final String NAME_COLUMN = "Name";
static final String EMAIL_COLUMN = "E-mail";
static final String PHONENUMBER_COLUMN = "Phone Number";
static final String ADDRESS_COLUMN = "Street Address";
static final String ZIP_COLUMN = "Zip Code";
static final String ARRIVAL_COLUMN = "Arrival Date";
static final String DEPARTURE_COLUMN = "Departure Date";
static final String ROOM_COLUMN = "Room Number";
static final String NOTES_COLUMN = "Notes Area";

should be:

// Field Names:
static final String KEY_ROWID = "Row_ID";
static final String NAME_COLUMN = "Name";
static final String EMAIL_COLUMN = "eMail";
static final String PHONENUMBER_COLUMN = "Phone_Number";
static final String ADDRESS_COLUMN = "Street_Address";
static final String ZIP_COLUMN = "Zip_Code";
static final String ARRIVAL_COLUMN = "Arrival_Date";
static final String DEPARTURE_COLUMN = "Departure_Date";
static final String ROOM_COLUMN = "Room_Number";
static final String NOTES_COLUMN = "Notes_Area";

Upvotes: 4

Related Questions