SQLite (PCL) query in Windows XAML not allowing more than 21 columns?

Question

I've used SQLite-PCL in my Universal Windows 8.1 RunTime app.
There's a big table with 23 columns in my project. The problem is - though table-creation & data-insertion codes run without any error, but in the full-column query (SELECT * FROM table) is not giving me more than 21 columns.

Here's my create-table method (it runs OK):

    private void CreateTable()
    {
        string CREATE_TABLE_SQL = @"CREATE TABLE IF NOT EXISTS "
                + TABLE_NAME
                + "( "
                + KEY_ID + " INTEGER PRIMARY KEY, " // 0
                + KEY_UPDATED_AT + " TEXT, "        // 1
                + KEY_CREATED_AT + " TEXT, "        // 2

            // ... all other column names are stated here in the similar way

                + KEY_LAST_EDITED_BY + " INTEGER, " // 20
                + KEY_LAST_EDIT_TIME + " TEXT, "    // 21
                + KEY_IS_LD + " INTEGER "        // 22
                + ");";
        using (var connection = new SQLiteConnection(DB_NAME))
        {
            using (var statement = connection.Prepare(CREATE_TABLE_SQL))
            {
                statement.Step();
            }
        }
    }

Here's my row-insertion SQL query (it runs OK too):

string insQuery = 
       @"INSERT INTO " + TABLE_NAME
          + " ( " + KEY_ID + ", " //1
          + KEY_UPDATED_AT + ", "//2
          + KEY_CREATED_AT + " , "//3
          // all other col. names are stated here
          + KEY_LAST_EDITED_BY + ", "//21
          + KEY_LAST_EDIT_TIME + ", "//22
          + KEY_IS_LD + " "//23
          + " ) " 
          + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

But if I query all the rows or a specific row with all columns, the prepared statement can't go beyond index 20, hence I'm assuming it's not containing more than 21 columns inside it. Here's the code which doesn't run OK & gives NullReferenceException during access of 21th item:

public List<MyModel> GetAll()
{
    List<MyModel> objList = new List<MyModel>();

    string query = @"SELECT * FROM " + TABLE_NAME
                        + " ORDER BY " + KEY_ID + " DESC;";
    using (var connection = new SQLiteConnection(DB_NAME))
    {
        using (var statement = connection.Prepare(query))
        {
            // The next line prints this: "Statement data-count=0, ColumnCount=23"
            ALog.d("Statement data-count=" + statement.DataCount + 
                    ", ColumnCount=" + statement.ColumnCount); 
            while (statement.Step() == SQLiteResult.ROW)
            {
                try
                {
                    int id = Int32.Parse(statement[0].ToString());
                    string updatedAt = statement[1].ToString();
                    string createdAt = statement[2].ToString();
                    // ... all other values are extracted here, then I'm building
                    //  my user object & the next line prints my expected values
                    ALog.d("User: " + user.ToString());

                    // ... the remaining columns are got here nicely
                    string imgUrl = statement[19].ToString();
                    int lastEdt = Int32.Parse(statement[20].ToString());

                    // This line is the culprit giving out NullReferenceException >_<
                    string lastEdtTm = statement[21].ToString();
                    bool isLd = Int32.Parse(statement[22].ToString()) > 0;

                    objList.Add(new MyModel(
                        // Params. of the constructor goes here ...
                     ));
                }
                catch (Exception e)
                {
                    ALog.d("Db - GetAll() : Exception:: " + e.ToString());
                }
            }

            // This line prints as: "Statement data-count=23, ColumnCount=23"
            // That means - all my data & columns have been queried,
            // but I couldn't read values from statement[21] & statement[22]
                ALog.d("Statement data-count=" + statement.DataCount + 
                        ", ColumnCount=" + statement.ColumnCount);
                statement.Reset();
                statement.ClearBindings();
        }
    }

    return objList;
}

Please note that, I've more than 10 tables in the project with less than 17 columns & all of those are working fine.

Answer 1

The problem is related to lines like this:

string lastEdtTm = statement[21].ToString();

Here, if statement[21] returns a null value, then that is equivalent to null.ToString(), which will throw an exception.

Easy fix:

string lastEdtTm = statement[21] == null ? "" : statement[21].ToString();

This will return an empty string if statement[21] resolves to null, or it`s string value otherwise.

Note that you should do this for all columns that may potentially return null - just because you're not getting that exception other places right now, does not mean you might not get it later when new rows are added, which may be missing other values.