Android: Unable to insert data into SQLite

Question

Please have a look at the following code

Code to insert data

package com.example.esoftcallmanager;

import android.app.Activity;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.widget.Toast;

public class DatabaseHandler implements DatabaseConnector
{
    private SQLiteDatabase database;
    private String dbPath = "data//data//com.example.esoftcallmanager.sqllite//esoftDatabase";
    private static Context context;

    private static DatabaseHandler instance;

    private DatabaseHandler(Context context)
    {
        this.context = context;
    }

    @Override
    public void createConnection() 
    {

        try
        {
            database = context.openOrCreateDatabase("esoftDatabase", Context.MODE_PRIVATE, null);

            String createDatabaseQuery = "create table BranchNetwork(" +
                    "brID integer primary key autoincrement,"
                    +"city text,"
                    +"streetAddress text,"
                    +"phoneNumber1 text,"
                    +"phoneNumber2 text,"
                    +"email text"
                    +");";

            String dropDatabaseQuery = "drop table BranchNetwork";
            database.execSQL(createDatabaseQuery);


        }
        catch(SQLException sql)
        {
            Toast.makeText(context, sql.getMessage(), Toast.LENGTH_LONG).show();
        }

    }

    @Override
    public void closeConnection() {
        // TODO Auto-generated method stub

    }

    @Override
    public String getPhoneNumber() {
        // TODO Auto-generated method stub
        return null;
    }

    public static DatabaseHandler getInstance(Context context)
    {
        if(instance==null)
        {
            instance = new DatabaseHandler(context);
        }

        return instance;
    }

    public static DatabaseHandler getInstance()
    {
        if(instance==null)
        {
            instance = new DatabaseHandler(context);
        }

        return instance;
    }

    @Override
    public String insertData(String city, String streetAddress,String phoneNumber1, String phoneNumber2, String email) 
    {
        String insertQuery = "insert into BranchNetwork ('city','streetAddress','phoneNumber1','phoneNumber2','email') values("+city+","+streetAddress+","+phoneNumber1+","+phoneNumber2+","+email+");";

        try
        {
            database.execSQL(insertQuery);
            return "Data Successfully Inserted";
        }
        catch(Exception e)
        {
            Toast.makeText(context, "Exception: "+e.getMessage(), Toast.LENGTH_LONG).show();
            return "Data Insertion Failed";
        }

    }


}

Whenever I try to insert data, I get the following error

   02-13 23:39:40.424: E/Database(655): Failure 1 (near "@wewe": syntax error) on 0x2a6128 when preparing 'insert into BranchNetwork ('city','streetAddress','phoneNumber1','phoneNumber2','email') values(asas,asasd,3434,3434,[email protected]);'.

What is wrong here? How can I correct it? Please help!

Sam · Accepted Answer · 2013-02-13 18:24:00Z

2

Quite simply, you need to wrap your Strings in quotes.

"values('"+city+"','"+streetAddress+"'..."

However, it is often better to use a parameterized query when working with raw values to prevent SQL injection attacks and other unsavory things:

db.rawQuery("insert into Foo (bar) values (?)", new String[] {city});

Or use the built-in methods like SQLiteDatabase#insert()

edited Feb 13, 2013 at 18:24

answered Feb 13, 2013 at 18:15

Sam

87k21 gold badges185 silver badges180 bronze badges

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

2 Comments

WarrenFaith Over a year ago

though it works the way, a raw query shouldn't be used to insert data. A raw query should only be the last resort...

Sam Over a year ago

Glad I could help! It's a shame someone else disagreed and gave me a downvote... Oh well.

hardartcore · Accepted Answer · 2013-02-13 18:17:05Z

2

Try to use this SQL statement :

String insertQuery = "INSERT INTO BranchNetwork ('city','streetAddress','phoneNumber1','phoneNumber2','email') VALUES('"+city+"','"+streetAddress+"','"+phoneNumber1+"','"+phoneNumber2+"','"+email+"');";

When I am using this kind of sql statements I have problem with the ' very often .

You can use ContentValues for insert / update / delete too. I prefer it instead of writing SQL statements like that. In your situation you can do this :

ContentValues values = new ContentValues();
values.put("city", city);
values.put("streetAddress",streetAddress);
values.put("phoneNumber1",phoneNumber1); // and ETC.
//INSERT 
sqliteDb.insert("BranchNetwork", null, values);

answered Feb 13, 2013 at 18:17

hardartcore

17.1k12 gold badges78 silver badges102 bronze badges

1 Comment

PeakGen Over a year ago

Great! Thank you a lot for letting me know another way! +1 from me!

WarrenFaith · Accepted Answer · 2013-02-13 18:17:43Z

2

The following will not work:

String insertQuery = "insert into BranchNetwork ('city','streetAddress','phoneNumber1','phoneNumber2','email') values("+city+","+streetAddress+","+phoneNumber1+","+phoneNumber2+","+email+");";

You shouldn't create your own insert SQL, instead use ContentValues and just use something like that to insert:

db.insert(TABLENAME, null, cv);

answered Feb 13, 2013 at 18:17

WarrenFaith

57.7k25 gold badges139 silver badges153 bronze badges

1 Comment

PeakGen Over a year ago

Thank you a lot for providing me another approach! +1 from me!

Collectives™ on Stack Overflow

Android: Unable to insert data into SQLite

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related