Error while inserting and viewing the data

Question

I'm currently doing my thesis on Android Application Development, and I want to create a Register Screen where user can Input their data and the data stored in a SQLite database, I have watched the new boston channel on youtube about this function, and do the exact same way, but it doesn't work. I don't know whats wrong, I don't even know whether my data has been inputted correctly or not, Can you guys please help me to tell me whats wrong in these code? Thank you.

package com.thesis.teamizer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class Database {

public static final String DATABASE_NAME = "TeamizerDB.db";
public static final String TABLE_MEMBER = "Member";
public static final int DATABASE_VERSION = 1;
public static final String MEMBER_USERNAME = "Username";
public static final String MEMBER_PASSWORD = "Password";
public static final String MEMBER_EMAIL = "Email";
public static final String MEMBER_PHONE = "Phone";
public DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

public static class DbHelper extends SQLiteOpenHelper {

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("CREATE TABLE " + TABLE_MEMBER + " (" + MEMBER_USERNAME
                + " TEXT PRIMARY KEY NOT NULL, " + MEMBER_PASSWORD
                + " TEXT NOT NULL, " + MEMBER_EMAIL + " TEXT NOT NULL, "
                + MEMBER_PHONE + "INTEGER NOT NULL);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MEMBER);
        onCreate(db);
    }

}

public Database(Context c) {
    ourContext = c;
}

public Database open() throws SQLException {
    ourHelper = new DbHelper(ourContext);
    ourDatabase = ourHelper.getWritableDatabase();
    return this;
}

public void close() {

    ourHelper.close();
}

public boolean createEntry(String username, String pass, String email,
        String phone) {
    // TODO Auto-generated method stub
    ContentValues cv = new ContentValues();
    cv.put(MEMBER_USERNAME, username);
    cv.put(MEMBER_PASSWORD, pass);
    cv.put(MEMBER_EMAIL, email);
    cv.put(MEMBER_PHONE, phone);
    ourDatabase.insert(TABLE_MEMBER, null, cv);
    return true;
}

public String getData() {
    // TODO Auto-generated method stub
    String[] columns = new String[] { MEMBER_USERNAME, MEMBER_PASSWORD,
            MEMBER_EMAIL, MEMBER_PHONE };
    Cursor c = ourDatabase.query(TABLE_MEMBER, columns, null, null, null,
            null, null);
    /*
     * Cursor c = ourDatabase.query(TABLE_MEMBER, columns, null, null, null,
     * null, null);
     */
    String result = "";

    int iUsername = c.getColumnIndex(MEMBER_USERNAME);
    int iPassword = c.getColumnIndex(MEMBER_PASSWORD);
    int iEmail = c.getColumnIndex(MEMBER_EMAIL);
    int iPhone = c.getColumnIndex(MEMBER_PHONE);

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {

        result = result + c.getString(iUsername) + "\n";

    }
    return result;
}
}

RegisScreen.java

package com.thesis.teamizer; 

import java.util.regex.Matcher;
import java.util.regex.Pattern;
import android.app.Activity;
import android.app.Dialog;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class RegisScreen extends Activity {

private EditText emailEditText;
private EditText usernameEditText;
private EditText passEditText;
private EditText confPassEditText;
private EditText phoneEditText;
private Button registerButton;


@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.regis_screen);

    declaration();
    validation();

}

private void validation() {
    // TODO Auto-generated method stub
    registerButton.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View arg0) {
            int flag = 0;
            final String username = usernameEditText.getText().toString();
            final String email = emailEditText.getText().toString();
            final String pass = passEditText.getText().toString();
            final String confpas = confPassEditText.getText().toString();
            final String phone = phoneEditText.getText().toString();
            // Validating username
            if (!isValidUsername(username)) {
                usernameEditText
                        .setError("Username Must be Filled, Have at Least 6 Characters Long, and Must Not Contain Space");
                flag++;
            }
            // Validating email
            if (!isValidEmail(email)) {
                emailEditText.setError("Invalid Email Format");
                flag++;
            }
            // Validating password
            if (!isValidPassword(pass)) {
                passEditText.setError("Invalid Password");
                flag++;
            }
            // Validating confirm password
            if (!isValidConfPass(confpas, pass)) {
                confPassEditText.setError("Password missmatch");
                flag++;
            }
            // Validating phone
            if (!isValidPhone(phone)) {
                phoneEditText
                        .setError("Phone can only between 10-12 digits long");
                flag++;
            }

            // If every validation == true
            if (flag == 0) {
                boolean didItWork = true;
                try {
                    Database entry = new Database(RegisScreen.this);
                    entry.open();
                    entry.createEntry(username, pass, email, phone);
                    entry.close();
                } catch (Exception e) {
                } finally {
                    if (didItWork) {
                        Dialog d = new Dialog(RegisScreen.this);
                        d.setTitle("Yoii");
                        TextView tv = new TextView(RegisScreen.this);
                        tv.setText("Success");
                        d.setContentView(tv);
                        d.show();
                    }
                }
                    Intent intent = new Intent("com.thesis.teamizer.SQLVIEWS");
                    startActivity(intent);

            }
        }

    });
}

