Resolved OleDbDataAdapter.Fill(DataTable) only returning one value from the selected items from an Access Database

tim8w

Well-known member
Joined
Sep 8, 2020
Messages
129
Programming Experience
10+
Hi,
I have the following code:

OleDbDataAdapter:
            conn = new OleDbConnection("Provider=Microsoft.ACE.Oledb.12.0;Data Source='MyAccessDB.accdb';");
            conn.Open();
            da = new OleDbDataAdapter("SELECT [Date], [Part Number], [Work Center], [Operation #], [Discr 1], [Comm 1], [Problem Code1], [Discr 2], [Problem code 2], [discr 3], [Problem code 3], [discr 4], [problem code 4] FROM RECORDS", conn);

            da.Fill(dt);
            da.Dispose();
            conn.Close();

When the DataTable is returned, it is dimensioned correctly for 13 items, but only the [0] index is populated and with the second field [Part Number]. Any ideas on why it's not working correctly?
 
Without seeing your database schema, I would guess that the columns you are asking for don't exist or the names you are passing in don't match the actual column names.
 
OT, there's no need to open and close the connection. The Fill and Update methods of a data adapter will do that automatically. You only need to do it manually if you are making calls on multiple data adapters, so that the connection doesn't get closed and reopened in between.

Also, you should not be disposing things explicitly like that. If you create, use and dispose an object in a narrow scope like, you should be creating it with a using statement, in which case it will be disposed implicitly at the end of the block.

Finally, there's no point creating the connection and data adapter separately, given that the data adapter has a constructor that takes a query and a connection string.
C#:
using (var adapter = new OleDbDataAdapter("Provider=Microsoft.ACE.Oledb.12.0;Data Source='MyAccessDB.accdb';",
                                          "SELECT [Date], [Part Number], [Work Center], [Operation #], [Discr 1], [Comm 1], [Problem Code1], [Discr 2], [Problem code 2], [discr 3], [Problem code 3], [discr 4], [problem code 4] FROM RECORDS"))
{
    adapter.Fill(dt);
}
 
Thanks for all the suggestions. I ended up using OleDataReader. Here's the code I used:

C#:
            conn = new OleDbConnection("Provider=Microsoft.ACE.Oledb.12.0;Data Source='MyAccessDB.accdb';");
            string sSQL = "SELECT [Date], [Part Number], [Work Center], [Operation #], [Discr 1], [Comm 1], [Problem Code1], [Discr 2], [Problem code 2], [discr 3], [Problem code 3], [discr 4], [problem code 4] FROM RECORDS";
            cmd = new OleDbCommand(sSQL, conn);
            conn.Open();
            OleDbDataReader dr = cmd.ExecuteReader()
 
Strange. I would have expected the DataReader to have the same problem as the DataAdapter. Why would the DataReader be able to read the other columns that the DataAdapter could not?
 
Strange. I would have expected the DataReader to have the same problem as the DataAdapter. Why would the DataReader be able to read the other columns that the DataAdapter could not?
Quite so, given that a data adapter uses a data reader internally anyway.
 
Thanks for all the suggestions. I ended up using OleDataReader. Here's the code I used:

C#:
            conn = new OleDbConnection("Provider=Microsoft.ACE.Oledb.12.0;Data Source='MyAccessDB.accdb';");
            string sSQL = "SELECT [Date], [Part Number], [Work Center], [Operation #], [Discr 1], [Comm 1], [Problem Code1], [Discr 2], [Problem code 2], [discr 3], [Problem code 3], [discr 4], [problem code 4] FROM RECORDS";
            cmd = new OleDbCommand(sSQL, conn);
            conn.Open();
            OleDbDataReader dr = cmd.ExecuteReader()
You should still be taking my advice about using statements. The data reader should be created with a using statement, so it is implicitly closed when you're done with it. ALL objects whose type implements IDisposable should be created with a using statement unless they need to used outside the scope they are created in, which generally means that they need to be accessed in methods other than the one they were created in.
 
Back
Top Bottom