0

I'm trying to add data to multiple tables using the addPoll function; however, when I do, the app crashes.

public void addPoll(String userName, String pollName, String optionName1){
    SQLiteDatabase db = this.getWritableDatabase();
    String userId = getUserId(userName);
    String pollId = getPollId(pollName);


    String query = "INSERT INTO polls SET userId=" + userId + ", pollName=" + pollName;
    String query2 = "INSERT INTO options SET pollId=" + pollId + ", optionName1=" + optionName1;


    db.execSQL(query);
    db.execSQL(query2);

}

I am doing it the way I would have done this in mysqli and php; is there an easier way to run these queries?

Full Database Handler:

package com.example.votingapp.library;

import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 2;
    // Database Name
    private static final String DATABASE_NAME = "beta.db";
    // Users table name
    public static final String TABLE_USERS = "users";

    // Users Table Columns names
    private static final String KEY_ID = "userId";
    private static final String KEY_NAME = "userName";
    private static final String KEY_PASS = "userPass";

    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_NAME + " TEXT,"
                + KEY_PASS + " TEXT" + ");";


       String CREATE_POLLS_TABLE = "CREATE TABLE polls ( pollId INTEGER PRIMARY KEY  AUTOINCREMENT, pollName TEXT, " +
                "userId INTEGER);";

      String CREATE_OPTIONS_TABLE = "CREATE TABLE options ( optionId INTEGER PRIMARY KEY  AUTOINCREMENT, optionName1 TEXT, optionName2 TEXT, " +
                "optionName3 TEXT, optionName4 TEXT, optionName5 TEXT, pollId INTEGER);";

       db.execSQL(CREATE_USERS_TABLE);
       db.execSQL(CREATE_POLLS_TABLE);
       db.execSQL(CREATE_OPTIONS_TABLE);

    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        db.execSQL("DROP TABLE IF EXISTS polls");
        db.execSQL("DROP TABLE IF EXISTS options");


        // Create tables again
        onCreate(db);
    }






    //Storing user details in database
    public void addUser(String userName, String userPass) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, userName); // userName
        values.put(KEY_PASS, userPass); // userPass

        // Inserting Row
        db.insert(TABLE_USERS, null, values);
        db.close(); // Closing database connection
    }

    public void addPoll(String userName, String pollName, String optionName1){
        SQLiteDatabase db = this.getWritableDatabase();
        String userId = getUserId(userName);
        String pollId = getPollId(pollName);


    String query = "INSERT INTO polls SET userId=" + userId + ", pollName=" + pollName;
    String query2 = "INSERT INTO options SET pollId=" + pollId + ", optionName1=" + optionName1;


        db.execSQL(query);
        db.execSQL(query2);

    }

    public String getPollId(String pollName) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor c = db.rawQuery("SELECT pollId FROM polls WHERE pollName='" + pollName + "'", null);

        if (c != null ) {
            c.moveToFirst();
            int iPassword = c.getColumnIndex("pollId");
            String pollId = c.getString(iPassword);
            return pollId;
        }else{
            return null;
        }
    }

    public String getUserId(String userName) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor c = db.rawQuery("SELECT userId FROM users WHERE userName='" + userName + "'", null);

        if (c != null ) {
            c.moveToFirst();
            int iPassword = c.getColumnIndex("userId");
            String userId = c.getString(iPassword);
            return userId;
        }else{
            return null;
        }
    }



    //Getting user data from database
    public HashMap<String, String> getUserDetails(){
        HashMap<String,String> user = new HashMap<String,String>();
        String selectQuery = "SELECT  * FROM " + TABLE_USERS;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if(cursor.getCount() > 0){
            user.put("userName", cursor.getString(1));
            user.put("userPass", cursor.getString(2));
        }
        cursor.close();
        db.close();
        // return user
        return user;
    }

    /**
     * Getting user login status
     * return true if rows are there in table
     * */
    public int getRowCount() {
        String countQuery = "SELECT  * FROM " + TABLE_USERS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int rowCount = cursor.getCount();
        db.close();
        cursor.close();

        // return row count
        return rowCount;
    }

    /**
     * Re crate database
     * Delete all tables and create them again
     * */
    public void resetTables(){
        SQLiteDatabase db = this.getWritableDatabase();
        // Delete All Rows
        db.delete(TABLE_USERS, null, null);
        db.close();
    }

    /**
     * Function get Login status
     * */
    public boolean isUserLoggedIn(Context context){
        DatabaseHandler db = new DatabaseHandler(context);
        int count = db.getRowCount();
        if(count > 0){
            // user logged in
            return true;
        }
        return false;
    }

    /**
     * Function to logout user
     * Reset Database
     * */
    public boolean logoutUser(Context context){
        DatabaseHandler db = new DatabaseHandler(context);
        db.resetTables();
        return true;
    }

}

1 Answer 1

1

Your SQL syntax is not correct here:

String query = "INSERT INTO polls SET userId=" + userId + ", pollName=" + pollName;
String query2 = "INSERT INTO options SET pollId=" + pollId + ", optionName1=" + optionName1;

Use either

UPDATE table SET ...

if you're updating, or

INSERT INTO table VALUES( ... )

if you're inserting new rows. Or better yet, use ContentValues and SQLiteDatabase insert() like you do in some other methods.

N.B. If you have a crash problem in your app, it's better to always include the logcat stacktrace in your question.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.