解决 OleDbDataAdapter.Fill（DataTable）仅从Access数据库中的所选项目中返回一个值

[tim8w]

你好
我有以下代码：

            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();

返回数据表后，它的大小正确，可用于13个项目，但仅填充[0]索引，并使用第二个字段[Part Number]。关于为什么它无法正常工作的任何想法？

 

[跳伞]

没有看到您的数据库架构，我猜您要查询的列不存在，或者您要传递的名称与实际的列名不匹配。

 

[金西尼]

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.

最后，假设数据适配器具有接受查询和连接字符串的构造函数，则没有必要分别创建连接和数据适配器。

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]

感谢所有的建议。我最终使用了OleDataReader。这是我使用的代码：

            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()

 

[跳伞]

奇怪。我本来希望DataReader与DataAdapter具有相同的问题。为什么DataReader能够读取DataAdapter无法读取的其他列？

 

[金西尼]

奇怪。我本来希望DataReader与DataAdapter具有相同的问题。为什么DataReader能够读取DataAdapter无法读取的其他列？

考虑到数据适配器无论如何都在内部使用数据读取器，这是相当不错的。

 

[金西尼]

感谢所有的建议。我最终使用了OleDataReader。这是我使用的代码：

            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.