Data is not being inserted into Sqlite database using Insert Query

Question

Ive been trying to add a few items from a string array into a database and I cant seem to get the insert Query to work ive been working off of what I saw here Ive tried various variants of the insert query.

Here's my code:

SQLiteDatabase db1 = openOrCreateDatabase( "Station11.db", SQLiteDatabase.CREATE_IF_NECESSARY , null);
    try{
        String query = "CREATE TABLE IF NOT EXISTS Station ("
                + "Station_name VARCHAR);";
        db1.execSQL(query);
        Toast.makeText(MainActivity.this, "Table created", Toast.LENGTH_LONG).show();
        for(i=0;i<10;i++)
        {
            Toast.makeText(MainActivity.this, stations[i][0],Toast.LENGTH_SHORT).show();
            query = "INSERT  INTO Station VALUES ('"+stations[i][0]+"');";
            db1.execSQL(query);

        }
    }catch (Exception e){
        Toast.makeText(MainActivity.this, "An Error has occured", Toast.LENGTH_LONG).show();
    }
    Cursor data_fetch = db1.rawQuery("Select Station_name From Station", null);

    String[] station_array = new String[data_fetch.getCount()];
    data_fetch.moveToFirst();
    i = 0;
    while (data_fetch.moveToNext()) {
        String name = data_fetch.getString(data_fetch.getColumnIndex("Station_name"));
        station_array[i] = name;
        i++;
        Toast.makeText(MainActivity.this, "retrieved data"+station_array[i], Toast.LENGTH_LONG).show();
    }
    data_fetch.close();
}

When i toast the retrieved data The toast says retrieved datanull I even tried inserting a string and not a variable but i still get datanull as the toast.

Any help would be appreciated.

PS This Is the String array im trying to insert:

stations[0][0]= "New York";
    stations[1][0]= "Boston";
    stations[2][0]= "Las Vegas";
    stations[3][0]= "Miami";
    stations[4][0]= "Chicago";
    stations[5][0]= "New England";
    stations[6][0]= "Detroit";
    stations[7][0]= "Michigan";
    stations[8][0]= "Austin";
    stations[9][0]= "New Orealns";

Answer 1

I believe that the issue derives from the use of two dimensional array:-

stations[0][0]= "New York";
stations[1][0]= "Boston";
stations[2][0]= "Las Vegas";
stations[3][0]= "Miami";
stations[4][0]= "Chicago";
stations[5][0]= "New England";
stations[6][0]= "Detroit";
stations[7][0]= "Michigan";
stations[8][0]= "Austin";
stations[9][0]= "New Orealns";

Rather use either:-

    String[] stations = new String[10];
    stations[0]= "New York";
    stations[1]= "Boston";
    stations[2]= "Las Vegas";
    stations[3]= "Miami";
    stations[4]= "Chicago";
    stations[5]= "New England";
    stations[6]= "Detroit";
    stations[7]= "Michigan";
    stations[8]= "Austin";
    stations[9]= "New Orealns";

or (probably this one as it's more flexible)

    String[] stations = new String[] {
            "New York",
            "Boston",
            "Las Vegas",
            "Miami", "Chicago",
            "New England",
            "Detroit",
            "Michigan",
            "Austin", 
            "New Orleans"
    };

along with :-

    for(i=0;i< stations.length();i++)
        {
            Toast.makeText(MainActivity.this, stations[i],Toast.LENGTH_SHORT).show();
            query = "INSERT  INTO Station VALUES ('"+stations[i]+"');";
            db1.execSQL(query);

        }

Alternately the query could include the column name(s) and be:-

query = "INSERT  INTO Station Station_name VALUES ('"+stations[i]+"');";

A fully working version of you code could be :-

    String[] stations = new String[] {
            "New York",
            "Boston",
            "Las Vegas",
            "Miami", "Chicago",
            "New England",
            "Detroit",
            "Michigan",
            "Austin",
            "New Orleans"
    };

    int i=0;
    SQLiteDatabase db1 = openOrCreateDatabase( "Station11.db", SQLiteDatabase.CREATE_IF_NECESSARY , null);
    try{
        String query = "CREATE TABLE IF NOT EXISTS Station ("
                + "Station_name VARCHAR);";
        db1.execSQL(query);
        Log.d("STATION_TBLCRT","Table Created");
        //Toast.makeText(MainActivity.this, "Table created", Toast.LENGTH_LONG).show();
        for(i=0;i<stations.length;i++)
        {
            Log.d("STATION_INS","Inserting station " + stations[i]);
            //Toast.makeText(MainActivity.this, stations[i],Toast.LENGTH_SHORT).show();
            query = "INSERT  INTO Station VALUES ('"+stations[i]+"');";
            db1.execSQL(query);

        }
    }catch (Exception e){
        Log.d("STATION_INSERR","Error inserting station " + stations[i]);
        //Toast.makeText(MainActivity.this, "An Error has occured", Toast.LENGTH_LONG).show();
    }
    Cursor data_fetch = db1.rawQuery("Select Station_name From Station", null);

    String[] station_array = new String[data_fetch.getCount()];
    //data_fetch.moveToFirst();
    i = 0;
    while (data_fetch.moveToNext()) {
        String name = data_fetch.getString(data_fetch.getColumnIndex("Station_name"));
        station_array[i] = name;
        Log.d("STATION_GET","Retrieved station " + station_array[i]);
        i++;
        //Toast.makeText(MainActivity.this, "retrieved data"+station_array[i], Toast.LENGTH_LONG).show();
    }
    data_fetch.close();
}

Notes!

• Toasts have been replaced with Log
• data_fetch.moveToFirst has been commented out as this would result in skipping the first row.
• If the above is run more than once then additional sets of stations will be added.
• the i++ in the data_fetch has been moved to after reporting the station (data would otherwise always be null).
• using Toasts due to duration of Toast can be misleading, hence use of Log so you can see all results.

Example output:-

10-14 08:16:37.446 22117-22117/mjt.sqlitedbexamples D/STATION_TBLCRT: Table Created
10-14 08:16:37.446 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station New York
10-14 08:16:37.451 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station Boston
10-14 08:16:37.454 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station Las Vegas
10-14 08:16:37.457 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station Miami
10-14 08:16:37.461 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station Chicago
10-14 08:16:37.465 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station New England
10-14 08:16:37.468 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station Detroit
10-14 08:16:37.471 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station Michigan
10-14 08:16:37.473 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station Austin
10-14 08:16:37.478 22117-22117/mjt.sqlitedbexamples D/STATION_INS: Inserting station New Orleans
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station New York
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station Boston
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station Las Vegas
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station Miami
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station Chicago
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station New England
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station Detroit
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station Michigan
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station Austin
10-14 08:16:37.482 22117-22117/mjt.sqlitedbexamples D/STATION_GET: Retrieved station New Orleans

Answer 2

In your code missing column name after table name in insert query :

Correct insert Query :

INSERT INTO Station Station_name VALUES ('"+stations[i][0]+"');

Example :

INSERT INTO table_name_here (column1, column2, column3) VALUES ("Learn PHP", "John Poul", NOW());