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";

Nikhil Jogdand · Accepted Answer · 2017-10-11 15:08:19Z

1

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());

answered Oct 11, 2017 at 15:08

Nikhil Jogdand

576 bronze badges

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

8 Comments

Arun Mani Over a year ago

I tried inserting with query = "INSERT INTO Station (Station_name) VALUES ('"+stations[i][0]+"');"; db1.execSQL(query); And i still get datanull as the output

Nikhil Jogdand Over a year ago

Please write your column name without () 'brackets'.

Arun Mani Over a year ago

You Mean Like This? "INSERT INTO Station Station_name VALUES ('"+stations[i][0]+"');"

Nikhil Jogdand Over a year ago

Yes.Go for it.,

Arun Mani Over a year ago

When i Used query = "INSERT INTO Station Station_name VALUES ('"+stations[i][0]+"');"; db1.execSQL(query); an Exception was thrown and the statement Toast.makeText(MainActivity.this, "An Error has occured", Toast.LENGTH_LONG).show(); was executed

|

MikeT · Accepted Answer · 2017-10-13 21:17:43Z

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

Collectives™ on Stack Overflow

Data is not being inserted into Sqlite database using Insert Query

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related