Click here to Skip to main content
15,040,212 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am trying to create login page with sqlite with multiple columns but unable to insert values in it :
Here is my insertion query code:


Java
 package com.example.doctorsmanager;
    
    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
     
    public class DataBaseAdapter 
    {
            static final String DATABASE_NAME = "doctordb";
            static final int DATABASE_VERSION = 2;
            
            static final String USERNAME_COLUMN = "UserName";
            static final String PASSWORD_COLUMN = "Password";
            static final String QUALIFICATION_COLUMN = "Qualification";
            static final String SPECIALIZATION_COLUMN = "Specialization";
            static final String COLUMN_ID = "id";
            static final String REGISTRATIONNUMBER_COLUMN = "RegistrationNumber";
            static final String CELLNUMBER_COLUMN = "CellNumber";
            // TODO: Create public field for each column in your table.
            // SQL Statement to create a new database.
            static final String DATABASE_CREATE = "create table "+"LOGIN"+
                                         "( " +"ID"+" integer primary key autoincrement,"+ "USERNAME  text,PASSWORD String,QUALIFICATION String,SPECIALIZATION text,REGISTRATIONNUMBER String,CELLNUMBER integer,MAIL String); ";
    //        public void onCreate(SQLiteDatabase db){
    //            String makeTable = "CREATE TABLE " + TABLE_FOOD + "("
    //                    + NAME_COLUMN + " TEXT," + CALORIE_COLUMN + " INTEGER,"
    //                    + PROTEIN_COLUMN + " INTEGER" + ")";
            // Variable to hold the database instance
            public  SQLiteDatabase db;
            // Context of the application using the database.
            private final Context context;
            // Database open/upgrade helper
            private DataBaseHelper dbHelper;
            public  DataBaseAdapter(Context _context) 
            {
                context = _context;
                dbHelper = new DataBaseHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
            }
            public DataBaseAdapter open() throws SQLException 
            {
                db = dbHelper.getWritableDatabase();
                return this;
            }
            public void close() 
            {
                db.close();
            }
     
            public  SQLiteDatabase getDatabaseInstance()
            {
                return db;
            }
     
            public void insertEntry(String userName,String password,String qualification,String specialization,String registrationNumber,String cellNumber,String mailId )
            {
               ContentValues newValues = new ContentValues();
                // Assign values for each row.
                newValues.put("USERNAME", userName);
                newValues.put("PASSWORD",password);
                newValues.put("QUALIFICATION",qualification);
                newValues.put("SPECIALIZATION",specialization);
                newValues.put("REGISTRATIONNUMBER",registrationNumber);
                newValues.put("CONTACTNUMBER",cellNumber);
                newValues.put("EMAIL",mailId);
                
                // Insert the row into your table
                db.insert("LOGIN", null, newValues);
                
                System.out.println("USERNAME");  
                System.out.println("PASSWORD");  
                
             //   Toast.makeText(context, "Reminder Is Successfully Saved", Toast.LENGTH_LONG).show();
            }
            public int deleteEntry(String UserName)
            {
                //String id=String.valueOf(ID);
                String where="USERNAME=?";
                int numberOFEntriesDeleted= db.delete("LOGIN", where, new String[]{UserName}) ;
               // Toast.makeText(context, "Number fo Entry Deleted Successfully : "+numberOFEntriesDeleted, Toast.LENGTH_LONG).show();
                return numberOFEntriesDeleted;
            }    
            public String getSinlgeEntry(String userName)
            {
                Cursor cursor=db.query("LOGIN", null, " USERNAME=?", new String[]{userName}, null, null, null);
                if(cursor.getCount()<1) // UserName Not Exist
                {
                    cursor.close();
                    return "NOT EXIST";
                }
                cursor.moveToFirst();
                String password= cursor.getString(cursor.getColumnIndex("PASSWORD"));
                cursor.close();
                return password;                
            }
            public void  updateEntry(String userName,String password,String qualification,String specialization,String registrationNumber,String cellNumber,String mailId)
            {
                // Define the updated row content.
                ContentValues updatedValues = new ContentValues();
                // Assign values for each row.
                updatedValues.put("USERNAME", userName);
                updatedValues.put("PASSWORD",password);
                updatedValues.put("QUALIFICATION",qualification);
                updatedValues.put("SPECIALIZATION",specialization);
                updatedValues.put("REGISTRATIONNUMBER",registrationNumber);
                updatedValues.put("CONTACTNUMBER",cellNumber);
                updatedValues.put("EMAIL",mailId);
     
                String where="USERNAME = ?";
                db.update("LOGIN",updatedValues, where, new String[]{userName});               
            }        

}
And here is the code on the signup page when user press the `button` to create account:
   
     btnCreateAccount.setOnClickListener(new View.OnClickListener() {
 
     public void onClick(View v) {
        // TODO Auto-generated method stub
 
        String userName=editTextUserName.getText().toString();
        String password=editTextPassword.getText().toString();
        String qualification=editTextQualification.getText().toString();
        String specializaion=editTextSpecialization.getText().toString();
        String registrationNumber=editTextRegistrationNumber.getText().toString();
        String cellNumber=editTextCellNumber.getText().toString();
        String mailId=editTextMail.getText().toString();
 
        // check if any of the fields are vaccant
        if(userName.equals("")||password.equals(""))
        {
                Toast.makeText(getApplicationContext(), "Field Vaccant", Toast.LENGTH_LONG).show();
                return;
        }
        // check if both password matches
        if(!password.equals(password))
        {
            Toast.makeText(getApplicationContext(), "Password does not match", Toast.LENGTH_LONG).show();
            return;
        }
        else
        {
            // Save the Data in Database
            DataBaseAdapter.insertEntry(userName,password,qualification,specializaion,registrationNumber,cellNumber,mailId);
            Toast.makeText(getApplicationContext(), "Account Successfully Created ", Toast.LENGTH_LONG).show();

and here is the query for main login page for successful login:

       btnLogIn.setOnClickListener(new View.OnClickListener() {
        public void onClick(View v) {
                        // get The User name and Password
                        String userName=editTextUserName.getText().toString();
                        String password=editTextPassword.getText().toString();
     
                        // fetch the Password form database for respective user name
                        String storedPassword=DataBaseAdapter.getSinlgeEntry(userName);
     
                        // check if the Stored password matches with  Password entered by user
                        if(password.equals(storedPassword))
                        {
                            Toast.makeText(MainActivity.this, "Congrats: Login Successfull", Toast.LENGTH_LONG).show();
    //                        dialog.dismiss();
                        }`enter code here`
                        else
                        {
                            Toast.makeText(MainActivity.this, "User Name or Password does not match", Toast.LENGTH_LONG).show();
                        }
    //                    Intent intentlogin=new Intent(getApplicationContext(),LoginSuccess.class);
    //                    startActivity(intentlogin);
                        }
                    });
This is my database helper class:

    package com.example.doctorsmanager;
    
    import android.content.Context;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteDatabase.CursorFactory;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.util.Log;
     
    public class DataBaseHelper extends SQLiteOpenHelper
    {
        public DataBaseHelper(Context context, String name,CursorFactory factory, int version) 
        {
                   super(context, name, factory, version);
        }
        // Called when no database exists in disk and the helper class needs
        // to create a new one.
        @Override
        public void onCreate(SQLiteDatabase _db) 
        {
                _db.execSQL(DataBaseAdapter.DATABASE_CREATE);
     
        }
        // Called when there is a database version mismatch meaning that the version
        // of the database on disk needs to be upgraded to the current version.
        @Override
        public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) 
        {
                // Log the version upgrade.
                Log.w("TaskDBAdapter", "Upgrading from version " +_oldVersion + " to " +_newVersion + ", which will destroy all old data");
     
                // Upgrade the existing database to conform to the new version. Multiple
                // previous versions can be handled by comparing _oldVersion and _newVersion
                // values.
                // The simplest case is to drop the old table and create a new one.
                _db.execSQL("DROP TABLE IF EXISTS " + "TEMPLATE");
                // Create a new one.
                onCreate(_db);
        }
     
    }


This is log cat:

02-28 02:37:47.838: W/TaskDBAdapter(14302): Upgrading from version 1 to 2, which will destroy all old data<br />
   02-28 02:37:47.848: E/SQLiteLog(14302): (1) table LOGIN already exists<br />
   02-28 02:37:47.908: D/AndroidRuntime(14302): Shutting down VM<br />
   02-28 02:37:47.908: W/dalvikvm(14302): threadid=1: thread exiting with uncaught exception (group=0xb4ab8ba8)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302): FATAL EXCEPTION: main<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302): Process: com.example.doctorsmanager, PID: 14302<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.doctorsmanager/com.example.doctorsmanager.MainActivity}: android.database.sqlite.SQLiteException: table LOGIN already exists (code 1): , while compiling: create table LOGIN( ID integer primary key autoincrement,USERNAME  text,PASSWORD String,QUALIFICATION String,SPECIALIZATION text,REGISTRATIONNUMBER String,CELLNUMBER integer,MAIL String);<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2195)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2245)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.app.ActivityThread.access$800(ActivityThread.java:135)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.os.Handler.dispatchMessage(Handler.java:102)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.os.Looper.loop(Looper.java:136)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.app.ActivityThread.main(ActivityThread.java:5017)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at java.lang.reflect.Method.invokeNative(Native Method)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at java.lang.reflect.Method.invoke(Method.java:515)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at dalvik.system.NativeStart.main(Native Method)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302): Caused by: android.database.sqlite.SQLiteException: table LOGIN already exists (code 1): , while compiling: create table LOGIN( ID integer primary key autoincrement,USERNAME  text,PASSWORD String,QUALIFICATION String,SPECIALIZATION text,REGISTRATIONNUMBER String,CELLNUMBER integer,MAIL String);<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1672)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1603)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at com.example.doctorsmanager.DataBaseHelper.onCreate(DataBaseHelper.java:20)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at com.example.doctorsmanager.DataBaseHelper.onUpgrade(DataBaseHelper.java:37)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:257)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at com.example.doctorsmanager.DataBaseAdapter.open(DataBaseAdapter.java:42)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at com.example.doctorsmanager.MainActivity.onCreate(MainActivity.java:29)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.app.Activity.performCreate(Activity.java:5231)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2159)<br />
   02-28 02:37:48.048: E/AndroidRuntime(14302):    ... 11 more</init></init>
