SQLite Data Adapter not displaying data

Question

I'm trying to fill a data grid view in my windows form application but nothing is being returned from the database when I execute the select query. I've looked at other questions about this topic on this site but cannot find anything that addresses my problem.

The name of the data view table is qbcMemDataView and the data source is a sqlite dataset called sqlite_dbDataSet1

Here is the code I have in place:

public Form1()
{
    InitializeComponent();

    dbConnection = new SQLiteConnection("Data Source=sqlite_db.sqlite;Version=3");

    dbConnection.Open();

    string[] restrictions = new string[4];

    restrictions[2] = "test_table_mom";

    using (DataTable dTbl = dbConnection.GetSchema("Tables", restrictions))
    {
        for (int i = 0; i < dTbl.Rows.Count; i++)
        {
            tblChooser.Items.Add(dTbl.Rows[i].ItemArray[dTbl.Columns.IndexOf("TABLE_NAME")].ToString());
        }

        if (tblChooser.Items.Count > 0)
        {
            tblChooser.SelectedIndex = 0;
        }
    }  
}

private void btnSelect_tbl_Click(object sender, EventArgs e)
{

    string sql = "SELECT id, name FROM test_table_mom";

    using (SQLiteDataAdapter dbAdapter = new SQLiteDataAdapter(sql, dbConnection))
    {
        DataTable dataTbl = new DataTable();

        dbAdapter.Fill(dataTbl);

        qbcMemDataView.DataSource = dataTbl;
    }
}

Also, here is a screenshot of the program running that might help better explain the issue I am having: http://imgur.com/j9ffeVi

I know there is data inside the table, I just don't know why it is not appearing in the data grid when the btnSelect_tbl_Click method is executed.

Any help would be appreciated.

Thanks!

Answer 1

Per the tutorial How to: Bind Data to the Windows Forms DataGridView Control, you are missing a BindingSource component that binds the data from the datasource to your table to the DataGrid.

Initialize the BindingSource at the top of your class like so:

private BindingSource bindingSource1 = new BindingSource();

Then near the top of your button click method before the sql add the line:

qbcMemDataView.DataSource = bindingSource1;

and finally change the last line of code

qbcMemDataView.DataSource = dataTbl;

to

bindingSource1.DataSource = dataTbl;

try that and see if it works for you.

Answer 2

Note: I'm not sure if this applies to c# but maybe it's universal fix.

Android builtin adapters and such use _id as the name of the id field. The other problem is _id and id well it's not well documented in android.

About "_id" field in Android SQLite

You can use this technique renaming in the select but it gets messy and you may not catch all occurrences.

string sql = "SELECT id _id, name FROM test_table_mom";

My Opinion: Go back and refactor your db id to _id.