获取值回从Access数据库OleDbDataReader阅读 [英] Getting values back from OleDbDataReader reading from Access database

问题描述

在它下面我使用连接到一个Access数据库和查询拉出值的代码。问题是..我不能让任何值从读者对象返回。我可以看到有正确的行数量，但是我不断收到一个InvalidOperationException（我是使用的GetValue（）或GetString的（））说：该行/列不存在数据。

  System.Data.OleDb.OleDbConnection康恩=新的
 System.Data.OleDb.OleDbConnection（）; 
 conn.ConnectionString = @供应商=的Microsoft Office 12.0 Access数据库引擎OLE DB提供程序; + 
 @数据来源= C：\Users\\\
earod\Desktop\ImportDB.accdb 
试
 {
 conn.Open（）; 
 OleDbCommand的CMD =新的OleDbCommand（SELECT * FROM [SQL代理的唯一ID测试负载]，conn）;在
 
 OleDbDataReader读卡器= cmd.ExecuteReader（）; 
 
串企业编码= reader.GetValue（0）的ToString（）; 
串的agentId = reader.GetString（1）; 
串的firstName = reader.GetString（2）; 
字符串的lastName = reader.GetString（3）; 
串nameSuffix = reader.GetString（4）; 
串corporateName = reader.GetString（5）; 
串的EntityType = reader.GetString（6）; 
串obfSSN = reader.GetString（7）; 
串obfFEIN = reader.GetString（8）; 
串dummyIndi​​cator = reader.GetString（9）; 
 //插入代码来处理数据。 
} 
赶上（异常前）
 {
 MessageBox.Show（无法连接到数据源）; 
} 
终于
 {
 conn.Close（）; 
} 
  

解决方案

你必须调用Read方法如下图所示（使用用途，而不是处理自己的连接

 字符串的connectionString = @供应商=的Microsoft Office 12.0 Access数据库引擎OLE DB提供程序;+ @数据来源= C：\Users\\\
earod\Desktop\ImportDB.accdb; 
 
字符串的queryString =SELECT * FROM [SQL代理的唯一ID测试负载] ; 
试
 {
使用（OleDbConnection的连接=新的OleDbConnection（的connectionString））
 {
 OleDbCommand的命令=新的OleDbCommand（的queryString，连接）; 
 connection.Open（）; 
 OleDbDataReader读卡器= Command.ExecuteReader却（）; 
 
，而（reader.Read（））
 {
串企业编码= reader.GetValue （0）的ToString（）; 
串的agentId = reader.GetString（1）; 
串的firstName = reader.GetString（2）; 
串的lastName = reader.GetString（3）; 
串nameSuffix = reader.GetString（4）; 
串corporateName = reader.GetString（5）; 
串的EntityType = reader.GetString（6）; 
串obfSSN = reader.GetString（7）; 
串obfFEIN = reader.GetString（8）; 
串dummyIndi​​cator = reader.GetString（9）; 
 //插入代码来处理数据。 
} 
 reader.Close（）; 
} 
} 
赶上（异常前）
 {
 MessageBox.Show（无法连接到数据源）; 
} 
  

Below it the code I'm using to connect to an Access database and pull the values from the query. Problem is.. I cannot get any values back from the reader object. I can see that there are the correct amount of rows, however I keep getting an InvalidOperationException (whether I use GetValue() or GetString()) saying "No data exists for the row/column."

        System.Data.OleDb.OleDbConnection conn = new
        System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;" +
                                @"Data source= C:\Users\nearod\Desktop\ImportDB.accdb";
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [SQL Agent Unique ID Test Load]", conn);

            OleDbDataReader reader = cmd.ExecuteReader();

            string companyCode = reader.GetValue(0).ToString();
            string agentId = reader.GetString(1);
            string firstName = reader.GetString(2);
            string lastName = reader.GetString(3);
            string nameSuffix = reader.GetString(4);
            string corporateName = reader.GetString(5);
            string entityType = reader.GetString(6);
            string obfSSN = reader.GetString(7);
            string obfFEIN = reader.GetString(8);
            string dummyIndicator = reader.GetString(9);
            // Insert code to process data.
        }
        catch (Exception ex)
        {
            MessageBox.Show("Failed to connect to data source");
        }
        finally
        {
            conn.Close();
        }

解决方案

you have to call Read method like below (use using instead of disposing yourself connection

string connectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;" + @"Data source= C:\Users\nearod\Desktop\ImportDB.accdb";

string queryString=  "SELECT * FROM [SQL Agent Unique ID Test Load]";
 try
     {
    using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbCommand command = new OleDbCommand(queryString, connection);
            connection.Open();
            OleDbDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                string companyCode = reader.GetValue(0).ToString();
            string agentId = reader.GetString(1);
            string firstName = reader.GetString(2);
            string lastName = reader.GetString(3);
            string nameSuffix = reader.GetString(4);
            string corporateName = reader.GetString(5);
            string entityType = reader.GetString(6);
            string obfSSN = reader.GetString(7);
            string obfFEIN = reader.GetString(8);
            string dummyIndicator = reader.GetString(9);
            // Insert code to process data.
            }
            reader.Close();
        }
   }
catch (Exception ex)
   {
            MessageBox.Show("Failed to connect to data source");
   }

这篇关于获取值回从Access数据库OleDbDataReader阅读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！