Posted
Updated 6-Mar-14 3:24am
v2
Comments
joshrduncan2012 6-Mar-14 9:53am
   
So what's your question?

Please, read carefully the message error.

The main issue is here:
table LOGIN already exists
...
android.database.sqlite.SQLiteException: table LOGIN already exists (code 1): , while compiling: create table LOGIN( ID integer primary key autoincrement,USERNAME text,PASSWORD String,QUALIFICATION String,SPECIALIZATION text,REGISTRATIONNUMBER String,CELLNUMBER integer,MAIL String);
...
android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
02-28 02:37:48.048: E/AndroidRuntime(14302): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
02-28 02:37:48.048: E/AndroidRuntime(14302): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
02-28 02:37:48.048: E/AndroidRuntime(14302): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
02-28 02:37:48.048: E/AndroidRuntime(14302): at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
02-28 02:37:48.048: E/AndroidRuntime(14302): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1672)
02-28 02:37:48.048: E/AndroidRuntime(14302): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1603)
02-28 02:37:48.048: E/AndroidRuntime(14302): at com.example.doctorsmanager.DataBaseHelper.onCreate(DataBaseHelper.java:20)
02-28 02:37:48.048: E/AndroidRuntime(14302): at com.example.doctorsmanager.DataBaseHelper.onUpgrade(DataBaseHelper.java:37)
</init></init>


It means that you created database and table succesfully. You can't another table with the same name. You need to check if database/table already exists before start adding database/table. I'm not familiar with Android and SQLite, but i think, it would be helpfull: Android SQLite Database Connection not succesfull[^]

For further information, please see:
Android SQLite Database Tutorial[^]
Using SQLite Database with Android[^]
   
best practice is to check whether the table exist or not and if it is not available then only you should create it.
   
Comments
Anirudh Mangalvedhekar 27-May-14 1:34am
   
Override two methods OnUpgrade() and onCreate(). In onCreate() put your code which will create a table and in onUpgrade() put this code:
db.execSQL("DROP TABLE IF EXISTS "+ DATABASE_NAME);
onCreate(db);
This should work, IMHO......
Found the issue :
Quote:
android.database.sqlite.SQLiteException: table LOGIN already exists (code 1): , while compiling: create table LOGIN( ID integer primary key autoincrement,USERNAME text,PASSWORD String,QUALIFICATION String,SPECIALIZATION text,REGISTRATIONNUMBER String,CELLNUMBER integer,MAIL String);

So creation of the table must be done at onCreate() method of your SqliteOpenHelper class and once this method is called, this should not be called again. This must be taken care by you in your code.

-KR
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900