Attempting to retrieve rowid of android database always returns 0.

Question

I'm using a SQLite database to store information on players and their teams using android. I've got it working so that all the players I've created will show up when ever I start my program, so the database is working. So far, so good.

However, whenever I try to retrieve the rowId of a player from the database, it always gives me 0 back. This surprises me in itself, since I'd think I'd get an error if I wasn't accessing it from the cursor correctly, but instead it gives me 0.

After debugging for a while, I realized that the Player object (not in database) also wasn't saving the id correctly, although it was finding a value for it. This was corrected when I set the id after creating the object, although it seems I would be able to set it in the creation statement itself. Curiouser and curiouser.

Even after I do have the id of the row, I can't edit it. The Player object will change, but if I actually retrieve the player from the database again, it hasn't changed at all.

Weirdest to me is that I don't receive any errors at any point throughout this process.

Ideas? Thank you in advance for your help! :-)

Here's my code for the Database Helper:

    public class VolleyDbAdapter {

 // Common
 public static final String KEY_ROWID = "id";
 public static final String KEY_NOTES = "Notes";

 // Player
 public static final String KEY_NUMBER = "Number";
 public static final String KEY_POSITION = "Position";
 public static final String KEY_FNAME = "FirstName";
 public static final String KEY_LNAME = "LastName";
 public static final String KEY_TEAMID = "teamId";


 private static final String TAG = "NotesDbAdapter";
 private DatabaseHelper mDbHelper;
 private SQLiteDatabase mDb;

 /**
  * Database creation sql statement
  */
 private static final String DATABASE_CREATE_PLAYERS = "CREATE TABLE 'players' ("
   + "  `id` INTEGER AUTO_INCREMENT DEFAULT NULL,"
   + "  `Number` INTEGER DEFAULT NULL,"
   + "  `Position` VARCHAR DEFAULT NULL,"
   + "  `FirstName` MEDIUMTEXT DEFAULT NULL,"
   + "  `LastName` MEDIUMTEXT DEFAULT NULL,"
   + "  `teamId` INTEGER DEFAULT NULL,"
   + "  `Notes` MEDIUMTEXT DEFAULT NULL,"
   + "  PRIMARY KEY (`id`)"
   + ");";

 private static final String DATABASE_PLAYERS = "players";
 private static final int DATABASE_VERSION = 5;

 private final Context mCtx;

 private static class DatabaseHelper extends SQLiteOpenHelper {

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

  @Override
  public void onCreate(SQLiteDatabase db) {

   db.execSQL(DATABASE_CREATE_PLAYERS);
   db.execSQL(DATABASE_CREATE_GAMES);
   db.execSQL(DATABASE_CREATE_STATS);
   db.execSQL(DATABASE_CREATE_MATCHES);
   db.execSQL(DATABASE_CREATE_LINEUPS);
   db.execSQL(DATABASE_CREATE_TEAMS);

  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
     + newVersion + ", which will destroy all old data");

   // Add this later, when actually might update the database
   // Don't want to accidentally delete something now!
   db.execSQL("DROP TABLE IF EXISTS players;");
   db.execSQL("DROP TABLE IF EXISTS teams;");
   db.execSQL("DROP TABLE IF EXISTS matches;");
   db.execSQL("DROP TABLE IF EXISTS games;");
   db.execSQL("DROP TABLE IF EXISTS lineups;");
   db.execSQL("DROP TABLE IF EXISTS stats;");
   onCreate(db);
  }
 }

 public VolleyDbAdapter(Context ctx) {
  this.mCtx = ctx;
 }

 public VolleyDbAdapter open() throws SQLException {
  mDbHelper = new DatabaseHelper(mCtx);
  mDb = mDbHelper.getWritableDatabase();
  return this;
 }

 public void close() {
  mDbHelper.close();
 }

 public long createPlayer(String fname, String lname, int number,
   String position, long teamId) {
  ContentValues initialValues = new ContentValues();

  initialValues.put(KEY_FNAME, fname);
  initialValues.put(KEY_LNAME, lname);
  initialValues.put(KEY_NUMBER, number);
  initialValues.put(KEY_POSITION, position);
  initialValues.put(KEY_TEAMID, teamId);
  // Add the teamId?

  return mDb.insert(DATABASE_PLAYERS, null, initialValues);
 }

 public boolean deletePlayer(long rowId) {

  return mDb.delete(DATABASE_PLAYERS, KEY_ROWID + "=" + rowId, null) > 0;
 }

 public boolean deletePlayer(int number) {

  return mDb.delete(DATABASE_PLAYERS, KEY_NUMBER + "=" + number, null) > 0;
 }

 public Cursor fetchAllPlayers() {

  return mDb.query(DATABASE_PLAYERS, new String[] { KEY_ROWID,
    KEY_NUMBER, KEY_POSITION, KEY_FNAME, KEY_LNAME, KEY_TEAMID,
    KEY_NOTES }, null, null, null, null, null);
 }

 public Cursor fetchPlayer(long rowId) throws SQLException {

  Cursor mCursor =

  mDb.query(true, DATABASE_PLAYERS, new String[] { KEY_ROWID, KEY_NUMBER,
    KEY_POSITION, KEY_FNAME, KEY_LNAME, KEY_TEAMID, KEY_NOTES },
    KEY_ROWID + "=" + rowId, null, null, null, null, null);
  if (mCursor != null) {
   mCursor.moveToFirst();
  }
  return mCursor;

 }

 public Cursor fetchPlayer(int number) throws SQLException {

  Cursor mCursor =

  mDb.query(true, DATABASE_PLAYERS, new String[] { KEY_ROWID, KEY_NUMBER,
    KEY_POSITION, KEY_FNAME, KEY_LNAME, KEY_TEAMID, KEY_NOTES },
    KEY_NUMBER + "=" + number, null, null, null, null, null);
  if (mCursor != null) {
   mCursor.moveToFirst();
  }
  return mCursor;

 }

 public boolean updatePlayer(long rowId, String fname, String lname,
   int number, String position, long teamId) {
  ContentValues args = new ContentValues();

  args.put(KEY_FNAME, fname);
  args.put(KEY_LNAME, lname);
  args.put(KEY_NUMBER, number);
  args.put(KEY_POSITION, position);
  args.put(KEY_TEAMID, teamId);
  return mDb
    .update(DATABASE_PLAYERS, args, KEY_ROWID + "=" + rowId, null) > 0;
 }

