• After 15+ years, we've made a big change: Android Forums is now Early Bird Club. Learn more here.

Trying to make use of databases

Derek Hill

Lurker
Aug 17, 2017
6
1
I am trying to create and insert a row for a login registration.

This is the code i use for my main page
Code:
public class MainActivity extends AppCompatActivity {
    //database
    DBHelper helper = new DBHelper(this);
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    }
    //clicking button to send user/pass
    public void onSubmit(View view){
        //getting text values and storing it as variables.
        EditText usertxt=(EditText)findViewById(R.id.ETuser);
        EditText passtxt=(EditText)findViewById(R.id.ETpass);
        EditText cpasstxt=(EditText)findViewById(R.id.ETcpass);
        TextView out=(TextView)findViewById(R.id.TVoutput);

        //storing them as string
        String struser = usertxt.getText().toString();
        String strpass = passtxt.getText().toString();
        String strcpass = cpasstxt.getText().toString();

        //Checking to see if username password or confirm password is blank.
        if(TextUtils.isEmpty(struser) || TextUtils.isEmpty(strpass) || TextUtils.isEmpty(strcpass)){
            Toast pass = makeText(MainActivity.this,"Please fill all fields.", LENGTH_SHORT);
            pass.show();
        }
        //checks to see if the pass and confirm pass is the same. if not then display message saying it doesnt match.
        if(!(strpass).equals(strcpass)){
            Toast pass = makeText(MainActivity.this,"Does not match", LENGTH_SHORT);
            pass.show();
        }else{
            helper.insertRow();
        }
        //setting text value of this text id.
        out.setText(usertxt.getText().toString()+"/"+passtxt.getText().toString()+"/"+cpasstxt.getText().toString());
    }
}

and my db class...
Code:
public class DBHelper extends SQLiteOpenHelper {
    SQLiteDatabase db;

    public DBHelper(Context context){
        super(context, "Testing.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table account (id integer primary key not null auto_increment, user text, pass text, rank text)");
        this.db = db;
    }
    public void insertRow(){
        db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        Cursor cursor = db.rawQuery("SELECT * FROM account", null);
        int count = cursor.getCount();
        values.put("id", count+1);
        values.put("user", "derek");
        values.put("pass", "testpass");
        db.insert("account", null, values);
        db.close();
    }

    public String searchPass(String user){
        db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT user, pass FROM account",null);
        String a, b;
        b = "Not Found";
        if(cursor.moveToFirst()){
            do{
                a = cursor.getString(0);
                if(a.equals(user)){
                    b = cursor.getString(1);
                    break;
                }
            }
            while(cursor.moveToNext());
        }
        return b;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS account");
        this.onCreate(db);
    }
}

what i am trying to achieve...

what i would like to do is to register a user and password for security. I would also like those values passed to the insertrow() function. But when i try to click submit the app crashes.
 
Code:
3/com.example.syn.testing E/AndroidRuntime: FATAL EXCEPTION: main
                                                                       Process: com.example.syn.testing, PID: 2663
                                                                       java.lang.IllegalStateException: Could not execute method for android:onClick
                                                                           at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:293)
                                                                           at android.view.View.performClick(View.java:5637)
                                                                           at android.view.View$PerformClick.run(View.java:22429)
                                                                           at android.os.Handler.handleCallback(Handler.java:751)
                                                                           at android.os.Handler.dispatchMessage(Handler.java:95)
                                                                           at android.os.Looper.loop(Looper.java:154)
                                                                           at android.app.ActivityThread.main(ActivityThread.java:6119)
                                                                           at java.lang.reflect.Method.invoke(Native Method)
                                                                           at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:886)
                                                                           at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:776)
                                                                        Caused by: java.lang.reflect.InvocationTargetException
                                                                           at java.lang.reflect.Method.invoke(Native Method)
                                                                           at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:288)
                                                                           at android.view.View.performClick(View.java:5637) 
                                                                           at android.view.View$PerformClick.run(View.java:22429) 
                                                                           at android.os.Handler.handleCallback(Handler.java:751) 
                                                                           at android.os.Handler.dispatchMessage(Handler.java:95) 
                                                                           at android.os.Looper.loop(Looper.java:154) 
                                                                           at android.app.ActivityThread.main(ActivityThread.java:6119) 
                                                                           at java.lang.reflect.Method.invoke(Native Method) 
                                                                           at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:886) 
                                                                           at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:776) 
                                                                        Caused by: android.database.sqlite.SQLiteException: near "auto_increment": syntax error (code 1): , while compiling: create table account (id integer primary key not null auto_increment, user text, pass text, rank text)
                                                                           at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                           at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
                                                                           at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
                                                                           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.executeSql(SQLiteDatabase.java:1677)
                                                                           at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1608)
                                                                           at com.example.syn.testing.DBHelper.onCreate(DBHelper.java:22)
                                                                           at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
                                                                           at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
                                                                           at com.example.syn.testing.DBHelper.insertRow(DBHelper.java:26)
                                                                           at com.example.syn.testing.MainActivity.onSubmit(MainActivity.java:45)
                                                                           at java.lang.reflect.Method.invoke(Native Method) 
                                                                           at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:288) 
                                                                           at android.view.View.performClick(View.java:5637) 
                                                                           at android.view.View$PerformClick.run(View.java:22429) 
                                                                           at android.os.Handler.handleCallback(Handler.java:751) 
                                                                           at android.os.Handler.dispatchMessage(Handler.java:95) 
                                                                           at android.os.Looper.loop(Looper.java:154) 
                                                                           at android.app.ActivityThread.main(ActivityThread.java:6119) 
                                                                           at java.lang.reflect.Method.invoke(Native Method) 
                                                                           at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:886) 
                                                                           at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:776)

I am still getting used to this so im still learning.
 
Upvote 0
Top tip: Read the stack trace. It usually tells you exactly what the problem is -

Code:
Caused by: android.database.sqlite.SQLiteException: near "auto_increment": syntax error (code 1): , while compiling: create table account (id integer primary key not null auto_increment, user text, pass text, rank text)
..
at com.example.syn.testing.DBHelper.onCreate(DBHelper.java:22)

So that line would be

Code:
db.execSQL("create table account (id integer primary key not null auto_increment, user text, pass text, rank text)");

So you can see it doesn't like "auto_increment". Your next step is to consult the SQLite online reference manual

https://sqlite.org/lang_createtable.html

Now tell me what you should be using instead of auto_increment.
 
Upvote 0
i should use AUTOINCREMENT with no underscore. after primary key. the error disappeared.

Also...i have a question. i get pixel errors on the emulator that looks similar to this.

Edit: Hmm....so i punched it in and even though no errors was seen i cant view the database with android device monitor. Still nothing there. I am only trying to send testing data though it but its not even initializing.
 

Attachments

  • Untitled.png
    Untitled.png
    12.1 KB · Views: 99
Last edited:
Upvote 0

BEST TECH IN 2023

We've been tracking upcoming products and ranking the best tech since 2007. Thanks for trusting our opinion: we get rewarded through affiliate links that earn us a commission and we invite you to learn more about us.

Smartphones