带有或不带有括号的MS Access Sql查询错误

Question

我通常在C#中使用SQL Server Express数据库，但需要在另一个应用程序中使用MS Access数据库复制一些查询。

在我的一个方法中，我使用INNER JOIN来连接2个表。当我运行我的原始查询，即下面显示的第一个查询时，它会抛出以下错误。

IErrorInfo.GetDescription失败，返回E_FAIL(0x80004005(。

在调查这个错误时，它建议我在列名(甚至可能是表名(周围加上括号。

但是，当代码到达我的OleDbDataReader时，它会抛出一个不同的错误"列名的无效方括号"。

我已经尝试了三次更改查询的结构，如版本#2和#3所示。然而，它仍然会出错。

请建议形成此参数化查询的正确方法。原始查询#1

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmdGetPhone = new OleDbCommand(
"SELECT s.GroupName, s.SpkrName, s.SpkrHomePhone, s.SpkrCellPhone, aIN.SpkrIN, aIN.GroupIN FROM Speakers AS s INNER JOIN AssignmentsIN AS aIN ON aIN.SpkrIN = s.SpkrName AND aIN.GroupIN = s.GroupName AND s.SpkrName = @SpkrName AND s.Local = @Local AND s.Visiting = @Visiting", conn))
{
cmdGetPhone.Parameters.Add("@SpkrName", OleDbType.VarWChar).Value = speaker;
cmdGetPhone.Parameters.Add("@GroupIN", OleDbType.VarWChar).Value = group;
cmdGetPhone.Parameters.Add("@Local", OleDbType.VarWChar).Value = local;
cmdGetPhone.Parameters.Add("@Visiting", OleDbType.VarWChar).Value = visiting;
using (OleDbDataReader reader = cmdGetPhone.ExecuteReader())
{
while (reader.Read())

查询#2

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmdGetPhone = new OleDbCommand(
"SELECT [Speakers.GroupName], [Speakers.SpkrName], [Speakers.SpkrHomePhone], [Speakers.SpkrCellPhone], [AssignmentsIN.SpkrIN], [AssignmentsIN.GroupIN] FROM Speakers INNER JOIN AssignmentsIN ON [AssignmentsIN.SpkrIN] = [Speakers.SpkrName] AND [AssignmentsIN.GroupIN] = [Speakers.GroupName] AND [Speakers.SpkrName] = @SpkrName AND [Speakers.Local] = @Local AND [Speakers.Visiting] = @Visiting", conn))
{
cmdGetPhone.Parameters.Add("@SpkrName", OleDbType.VarWChar).Value = speaker;
cmdGetPhone.Parameters.Add("@GroupIN", OleDbType.VarWChar).Value = group;
cmdGetPhone.Parameters.Add("@Local", OleDbType.VarWChar).Value = local;
cmdGetPhone.Parameters.Add("@Visiting", OleDbType.VarWChar).Value = visiting;
using (OleDbDataReader reader = cmdGetPhone.ExecuteReader())
{
while (reader.Read())

查询#3

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmdGetPhone = new OleDbCommand(
"SELECT s.GroupName, s.SpkrName, s.SpkrHomePhone, s.SpkrCellPhone, aIN.SpkrIN, aIN.GroupIN FROM (Speakers AS s INNER JOIN AssignmentsIN AS aIN ON aIN.SpkrIN = s.SpkrName AND aIN.GroupIN = s.GroupName AND s.SpkrName = @SpkrName AND s.Local = @Local AND s.Visiting = @Visiting)", conn))
{
cmdGetPhone.Parameters.Add("@SpkrName", OleDbType.VarWChar).Value = speaker;
cmdGetPhone.Parameters.Add("@GroupIN", OleDbType.VarWChar).Value = group;
cmdGetPhone.Parameters.Add("@Local", OleDbType.VarWChar).Value = local;
cmdGetPhone.Parameters.Add("@Visiting", OleDbType.VarWChar).Value = visiting;
using (OleDbDataReader reader = cmdGetPhone.ExecuteReader())
{
while (reader.Read())

Answer 1

您的第一个语法看起来不错。您有：

SELECT s.GroupName, s.SpkrName, s.SpkrHomePhone, s.SpkrCellPhone, aIN.SpkrIN,
aIN.GroupIN FROM Speakers AS s 
INNER JOIN AssignmentsIN AS aIN ON 
aIN.SpkrIN = s.SpkrName 
AND aIN.GroupIN = s.GroupName 
AND s.SpkrName = @SpkrName 
AND s.Local = @Local 
AND s.Visiting = @Visiting

然而，你的参数是

@SpkrName   ok
@GroupIN    hum, I don't see that in above
@Local      ok
@Visiting   ok

因此，您的第一个SQL语法看起来很好(坚持使用它——它很好(。但似乎确实增加了一个额外的参数。不确定，也许sql server不关心sql提供程序

使用oleDB提供程序？嗯，可能会有所不同。也作为参考？使用JET/ACE时，顺序很重要，所以请将它们放在sql中，并按照相同的顺序添加params。你的订单还可以(没有遗漏的(。

所以，一个帕尔姆看起来不见了。我们假设观众和花生画廊对一条可怕的错误信息的评论不会在这里发表！