指定的字段'ID'可以引用多个表

Question

当我试图通过ID:查找时，我一直收到这个错误

system.data.oledb.oledbexception指定字段"ID"可以引用到SQL语句的FROM子句中列出的多个表

这是我的代码：

public static Invoice GetInvoice(string id)
{
OleDbConnection conn = GetConnection();
Invoice invoice = null;
if (conn == null)
{
return null;
}
string sqlString = "SELECT * FROM Person INNER JOIN Employee ON " +
"Person.ID=Employee.ID WHERE ID = @ID";
OleDbCommand comm = new OleDbCommand(sqlString, conn);
comm.Parameters.AddWithValue("@ID", id);
OleDbDataReader dr = null;
try
{
conn.Open();
dr = comm.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
invoice = new Invoice();
invoice.PersonID = (string)dr["ID"];
invoice.FirstName = (string)dr["FirstName"];
invoice.LastName = (string)dr["LastName"];
invoice.Age = (int)dr["Age"];
}
}
catch (Exception ex)
{
invoice = null;
MessageBox.Show(ex.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return invoice;
}

Answer 1

您需要更改查询，此时您正在选择通配符"*"，这意味着它将提取Persons ID和Employee ID，但不会有唯一的引用。更改您的通配符以提取准确的表ID，如下所示：

SELECT Person.ID, FirstName, LastName FROM...

您还需要将WHERE语句更改为以下内容：

WHERE Person.ID = @ID

由于where语句不知道要筛选哪些表ID(我知道它们是相同的值，但SQL不在乎(