在 C# 中使用 MS Access 查找字段 [英] Using an MS Access Lookup field in C#

问题描述

我正在构建对现有 Access 数据库的扩展以及一个用 C# 编程的附带前端.最初的 Access 数据库设计得不是很好，当然也没有考虑到未来的扩展.为简单起见，假设旧数据库有 2 个表:tblEmployee [empId(AutoNumber), empName(Text)] 和 tblProjects [prjId(AutoNumber), prjName(Text), prjEmps(号码/查找)].两个表都有一个 AutoNumber 主键.Projects 表有一个多值查找字段，允许用户将多个员工分配给一个项目.当我在 Access SELECT prjId, prjName, prjEmps FROM tblProject; 中查询 tblProjects 表时，prjEmps 字段列出了所有以逗号分隔的员工姓名.但是，问题是当我在 C# 中使用相同的查询时，prjEmps 返回一个数字的字符串版本，它不是员工的 empId.我不确定它是否有所不同，但我在 C# 中使用了 System.Data.OleDb 和 System.Data 命名空间.这是我的 C# 代码的要点:

I am building an extension to an existing Access database and an accompanying front end programmed in C#. The original Access database was not designed very well and certainly not designed with future expansion in mind. For simplicity's sake, lets say the legacy DB has 2 tables: tblEmployee [empId(AutoNumber), empName(Text)] and tblProjects [prjId(AutoNumber), prjName(Text), prjEmps(Number/Lookup)]. Both tables have an AutoNumber primary key. The Projects table has a multi-value lookup field that allows users to assign multiple employees to a project. When I query the tblProjects table in Access SELECT prjId, prjName, prjEmps FROM tblProject;, the prjEmps field lists all the employees' names separated by commas. However, the problem is when I use the same query in C#, the prjEmps returns a string version of a number that is not the empId of the employee(s). I am not sure if it makes a difference, but I am using the System.Data.OleDb and System.Data namespaces in C#. Here is the gist my C# code:

    string connStr = @"Provider = Microsoft.ACE.OLEDB.12.0; " +
        @"Data Source=" + dbFilePath;
    string query = "SELECT prjId, prjName, prjEmps FROM tblProject;";
    OleDbConnection dbConn = new OleDbConnection(connStr);
    OleDbCommand Cmd = new OleDbCommand(query, dbConn);
    OleDbDataAdapter adp = new OleDbDataAdapter(query, dbConn);
    DataTable dt = new DataTable();
    adp.Fill(dt);
    dbConn.Close();
    foreach (DataRow row in dt.Rows)
    {
        int prjId = row.Field<int>("prjId");
        string prjName = row.Field<string>("prjName");
        string prjEmps = row.Field<string>("prjEmps");
            MessageBox.Show("Project ID: " + prjId.ToString() + "\n" +
                            "Project Name: " + prjName + "\n" +
                            "Employees: " + prjEmps);
    }

如果我能得到名字的串联列表，我会很高兴，但我更喜欢整数键或类似的数组.有关如何解决此问题的任何想法?

I would be happy if I could just get the concatenated list of names, but I would prefer an array of integer keys or the like. Any ideas on how to fix this?

推荐答案

使用 ODBC 提供程序，OLEDB 不支持多值查找字段，如果使用它读取多值查找字段，则会得到垃圾值，使用 ODBC 你会得到;"分隔值，然后可以将其拆分为单个值或替换为，".

Use ODBC provider, OLEDB does not supports multi-value lookup field and you get garbage values if you use it to read multi-value lookup field , using ODBC you will get ";" separated values which can then be split into individual values or replace with ",".

这篇关于在 C# 中使用 MS Access 查找字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！