Android sqlite insert data SQLiteException

Question

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
        // providers data
//      db.execSQL("insert  into "
//              + TABLE_PROVIDERS
//              + " (_id, provider_id, parent_id, title, image, sh_n, enable, visible, image_bg, type, is_group, hide_in_search, limit_min, limit_max, off_on_line, invoice_search) "
//              + "VALUES (1,600,101,'Источники оплаты','',1,1,0,'','',1,1,0,0,0,0)");

        ContentValues cv = new ContentValues();
        cv.put("_id", 1);
        cv.put("provider_id", 600);
        cv.put("parent_id", 101);
        cv.put("title", "Bla");
        cv.put("image", "bla");
        cv.put("sh_n", 1);
        cv.put("enable", 1);
        cv.put("visible", 1);
        cv.put("image_bg", "");
        cv.put("type", "");
        cv.put("is_group", 1);
        cv.put("hide_in_search", 1);
        cv.put("limit_min", 10);
        cv.put("limit_max", 10000);
        cv.put("off_on_line", 1);
        cv.put("invoice_search", 1);

        db.insertOrThrow(TABLE_PROVIDERS, null, cv);
}

Now I'm doing as was suggested, inserting row by row:

import java.util.*;

import android.content.*;
import android.database.*;
import android.database.sqlite.*;

public class ProviderDataSource {

    private SQLiteDatabase database;
    private MySQLiteHelper dbHelper;

    public ProviderDataSource(Context context) {
        dbHelper = new MySQLiteHelper(context);
    }

    public void open() throws SQLException {
        database = dbHelper.getWritableDatabase();
    }

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

    public List<Provider> getProvidersByParentId(long parentId) {

        List<Provider> providersList = new ArrayList<Provider>();

        Cursor cursor = database.query(MySQLiteHelper.TABLE_PROVIDERS, new String[] { "provider_id", "title", "image" }, " parent_id=" + parentId,
                null, null, null, null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Provider provider = cursorToProvider(cursor);
            providersList.add(provider);
            cursor.moveToNext();
        }
        cursor.close();
        System.out.println("getProvidersByParentId");
        return providersList;
    }

    private Provider cursorToProvider(Cursor cursor) {

        Provider provider = new Provider();
        provider.setId(cursor.getInt(1));
        provider.setTitle(cursor.getString(3));
        provider.setImg(cursor.getString(4));

        return provider;

    }

}

It doesn't work. It seems like the row wasn`t inserted and the array returnd by getProvidersByParentId method is empty. Parent_id as argument is 101.

Answer 1

Your insert statement is wrong. In SQLite, you cant insert multiple records by separating them with comma, in fact you need to prepare separate insert commands for that. But if your SQLite version is 3.7.11 then its possible.

Read this.

Answer 2

You can do it with a for loop like this

// add a button click event like

String[] arr1 = new String[n]; String[] arr2 = new String[n]; String[] arr3 = new String[n];

addButton.setOnClickListener
            (
                new View.OnClickListener()
                {
                    @Override public void onClick(View v) {


                               for(int i=1;1<4;i++){


            try
            {
                // ask the database manager to add a row given the two strings

                //db object of database manager class 

                db.addValues
                (
                    arr1[i] , 
                    arr2[i] , 
                    arr3[i] , 

                );


                           }
catch (Exception e)
            {
                Log.e("Add Error", e.toString());
                e.printStackTrace();
            }

                                }

                                     );

// function of insertion

public void addValues(String Col1, String Col2,
                          String col3)

{

ContentValues values = new ContentValues();




        values.put(Columns1, Col1);
        values.put(Columns2, Col2);
        values.put(Columns3, Col3);

try{
            db.insert(Course_Info_Table, null, values);


}
        catch(Exception e)
        {

        }

}

Answer 3

use ContentValues to insert data into table like this.

SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();

        cv.put("_id", 1);
        cv.put("provider_id", 600);
        cv.put("parent_id", 0);

        cv.put("title", "Источники оплаты");
        cv.put("image", "");
        cv.put("sh_n", 1);
        cv.put("enable", 1);
        cv.put("visible", 0);
        cv.put("image_bg", "");
        cv.put("type", "");
        cv.put("is_group", 1);
        cv.put("hide_in_search", 1);
        cv.put("limit_min", 0);
        cv.put("limit_max", 0);
        cv.put("off_on_line", 0);
        cv.put("invoice_search", 0);
        db.insertOrThrow(TABLE_PROVIDERS, null, cv);
          db.close();

for multiple records call this methods multiple times.

public void insertMydata(int _id, int provider_id, int parent_id /*....add more parameters as you required */){

                SQLiteDatabase db = this.getWritableDatabase();
            ContentValues cv = new ContentValues();

            cv.put("_id", _id);
            cv.put("provider_id", provider_id);
            cv.put("parent_id", parent_id);
               /*
                 ......
                 ..... 
                 put more code here for other columns
                 ......
               */
                db.insertOrThrow(TABLE_PROVIDERS, null, cv);
                db.close();


}