column _id doesnt exist.

Put problem concerning Views, Layouts and other XML-Resources (like AndroidManifest) here.

column _id doesnt exist.

Postby vaishumol » Sat Mar 20, 2010 6:01 am

Hi friends im getting the following error while running my application.

Error :
Caused by: java.lang.IllegalArgumentException: column '_id' does not exist
android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:314)


Here is my javacode.
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2.  
  3.  
  4. import android.view.Menu;
  5.  
  6. import android.view.MenuInflater;
  7.  
  8. import android.view.MenuItem;
  9.  
  10. import android.view.View;
  11.  
  12. import android.view.ViewGroup;
  13.  
  14. import android.widget.Button;
  15.  
  16. import android.widget.CursorAdapter;
  17.  
  18. import android.widget.ImageView;
  19.  
  20. import android.widget.TextView;
  21.  
  22.  
  23.  
  24. public class ResultScreen extends ListActivity{
  25.  
  26.        
  27.  
  28.         RandomQuestionActivity random;
  29.  
  30.         int score;
  31.  
  32.         TextView answer;
  33.  
  34.        
  35.  
  36.         private SampleEntry entry;
  37.  
  38.      
  39.  
  40.        
  41.  
  42.         private ResultsAdapter resultsAapter;
  43.  
  44.        
  45.  
  46.         /**
  47.  
  48.          * Cursor for the category.
  49.  
  50.          */
  51.  
  52.         private Cursor cursor;
  53.  
  54.        
  55.  
  56.        
  57.  
  58.         public void onCreate(Bundle savedInstanceState) {
  59.  
  60.         super.onCreate(savedInstanceState);
  61.  
  62.        
  63.  
  64.         setContentView(R.layout.list_pagination);
  65.  
  66.        
  67.  
  68.         cursor = SampleApplication.mDb.fetchAllResults();
  69.  
  70.         startManagingCursor(cursor);
  71.  
  72.         resultsAapter = new ResultsAdapter(this, cursor);
  73.  
  74.         getListView().setAdapter(resultsAapter);
  75.  
  76.         getListView().setCacheColorHint(0);
  77.  
  78.        
  79.  
  80.        
  81.  
  82.        
  83.  
  84.         }
  85.  
  86.        
  87.  
  88.         private static class ResultsAdapter extends CursorAdapter {
  89.  
  90.  
  91.  
  92.         public ResultsAdapter(Context context, Cursor cursor) {
  93.  
  94.           super(context, cursor);
  95.  
  96.  
  97.  
  98.           mInflater = LayoutInflater.from(context);
  99.  
  100.  
  101.  
  102.           idColumn = cursor.getColumnIndexOrThrow(DBAdapter.KEY_ID);
  103.  
  104.           answerColumn = cursor.getColumnIndexOrThrow(DBAdapter.KEY_ANSWER);
  105.  
  106.                    
  107.  
  108.         }
  109.  
  110.  
  111.  
  112.         private LayoutInflater mInflater;
  113.  
  114.  
  115.  
  116.         private int idColumn;
  117.  
  118.         private int answerColumn;
  119.  
  120.        
  121.  
  122.         @Override
  123.  
  124.         public View newView(Context context, Cursor cursor, ViewGroup parent) {
  125.  
  126.           View view = mInflater.inflate(R.layout.result_screen, parent, false);
  127.  
  128.  
  129.  
  130.           ViewHolder holder = new ViewHolder();
  131.  
  132.           holder.answer = (TextView) view.findViewById(R.id.answer);
  133.  
  134.        
  135.  
  136.           holder.answer.setTextSize(16);
  137.  
  138.           view.setTag(holder);
  139.  
  140.  
  141.  
  142.           return view;
  143.  
  144.         }
  145.  
  146.  
  147.  
  148.         private static class ViewHolder {
  149.  
  150.         public TextView answer;
  151.  
  152.         }
  153.  
  154.  
  155.  
  156.         @Override
  157.  
  158.         public void bindView(View view, Context context, Cursor cursor) {
  159.  
  160.           ViewHolder holder = (ViewHolder) view.getTag();
  161.  
  162.  
  163.  
  164.           holder.answer.setText(cursor.getString(answerColumn));
  165.  
  166.         }
  167.  
  168.  
  169.  
  170.         public void refresh() {
  171.  
  172.           getCursor().requery();
  173.  
  174.         }
  175.  
  176.  
  177.  
  178.       }
  179.  
  180.  
  181.  
  182. }
  183.  
  184.  
