Insert null in Sqlite table

Question

I am trying to figure how to insert a null value into an SQLite table in Android.

This is the table:

"create table my table (_id integer primary key autoincrement, " +
                                               "deviceAddress   text not null unique, " +
                                               "lookUpKey text , " + 
                                               "deviceName text , " +
                                               "contactName text , " +
                                               "playerName text , " +
                                               "playerPhoto blob " +
                                               ");";

I wanted to use a simple Insert command via execSQL but since one of the values is a blob I can't do it (I think).

So, I am using a standard db.Insert command. How do I make one of the values null?

If I just skip it in the ContentValues object will it automatically put a null value in the column?

sujith · Accepted Answer · 2013-04-25 06:52:00Z

81

You can use ContentValues.putNull(String key) method.

edited Apr 25, 2013 at 6:52

answered May 24, 2012 at 7:58

sujith

2,4212 gold badges17 silver badges27 bronze badges

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

Comments

c-romeo · Accepted Answer · 2017-12-19 18:08:57Z

35

Yes, you can skip the field in ContentValues and db.insert will set it NULL. Also you can use the other way:

ContentValues cv = new ContentValues();
cv.putNull("column1");
db.insert("table1", null, cv);

this directrly sets "column1" NULL. Also you can use this way in update statement.

edited Dec 19, 2017 at 18:08

c-romeo

4285 silver badges15 bronze badges

answered Mar 23, 2012 at 6:24

redandblack

5263 silver badges5 bronze badges

5 Comments

mSafdel Over a year ago

you can't send null to put method, instead you must use putNull method

Aksel Fatih Over a year ago

Actually the answer is not wrong. When you checked the source code of the ContentValues.java which is located at the android.content package, it can be seen that; the method simply calls put method with a supplied null argument. grepcode.com/file/repository.grepcode.com/java/ext/…

samus Over a year ago

public void putNull(String key) { mValues.put(key, null); } ... I suppose putNull's implementation could change, so it is probably better to use it rather than put directly. Nonetheless, it's still a correct answer.

vovahost Over a year ago

@Samus Arin I think putNull(String key) is a convenience method to set a Column value to null and it's implementation shouldn't change.

vapcguy Over a year ago

For me, when I tried to skip a date field, even though the field was not required to be NOT NULL, I had issues trying to do inserts using C# into SQLite, giving an error that String was not recognized as a valid DateTime. Maybe Android code is different. But I had to insert null in my INSERT command string, instead.

Lior Iluz · Accepted Answer · 2012-03-22 13:45:59Z

1

I think you can skip it but you can also put null, just make sure that when you first create the Database, you don't declare the column as "NOT NULL".

answered Mar 22, 2012 at 13:45

Lior Iluz

26.6k16 gold badges68 silver badges116 bronze badges

1 Comment

vapcguy Over a year ago

For me, when I tried to skip a date field, even though the field was not required to be NOT NULL, I had issues trying to do inserts using C# into SQLite, giving an error that String was not recognized as a valid DateTime. Maybe Android code is different. But I had to insert null in my INSERT command string, instead.

vapcguy · Accepted Answer · 2016-12-08 22:02:01Z

In your insert query string command, you can insert null for the value you want to be null. This is C# as I don't know how you set up database connections for Android, but the query would be the same, so I've given it for illustrative purposes I'm sure you could equate to your own:

SQLiteConnection conn = new SQLiteConnection(SQLiteConnString()); 
// where SQLiteConnString() is a function that returns this string with the path of the SQLite DB file:
// String.Format("Data Source={0};Version=3;New=False;Compress=True;", filePath);

try 
{
    using (SQLiteCommand cmd = conn.CreateCommand()
    {
        cmd.CommandText = "INSERT INTO MyTable (SomeColumn) VALUES (null)";
        cmd.ExecuteNonQuery();
    }
}
catch (Exception ex)
{
    // do something with ex.ToString() or ex.Message
}

Collectives™ on Stack Overflow

Insert null in Sqlite table

4 Answers 4

Comments

5 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related