查询表达式'((Student Number = ?) AND ((? = 1 AND Name IS NULL) OR (Name = ?)))'中的语法错误(缺少运算符)

Question

错误提示:

查询表达式"((学号)"中语法错误(缺少运算符)= ?)((?= 1 AND Name = NULL) OR (Name = ?))

代码是:

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    ds.Tables("BSorg Members").Rows(inc).Item(1) = tbStudentNumber.Text()
    ds.Tables("BSorg Members").Rows(inc).Item(1) = tbName.Text()
    da.Update(ds, "BSorg Members")
  MsgBox("Data updated")
End Sub

其中tbStudentNumber.Text()在访问时显示数据"Student Number"并显示"Name"。

这是什么意思，我怎么解决它?

Answer 1

从错误消息中可以看出，您试图将字段名作为参数传递给查询。如果是这样，那就行不通了。查询参数只适用于字段(列)值，不适用于字段名称。

为了说明这一点，下面的c#代码将不能工作:

using (var con = new OleDbConnection(
            "Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=C:__tmpmain.accdb;"))
{
    con.Open();
    using (var cmd = new OleDbCommand(
                "SELECT COUNT(*) AS n FROM Table1 WHERE ? = 1", con))
    {
        cmd.Parameters.AddWithValue("?", "ID");
        int n = (int)cmd.ExecuteScalar();
        Console.WriteLine(n.ToString());
    }
    con.Close();
}

在这种情况下，

就失败了

条件表达式中的数据类型不匹配

然而，这段代码运行良好

using (var con = new OleDbConnection(
            "Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=C:__tmpmain.accdb;"))
{
    con.Open();
    using (var cmd = new OleDbCommand(
                "SELECT COUNT(*) AS n FROM Table1 WHERE ID = 1", con))
    {
        //cmd.Parameters.AddWithValue("?", "ID");
        int n = (int)cmd.ExecuteScalar();
        Console.WriteLine(n.ToString());
    }
    con.Close();
}

Answer 2

也许你错过了这个

ds.Tables("BSorg Members").Rows(inc).Item(0) = tbStudentNumber.Text
ds.Tables("BSorg Members").Rows(inc).Item(1) = tbName.Text