Parsed in 0.015 seconds, using GeSHi 1.0.8.4


And my Database query is like this.
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. public Cursor fetchAllResults() {
  3.  
  4.  
  5.  
  6.                 return mDb.query(REVIEW_TABLE, REVIEW_COLUMNS, KEY_ID, null, null, null,
  7.  
  8.                                 null);
  9.  
  10.  
  11.  
  12.         }
Parsed in 0.010 seconds, using GeSHi 1.0.8.4


Pleae help me to solve the issue friends. Thanks in advance.
vaishumol
Experienced Developer
Experienced Developer
 
Posts: 74
Joined: Sun Jan 03, 2010 6:33 am

Top

Postby grzegorz.zajac » Sat Mar 20, 2010 4:33 pm

Hello,

Does your table in DB contain _id column?
If you want to use CursorAdapter (or SimpleCursorAdapter) the corresponding table MUST have _id column.

The common approach is to create _id column as: _id INTEGER PRIMARY KEY AUTOINCREMENT

Regards,
Grzesiek
grzegorz.zajac
Developer
Developer
 
Posts: 39
Joined: Wed Feb 18, 2009 8:37 pm
Location: Krakow/Poland

Postby pskink » Sat Mar 20, 2010 8:15 pm

grzegorz.zajac wrote:Hello,

Does your table in DB contain _id column?
If you want to use CursorAdapter (or SimpleCursorAdapter) the corresponding table MUST have _id column.

Regards,
Grzesiek


not exactly, the returned Cursor MUST have such column
pskink
pskink
Master Developer
Master Developer
 
Posts: 719
Joined: Mon Nov 24, 2008 3:49 pm

Postby grzegorz.zajac » Sat Mar 20, 2010 9:00 pm

OK, you are right.
Here is a description from API reference for CursorAdapter:
Adapter that exposes data from a Cursor to a ListView widget. The Cursor must include a column named "_id" or this class will not work.


But if the cursor is just a result of your query (you do not modify the cursor on your own) then the table has to contain _id column... Or maybe I am wrong?

Regards,
Grzesiek
grzegorz.zajac
Developer
Developer
 
Posts: 39
Joined: Wed Feb 18, 2009 8:37 pm
Location: Krakow/Poland

Postby pskink » Sat Mar 20, 2010 9:08 pm

grzegorz.zajac wrote:But if the cursor is just a result of your query (you do not modify the cursor on your own) then the table has to contain _id column... Or maybe I am wrong?

Regards,
Grzesiek


well, good question, but consider such raw query:

"select imie, nazwisko, index as _id from pracownicy"

that makes the trick :)
pskink
pskink
Master Developer
Master Developer
 
Posts: 719
Joined: Mon Nov 24, 2008 3:49 pm

Postby grzegorz.zajac » Sat Mar 20, 2010 9:21 pm

Bravo!
Excellent example :)

Anyway, for the convenience function query() and the case from the first post, _id column is needed in the table...

Regards,
Grzesiek
grzegorz.zajac
Developer
Developer
 
Posts: 39
Joined: Wed Feb 18, 2009 8:37 pm
Location: Krakow/Poland

Top

Postby pskink » Sat Mar 20, 2010 9:42 pm

grzegorz.zajac wrote:
Anyway, for the convenience function query() and the case from the first post, _id column is needed in the table...

Regards,
Grzesiek


but if he replace mDb.query to mDb.rawQuery it's not.

if his db file is not created by him, but e.g. taken from the web this is the only solution as he cannot run ALTER TABLE statement
pskink
pskink
Master Developer
Master Developer
 
Posts: 719
Joined: Mon Nov 24, 2008 3:49 pm

Postby grzegorz.zajac » Sat Mar 20, 2010 9:45 pm

Agree :)
grzegorz.zajac
Developer
Developer
 
Posts: 39
Joined: Wed Feb 18, 2009 8:37 pm
Location: Krakow/Poland

Postby vaishumol » Thu Mar 25, 2010 6:11 pm

Thanks you friends.

With your help i solved my issue.

Thanks a lot.

