How to select a particular record and to set it to textView - Android SQlite

Question

I have the following code ! The SelectTable is not working as of now. All I need to do is to Pick a particular record from the database and to change the TextView of that to the record that is selected .
I.E., textView1.setText textView.setText should set it to the name and place of the selected record ! Please Help !

package com.example.sqlitedemo;

public class MainActivity extends Activity {

LinearLayout Linear;
SQLiteDatabase mydb;
private static String DBNAME = "PERSONS.db";    /
private static String TABLE = "MY_TABLE";       
TextView textView1,textView;

        @Override
        public void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
     textView1 = (TextView)findViewById(R.id.textView1);
     textView =(TextView) findViewById(R.id.textView2); 

            Toast.makeText(getApplicationContext(), "Creating table.", Toast.LENGTH_SHORT).show();

            dropTable();        // DROPPING THE TABLE.
            createTable();

            insertIntoTable();
            selectTable();
            //showTableValues();

        }

        public void createTable(){
            try{
            mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
            mydb.execSQL("CREATE TABLE IF  NOT EXISTS "+ TABLE +" (ID INTEGER PRIMARY KEY, NAME TEXT, PLACE TEXT);");
            mydb.close();
            }catch(Exception e){
                Toast.makeText(getApplicationContext(), "Error in creating table", Toast.LENGTH_LONG);
            }
        }
        // THIS FUNCTION INSERTS DATA TO THE DATABASE
        public void insertIntoTable(){
            try{
                mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
                mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('CODERZHEAVEN','GREAT INDIA')");
                mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('ANTHONY','USA')");
                mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('SHUING','JAPAN')");
                mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('JAMES','INDIA')");
                mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('SOORYA','INDIA')");
                mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('MALIK','INDIA')");
                mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('myname','America')");
                mydb.close();
            }catch(Exception e){
                Toast.makeText(getApplicationContext(), "Error in inserting into table", Toast.LENGTH_LONG);
            }
        }

        public void selectTable(){
            try{
            mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
            String q = "SELECT * FROM " + TABLE + " WHERE ID = '1'";
            //String name = c.getString(c.getColumnName(0));
            //String place = c.getString(c.getColumnName(1));
            Cursor mCursor = mydb.rawQuery(q, null);
            String name = mCursor.getString(0);
            String place = mCursor.getString(1);
            textView1.setText(name);
            textView.setText(place);

            mydb.close();
            }catch(Exception e){
                Toast.makeText(getApplicationContext(), "Error selecting", Toast.LENGTH_LONG);
            }

        }

        public void dropTable(){
            try{
                mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
                mydb.execSQL("DROP TABLE " + TABLE);

                mydb.close();
            }catch(Exception e){
                Toast.makeText(getApplicationContext(), "Error encountered while dropping.", Toast.LENGTH_LONG);
            }
        }
    }

xml Layout :


    <?xml version="1.0" encoding="utf-8"?>
    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="vertical"
        android:layout_width="fill_parent"
        android:layout_height="fill_parent">

            <TextView
                android:id="@+id/textView1"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Medium Text"
                android:textAppearance="?android:attr/textAppearanceMedium" />

            <TextView
                android:id="@+id/textView2"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Medium Text"
                android:textAppearance="?android:attr/textAppearanceMedium" />

        </LinearLayout>

Answer 1

You have create table query as follows,

mydb.execSQL("CREATE TABLE IF  NOT EXISTS "+ TABLE +" (ID INTEGER PRIMARY KEY, NAME TEXT, PLACE TEXT);");

Here your ID is in INTEGER format while in select query you are accessing it as Id = "1", which is wrong.

To correct this you need to make following corrections.

• First of all make your ID to AUTOINCREMENT as follows,

  mydb.execSQL("CREATE TABLE IF  NOT EXISTS "+ TABLE +" (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, PLACE TEXT);");
  

• Change your select query as follows,

  String q = "SELECT * FROM " + TABLE + " WHERE ID = 1";
  

• After this statement Cursor mCursor = mydb.rawQuery(q, null); write following statement,

  mCursor.moveToFirst();
  

Since you need to modify the create table query, I suggest you to remove your old application and install fresh one, otherwise it will not give proper output.