CODEWITHSUNDEEP
javaandroidsqliteandroid-sqlite
Joel Dong-Joe Suh
Joel Dong-Joe Suh

Reputation: 47

Unable to insert data into android studio sqlite database

I am currently doing a school project, trying to create a food delivery service and need a signup/login database. But am having trouble creating/inserting the data into the database. The app doesn't crash, but the Toast Message tells me "Sign up unsuccessful.".

From the error log, it seems I am able to create the table, but is unable to insert the data into the table.

I would appreciate it if anyone could give me some advice.

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = " mylist.db ";
public static final String TABLE_NAME = " mylist ";
public static final String USER_NAME = " username ";
public static final String EMAIL = " email ";
public static final String PHONE_NUMBER = " phone ";
public static final String PASSWORD = " password ";

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

//CREATING THE DATABASE TABLE
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
    String createTable;
    createTable = "CREATE TABLE " + TABLE_NAME +
            " ( ID INTEGER PRIMARY KEY AUTOINCREMENT, "+
            USER_NAME + " username " +
            EMAIL + " email ," +
            PHONE_NUMBER + " phone ,"+
            PASSWORD + " password ) ";
    sqLiteDatabase.execSQL(createTable);
}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
}

public boolean addData(String username, String email, String phone, String password){
SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(USER_NAME, username);
contentValues.put(EMAIL, email);
contentValues.put(PHONE_NUMBER, phone);
contentValues.put(PASSWORD, password);
long result = sqLiteDatabase.insert(TABLE_NAME, null, contentValues);
    if (result == -1) return false;
else {return true;}
}

}

Error log

    E/SQLiteLog: (1) table mylist has no column named email
E/SQLiteDatabase: Error inserting  phone =  email =  password =  username =j
    android.database.sqlite.SQLiteException: table mylist has no column named email (code 1): , while compiling: INSERT INTO  mylist ( phone , email , password , username ) VALUES (?,?,?,?)
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:890)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:501)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1546)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1417)
        at com.example.doonfood.DatabaseHelper.addData(DatabaseHelper.java:45)
        at com.example.doonfood.signuppage.ADDDATA(signuppage.java:33)
        at com.example.doonfood.signuppage.Signup(signuppage.java:48)
        at java.lang.reflect.Method.invoke(Native Method)
        at androidx.appcompat.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:409)
        at android.view.View.performClick(View.java:6294)
        at com.google.android.material.button.MaterialButton.performClick(MaterialButton.java:992)
        at android.view.View$PerformClick.run(View.java:24770)
        at android.os.Handler.handleCallback(Handler.java:790)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:164)
        at android.app.ActivityThread.main(ActivityThread.java:6494)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)

signuppage.java

public class signuppage extends AppCompatActivity {

DatabaseHelper myDB;
EditText SignupUserName;
EditText SignupEMail;
EditText SignupPhoneNumber;
EditText SignupPassword;
Button SignupBtn;

@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.signuppage);
    myDB = new DatabaseHelper(this);
    SignupUserName = findViewById(R.id.SignupUserName);
    SignupEMail = findViewById(R.id.SignupEMail);
    SignupPhoneNumber = findViewById(R.id.SignupPhoneNumber);
    SignupPassword = findViewById(R.id.SignupPassword);
    SignupBtn = findViewById(R.id.SignupBtn);
}

public void ADDDATA(String newUSER, String newEMAIL, String newPHONE, String newPASSWORD){
    boolean insertData = myDB.addData(newUSER, newEMAIL, newPHONE, newPASSWORD);
    if (insertData == true){
        Toast.makeText(signuppage.this, "Sign up successful.", Toast.LENGTH_SHORT).show();
    }else {
        Toast.makeText(signuppage.this, "Sign up unsuccessful.", Toast.LENGTH_SHORT).show();
    }
}

public void Signup(View view) {
    String newUSER, newEMAIL, newPHONE, newPASSWORD;
    newUSER = SignupUserName.getText().toString();
    newEMAIL = SignupEMail.getText().toString();
    newPHONE = SignupPhoneNumber.getText().toString();
    newPASSWORD = SignupPassword.getText().toString();
    if ((SignupUserName.length() != 0) || (SignupEMail.length() != 0) || (SignupPhoneNumber.length() != 0) || (SignupPassword.length() != 0)) {
        ADDDATA(newUSER, newEMAIL, newPHONE, newPASSWORD);
        SignupUserName.setText(newUSER);
        SignupEMail.setText(newEMAIL);
        SignupPhoneNumber.setText(newPHONE);
        SignupPassword.setText(newPASSWORD);
    } else {
        Toast.makeText(signuppage.this, "Text Field cannot be empty.", Toast.LENGTH_SHORT).show();
    }
}

}

Upvotes: 2

Views: 1471

Answers (2)

forpas
forpas

Reputation: 164089

First, remove the leading and trailing spaces in the definitions of the variables for the names of the db/table/columns:

public static final String DATABASE_NAME = "mylist.db";
public static final String TABLE_NAME = "mylist";
public static final String ID = "ID"; // I added this definition
public static final String USER_NAME = "username";
public static final String EMAIL = "email";
public static final String PHONE_NUMBER = "phone";
public static final String PASSWORD = "password";

Then, fix the CREATE statement of the table where instead of defining the data types of the columns you use their names twice and you also missed the commas in between:

createTable = "CREATE TABLE " + TABLE_NAME + "(" +
              ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
              USER_NAME + " TEXT," +
              EMAIL + " TEXT," +
              PHONE_NUMBER + " TEXT," +
              PASSWORD + " TEXT)";

After you make these changes you will have to uninstall the app from the device, so that the database is deleted and rerun to recreate the database and the table.

Upvotes: 1

Sanjeev Dahiya
Sanjeev Dahiya

Reputation: 11

  1. First, you don't need to write .db in the database name.

  2. Write down like this in onCreate()

    String query = "Create table if not exists " + TABLE_NAME + " (UESR_NAME, EMAIL, PHONE_NUMBER,PASSWORD)"; db.execSQL(query);

  3. Create a method to insert data in the table, like this : public void insertData(String userName, String email, String phoneNumber, String password) { SQLiteDatabase db = getWritableDatabase(); ContentValues values = new ContentValues(); values.put("USER_NAME", userName); values.put("EMAIL", email); values.put("PHONE_NUMBER",phoneNumber); values.put("PASSWORD",password); db.insert(TABLE_NAME,null,values); db.close(); }

Upvotes: 1

Related Questions