And for loading the players:

    public class GlobalApp extends Application {
 SharedPreferences preferences;
 VolleyDbAdapter mDbHelper;
 public long teamId;
 public long matchId;
 public long gameId;


 ArrayList<Player> playerList;
 ArrayList<Player> court;
 ArrayList<Player> play;

 public int selectedPlayer;
 public int selectedPosition;
 //More vars not shown


 public void onCreate() {
  super.onCreate();
  preferences = PreferenceManager.getDefaultSharedPreferences(this);
  initializeVars();
  importOldData();
 }

 public void importOldData() {
  mDbHelper = new VolleyDbAdapter(this);
  mDbHelper.open();
  getPlayersFromDatabase();
  mDbHelper.close();
 }

    public void getPlayersFromDatabase() {
      Cursor c = mDbHelper.fetchPlayersFromTeam(getTeamId());
      c.moveToFirst();
      while (c.isAfterLast() == false) {
       long myId = c.getInt(c.getColumnIndex(VolleyDbAdapter.KEY_ROWID));
       String fname = c.getString(c
         .getColumnIndex(VolleyDbAdapter.KEY_FNAME));
       String lname = c.getString(c
         .getColumnIndex(VolleyDbAdapter.KEY_LNAME));
       String pos = c.getString(c
         .getColumnIndex(VolleyDbAdapter.KEY_POSITION));
       String num = c.getString(c
         .getColumnIndex(VolleyDbAdapter.KEY_NUMBER));
       long ti = c.getLong(c.getColumnIndex(VolleyDbAdapter.KEY_TEAMID));

       Player p = new Player(myId, fname, lname, num, pos, ti);
       addPlayer(p);

       c.moveToNext();
      }
      c.close();

     }

The code to create a new player (MenuManager is an activity that has the menu added to it):

public class EditPlayer extends MenuManager implements OnClickListener {

 GlobalApp global;
 VolleyDbAdapter mDbHelper;

 EditText s1, s2, s3;
 Button b1;
 Spinner choosePos;
 int editPosition;
 // Player selectedPlayer;
 ArrayAdapter<CharSequence> adapter;

 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.edit_player);
  global = ((GlobalApp) getApplicationContext());
  mDbHelper = new VolleyDbAdapter(this);
  mDbHelper.open();

  s1 = (EditText) this.findViewById(R.id.player_input_fname);
  s2 = (EditText) this.findViewById(R.id.player_input_lname);
  s3 = (EditText) this.findViewById(R.id.player_input_number);

  choosePos = (Spinner) findViewById(R.id.choosePos);
  adapter = ArrayAdapter.createFromResource(this, R.array.positions,
    android.R.layout.simple_spinner_item);
  adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
  choosePos.setAdapter(adapter);

  b1 = (Button) this.findViewById(R.id.player_submit);
  b1.setOnClickListener(this);

  if (global.getSelectedPlayer() != null) {
   fillFields();
  }

 }

 public void fillFields() {
  s1.setText(global.getSelectedPlayer().getFName());
  s2.setText(global.getSelectedPlayer().getLName());
  s3.setText(global.getSelectedPlayer().getNumber());
  choosePos.setSelection(adapter.getPosition(global.getSelectedPlayer()
    .getPosition()));

 }

 @Override
 public void onClick(View v) {
  switch (v.getId()) {
  case R.id.player_submit:

   if (global.getSelectedPlayer() == null) {
    long myId = mDbHelper.createPlayer(s1.getText().toString(), s2
      .getText().toString(), Integer.parseInt(s3.getText()
      .toString()), choosePos.getSelectedItem().toString(),
      global.teamId);

    Player myP = new Player(myId, s1.getText().toString(), s2
      .getText().toString(), s3.getText().toString(),
      choosePos.getSelectedItem().toString(), global
        .getTeamId());
    myP.setId(myId);

    global.addPlayer(myP);
    global.sortPlayers(global.NUMBER);

    Intent intent = new Intent();
    setResult(RESULT_OK, intent);
    finish();
   } else {
    Player p = global.getSelectedPlayer();
    p.setFName(s1.getText().toString());
    p.setLName(s2.getText().toString());
    p.setNumber(s3.getText().toString());
    p.setPosition(choosePos.getSelectedItem().toString());

    mDbHelper.updatePlayer(p.getId(), p.getFName(), p.getLName(),
      p.getIntNumber(), p.getPosition(), p.getTeamId());

    global.sortPlayers(global.NUMBER);

    Intent intent = new Intent();
    setResult(RESULT_OK, intent);
    finish();
   }

   // this.finish();

   // setResult(0);
   break;
  }

 }

}

