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

tim8w

Active member
Joined
Sep 8, 2020
Messages
36
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?
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,135
Location
Chesapeake, VA
Programming Experience
10+
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.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,277
Location
Sydney, Australia
Programming Experience
10+
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);
}
 

tim8w

Active member
Joined
Sep 8, 2020
Messages
36
Programming Experience
10+
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()
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,135
Location
Chesapeake, VA
Programming Experience
10+
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?
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,277
Location
Sydney, Australia
Programming Experience
10+
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.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,277
Location
Sydney, Australia
Programming Experience
10+
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.
 
Top Bottom