private void declaration() {
    // TODO Auto-generated method stub
    usernameEditText = (EditText) findViewById(R.id.editText_username);
    emailEditText = (EditText) findViewById(R.id.editText_email);
    passEditText = (EditText) findViewById(R.id.editText_password);
    confPassEditText = (EditText) findViewById(R.id.editText_confpassword);
    phoneEditText = (EditText) findViewById(R.id.editText_phone);
    registerButton = (Button) findViewById(R.id.btn_signup);
}

// Username Validation
private boolean isValidUsername(String username) {
    // TODO Auto-generated method stub

    if (username != null && username.length() > 6
            && !username.contains(" ")) {
        return true;
    }

    return false;
}

// Email Validation
private boolean isValidEmail(String email) {
    String EMAIL_PATTERN = "^[_A-Za-z0-9-\\+]+(\\.[_A-Za-z0-9-]+)*@"
            + "[A-Za-z0-9-]+(\\.[A-Za-z0-9]+)*(\\.[A-Za-z]{2,})$";

    Pattern pattern = Pattern.compile(EMAIL_PATTERN);
    Matcher matcher = pattern.matcher(email);
    return matcher.matches();
}

// Password Validation
private boolean isValidPassword(String pass) {
    if (pass != null && pass.length() > 6) {
        return true;
    }
    return false;
}

private boolean isValidConfPass(String confpas, String pass) {
    if (confpas.equals(pass)) {
        return true;
    }
    return false;
}

private boolean isValidPhone(String phone) {
    if (phone.length() > 9 || phone.length() < 13) {
        return true;
    }
    return false;
}
}

SQLViews.java

package com.thesis.teamizer;

import android.app.Activity;
import android.os.Bundle;
import android.widget.TextView;

public class SQLViews extends Activity{

@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.sqlview);
    TextView tv = (TextView) findViewById(R.id.tvSQLinfo);
    Database info = new Database (this);
    info.open();
    String data = info.getData();       
    info.close();
    tv.setText(data); 

} 

}

Here are the log cat :

    10-29 23:27:12.421: E/SQLiteDatabase(30715): Error inserting Email=djdjsj@sjsjsjs.com     Password=juliusleo Username=sjsjsjsjs