And finally, the player itself:

public class Player{
 String number;
 String fname;
 String lname;
 String position;
 long id;
 long teamId;

 public Player(String fn, String ln, String nu, String p, long t) {
  fname = fn;
  lname = ln;
  number = nu;
  position = p;
  teamId = t;
 }

 public Player(long i, String fn, String ln, String nu, String p, long t) {
  id = i;
  fname = fn;
  lname = ln;
  number = nu;
  position = p;
  teamId = t;
 }

 public void setId(long i) { id = i; }
 public long getId() { return id; }
 public long getTeamId() {return teamId; }
 //You should never need to set the teamId outside of the constructor


 public String getFName() { return fname; }
 public String getLName() { return lname; }
 public String getFullName() { return fname + " " + lname; }


 public String getNumber() { return number; }
 public int getIntNumber() { return Integer.valueOf(getNumber()); } 
 public String getPosition() { return position; }

 public int getHits() { return hits; }
 public int getSets() { return sets; }
 public int getSetsKills() { return setsKills; }
 public int getPasses() { return passes; }
 public int getPassesKills() { return passesKills; }
 public int getHitsKills() { return hitsKills; }
 public int getHitsMisses() { return hitsMisses; }
 public int getBlocks() { return blocks; }

 public void setFName(String s) { fname = s; }
 public void setLName(String s) { lname = s; }
 public void setNumber(String s) { number = s; }
 public void setPosition(String s) { position = s; }

 public String toString() { return getNumber(); }

 public String showMyStats() {

  /*TableLayout tb = new TableLayout(c);
   tb.addView(getStatRow("Number: ", getNumber(), c));
   tb.addView(getStatRow("Name: ", getName(), c));
   tb.addView(getStatRow("Position: ", getPosition(), c));
   tb.addView(getStatRow("", "", c));
   tb.addView(getStatRow("Hit Attempts: ", getHits(), c));
   tb.addView(getStatRow("Kills", getHitsKills(), c));
   tb.addView(getStatRow("", "", c));
   tb.addView(getStatRow("", "", c));*/

  String stats = "";
  stats += "Number: " + getNumber() + "\n";
  stats += "Name: " + getFullName() + "\n";
  stats += "Position: " + getPosition() + "\n";
  stats += "\n";
  stats += "Hit Attempts: " + getHits() + "\n";
  stats += "Kills: " + getHitsKills() + "\n";
  stats += "Misses: " + getHitsMisses() + "\n";
  stats += "Hitting Percentage: " + getHittingPercentage() + "\n";
  stats += "\n";
  stats += "Sets: " + getSets() + "\n";
  stats += "Set Kills: " + getSetsKills() + "\n";
  stats += "Setting Percentage: " + getSettingPercentage() + "\n";
  stats += "\n";
  stats += "Passes: " + getPasses() + "\n";
  stats += "Passes Kills: " + getPassesKills() + "\n";
  stats += "Passing Percentage: " + getPassingPercentage() + "\n";


  //TextView popup = new TextView(c);
  //popup.setLayout(new TableLayout());
  //popup.setText(stats);


  return stats;
 }

 public TableRow getStatRow(String s1, String s2, Context c) {
  TableRow tr = new TableRow(c);
  TextView t = new TextView(c);

  t.setText(s1);
  tr.addView(t);
  t.setText(s2);
  tr.addView(t);

  return tr;

 }

 public TableRow getStatRow(String s1, int s2, Context c) {
  return getStatRow(s1, Integer.toString(s2), c);

 }

 public double getHittingPercentage() {
  if(getHits() > 0) {
   return (getHitsKills()-getHitsMisses()) / getHits(); 
  } else { return 0; }

 }

 public double getSettingPercentage() {
  if(getSets() > 0) {
   return (getSetsKills()) / getSets(); 
  } else { return 0; }

 }

 public double getPassingPercentage() {
  if(getPasses() > 0) {
   return (getPassesKills()) / getPasses(); 
  } else { return 0; }

 }

}

Answer 1

Actually, I think the right formula for auto increment is :

INTEGER PRIMARY KEY AUTOINCREMENT

not

AUTO_INCREMENT