Now im coming again with another issue in SQLITE.
Im having three tables that marks, name, and result tables. Getting name from textbox and updating the user_name table. then im getting the marks from another textbox and updating in marks table. At last im getting the values from both tables, and inserting into result table. while inserting the values im getting following errors :

03-25 22:28:36.241: ERROR/Database(769): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
03-25 22:28:36.241: ERROR/Database(769): at android.database.sqlite.SQLiteStatement.native_execute(Native Method)
03-25 22:28:36.241: ERROR/Database(769): at android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java:66)
03-25 22:28:36.241: ERROR/Database(769): at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1313)
03-25 22:28:36.241: ERROR/Database(769): at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1173)


Please help me friends.

Thanks in advance.
vaishumol
Experienced Developer
Experienced Developer
 
Posts: 74
Joined: Sun Jan 03, 2010 6:33 am

Re: column _id doesnt exist.

Postby blkhwks19 » Wed Aug 03, 2011 12:12 am

I am having the same issue as the very first post of this thread. I read the response posts but I cant seem to implement a solution that works. Im only partially understanding what the problem is and what needs to be done to fix it so I really need a little more guidance to get my application set up correctly. Heres my code:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. SQLiteDatabase db = openOrCreateDatabase("MyDB", MODE_PRIVATE, null);
  2.         db.execSQL("CREATE TABLE IF NOT EXISTS Characters (Name VARCHAR, Player VARCHAR);");
  3.         db.execSQL("INSERT INTO Characters VALUES ('TestName', 'TestPlayer');");
  4.        
  5.         Cursor c = db.rawQuery("SELECT * FROM Characters", null);
  6.        
  7.        if (c.getCount() > 0) {
  8.                   c.moveToFirst();
  9.                   ListView list = (ListView) findViewById(android.R.id.list);
  10.                   SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_2, c, new String[] {"Name", "Player"}, new int[] {android.R.id.text1, android.R.id.text2});
  11.                   list.setAdapter(adapter);
  12.         }
  13.        
  14.         db.close();
  15.  
Parsed in 0.011 seconds, using GeSHi 1.0.8.4


Basically all this does is create a database/table and insert some test data into it. Then retrieve the data and display it in a listview. As I mentioned, I keep getting the error "column '_id' does not exist". I tried altering my create table statemtent to include an _id column, like this:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. db.execSQL("CREATE TABLE IF NOT EXISTS Characters (_id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR, Player VARCHAR);");
Parsed in 0.010 seconds, using GeSHi 1.0.8.4

... but that doesnt seem to work. Not sure what to do next, any ideas? Thanks a ton in advance!!!
blkhwks19
Freshman
Freshman
 
Posts: 3
Joined: Wed Aug 03, 2011 12:01 am

Re: column _id doesnt exist.

Postby blkhwks19 » Sat Aug 06, 2011 8:35 pm

Anyone have any thoughts on why this error keeps coming up? Im not sure how to create the database, or insert/retrieve the data in the proper way to satisfy this error. Any ideas PLEASE let me know, thanks so much!
blkhwks19
Freshman
Freshman
 
Posts: 3
Joined: Wed Aug 03, 2011 12:01 am

Re: column _id doesnt exist.

Postby blkhwks19 » Mon Aug 15, 2011 3:01 pm

For anyone else reading this or having the same problem, I did end up finding a solution. This thread was totally right and does have the answer, I was jsut too new to all this to really see it and understand what it meant, but after some more research I figured it out.

The simplest way to fix this is to include some sort of index column in your table, doesnt matter what its called but just say its called index, and its set to be the primary key and also autoincrement. Then the select statement that gets returned to the cursor just needs to get the index column as _id, followed by the rest of the columns from the table. So it should look like this:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. Cursor c = db.rawQuery("SELECT index as _id, FirstName, LastName FROM MyTable", null);
Parsed in 0.010 seconds, using GeSHi 1.0.8.4


Thats it, that satisfies the cursors need for the _id column. There may be multiple ways of achieving the same results but I found this worked nice and easy for me as my database table isnt very large so I can simply just type out the names of the columns to return. If anyone finds an easier or more efficient or just generally better practice way of solving this issue, please let us know here so we can learn!
blkhwks19
Freshman
Freshman
 
Posts: 3
Joined: Wed Aug 03, 2011 12:01 am

Top

Return to View, Layout & Resource Problems

Who is online

Users browsing this forum: No registered users and 1 guest