10-29 23:27:12.421: E/SQLiteDatabase(30715): android.database.sqlite.SQLiteConstraintException: Member.PhoneINTEGER may not be NULL (code 19)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:972)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1603)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1473)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at com.thesis.teamizer.Database.createEntry(Database.java:69)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at com.thesis.teamizer.RegisScreen$1.onClick(RegisScreen.java:83)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.view.View.performClick(View.java:4654)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.view.View$PerformClick.run(View.java:19438)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.os.Handler.handleCallback(Handler.java:733)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.os.Handler.dispatchMessage(Handler.java:95)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.os.Looper.loop(Looper.java:146)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at android.app.ActivityThread.main(ActivityThread.java:5602)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at java.lang.reflect.Method.invokeNative(Native Method)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at java.lang.reflect.Method.invoke(Method.java:515)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1283)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1099)
10-29 23:27:12.421: E/SQLiteDatabase(30715):    at dalvik.system.NativeStart.main(Native Method)
10-29 23:27:12.446: D/TextLayoutCache(30715): Enable myanmar Zawgyi converter
10-29 23:27:12.446: D/TextLayoutCache(30715): Enable myanmar Zawgyi converter
10-29 23:27:12.501: D/TextLayoutCache(30715): Enable myanmar Zawgyi converter
10-29 23:27:12.501: D/TextLayoutCache(30715): Enable myanmar Zawgyi converter
10-29 23:27:12.551: E/SQLiteLog(30715): (1) no such column: Phone
10-29 23:27:12.551: D/AndroidRuntime(30715): Shutting down VM
10-29 23:27:12.551: W/dalvikvm(30715): threadid=1: thread exiting with uncaught exception (group=0x41d92c08)
10-29 23:27:12.556: E/AndroidRuntime(30715): FATAL EXCEPTION: main
10-29 23:27:12.556: E/AndroidRuntime(30715): Process: com.thesis.teamizer, PID: 30715
10-29 23:27:12.556: E/AndroidRuntime(30715): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.thesis.teamizer/com.thesis.teamizer.SQLViews}: android.database.sqlite.SQLiteException: no such column: Phone (code 1): , while compiling: SELECT Username, Password, Email, Phone FROM Member
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2413)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2471)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.app.ActivityThread.access$900(ActivityThread.java:175)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1308)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.os.Handler.dispatchMessage(Handler.java:102)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.os.Looper.loop(Looper.java:146)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.app.ActivityThread.main(ActivityThread.java:5602)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at java.lang.reflect.Method.invokeNative(Native Method)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at java.lang.reflect.Method.invoke(Method.java:515)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1283)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1099)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at dalvik.system.NativeStart.main(Native Method)
10-29 23:27:12.556: E/AndroidRuntime(30715): Caused by: android.database.sqlite.SQLiteException: no such column: Phone (code 1): , while compiling: SELECT Username, Password, Email, Phone FROM Member
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1113)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:690)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1448)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1295)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1166)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1334)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at com.thesis.teamizer.Database.getData(Database.java:77)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at com.thesis.teamizer.SQLViews.onCreate(SQLViews.java:17)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.app.Activity.performCreate(Activity.java:5451)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1093)
10-29 23:27:12.556: E/AndroidRuntime(30715):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2377)
10-29 23:27:12.556: E/AndroidRuntime(30715):    ... 11 more
10-29 23:27:14.241: I/dalvikvm-heap(30856): Grow heap (frag case) to 12.955MB for 4367376-byte allocation
10-29 23:27:14.341: D/TextLayoutCache(30856): Enable myanmar Zawgyi converter
10-29 23:27:14.341: D/TextLayoutCache(30856): Enable myanmar Zawgyi converter
10-29 23:27:14.341: D/TextLayoutCache(30856): Enable myanmar Zawgyi converter
10-29 23:27:14.341: D/TextLayoutCache(30856): Enable myanmar Zawgyi converter
10-29 23:27:14.361: D/libEGL(30856): loaded /system/lib/egl/libEGL_mali.so
10-29 23:27:14.366: D/libEGL(30856): loaded /system/lib/egl/libGLESv1_CM_mali.so
10-29 23:27:14.371: D/libEGL(30856): loaded /system/lib/egl/libGLESv2_mali.so
10-29 23:27:14.376: E/(30856): Device driver API match
10-29 23:27:14.376: E/(30856): Device driver API version: 29
10-29 23:27:14.376: E/(30856): User space API version: 29 
10-29 23:27:14.376: E/(30856): mali: REVISION=Linux-r3p2-01rel3 BUILD_DATE=Tue Jul 22 19:59:34 KST 2014 

Answer 1

I don't see anything wrong with your code, but then you've not supplied any LogCat and there is a distinct lack of any debug output to be seen, so without that, it's going to be a stab in the dark as to whats wrong.

As for general hints and tips, you could try:

1. If using the emulator, you can use the DDMS in Eclipse to browse to your database file, should be in: /data/data/com.YOURAPP/databases/YOURDB - After pulling your DB, you can use a free tool, such as: SQLiteStudio you can check what (if anything) is inside your database.

2. If you're running on a device, you can use a batch script such as:

   cd C:\adt-bundle-windows-x86_64-20140321\sdk\platform-tools adb -d shell "run-as com.goosesys.gaggle cat /data/data/com.goosesys.gaggle/databases/goosemob > /sdcard/goosemob.sqlite" pause

Which I use to pull my database and cp if to the SD card for easier retrieval. You'll have to change paths etc (for adb, and your app/database path).

Failing that, you're going to have to give us a bit more info. Maybe add some output that shows the amount of rows if you just do a simple SELECT * - Or the count of rows returned from your query. Anything will help really.

I'm happy to help more, when you've supplied more info.

EDIT

Spotted this:

db.execSQL("CREATE TABLE " + TABLE_MEMBER + " (" + MEMBER_USERNAME
            + " TEXT PRIMARY KEY NOT NULL, " + MEMBER_PASSWORD
            + " TEXT NOT NULL, " + MEMBER_EMAIL + " TEXT NOT NULL, "
            + MEMBER_PHONE + "INTEGER NOT NULL);");

Should be:

db.execSQL("CREATE TABLE " + TABLE_MEMBER + " (" + MEMBER_USERNAME
            + " TEXT PRIMARY KEY NOT NULL, " + MEMBER_PASSWORD
            + " TEXT NOT NULL, " + MEMBER_EMAIL + " TEXT NOT NULL, "
            + MEMBER_PHONE + " INTEGER NOT NULL);"); // NOTE THE SPACE IN FRONT OF "INTEGER NOT NULL"

It's likely that your database hasn't even been created. As such you should of seen an Exception in LogCat.

EDIT Funkystein beat me to it!

EDIT

android.database.sqlite.SQLiteConstraintException: Member.PhoneINTEGER may not be NULL (code 19)

There's a problem right there. You're not entering a value for PhoneINTEGER, because you're looking for Phone. I'd delete your database and start over. You'll probably find it works.