Asp.net中的SqlException - 'Van‘附近的语法不正确

Question

我的SqlCommand有问题，只要我打开页面，我就会得到这个错误：

System.Data.SqlClient.SqlException: Incorrect syntax near 'Van'.

我找不到问题，因为'Van‘只在整个项目中出现过一次，而且在标题中也是如此。这是我在Page_Load中的代码：

   using (SqlConnection con = new SqlConnection(RoleEnvironment.GetConfigurationSettingValue("DatabaseConnectionString")))
            {
                var cmd = new SqlCommand("SELECT (SELECT Memo_ID, Dep_Name FROM Department WHERE (Department_ID = Staff.Depar_ID)) AS DepartmentName FROM Staff WHERE (FirstName + SPACE(1) + LastName = " + User.Identity.Name, con);
                cmd.Connection.Open();
                var sqlReader = cmd.ExecuteReader();
                while (sqlReader.Read())
                {

                    String result = sqlReader.GetString(0);
                    DropDownList1.DataBind();
                    DropDownList1.Items.FindByValue(result).Selected = true;
                    //Fill some data like : string result = sqlReader("SomeFieldName");
                }
                sqlReader.Close();
                cmd.Connection.Close();
                cmd.Dispose();
            }

数据库连接字符串是正确的，因为它适用于我的所有其他页面。我正在尝试获取员工工作的部门，以便他/她只能查看自己部门的备忘录。

Answer 1

您需要在提供的姓氏后面加上括号。

SELECT (SELECT Memo_ID, Dep_Name FROM Department 
WHERE (Department_ID = Staff.Depar_ID)) AS DepartmentName 
FROM Staff WHERE (FirstName + SPACE(1) + LastName = 'xxx' )

下面是它应该是什么样子：

  using (SqlConnection con = new SqlConnection(RoleEnvironment.GetConfigurationSettingValue("DatabaseConnectionString")))
        {
            var cmd = new SqlCommand("SELECT (SELECT Memo_ID, Dep_Name FROM Department WHERE (Department_ID = Staff.Depar_ID)) AS DepartmentName FROM Staff WHERE (FirstName + SPACE(1) + LastName = '" + User.Identity.Name + "')", con);
            cmd.Connection.Open();
            var sqlReader = cmd.ExecuteReader();
            while (sqlReader.Read())
            {

                String result = sqlReader.GetString(0);
                DropDownList1.DataBind();
                DropDownList1.Items.FindByValue(result).Selected = true;
                //Fill some data like : string result = sqlReader("SomeFieldName");
            }
            sqlReader.Close();
            cmd.Connection.Close();
            cmd.Dispose();

Answer 2

你需要引用你的姓氏。您可能也希望转换为参数化查询。

Answer 3

我本以为您的WHERE子句会用引号将User.Identity.Name括起来：

WHERE (FirstName + SPACE(1) + LastName = '" + User.Identity.Name + "'" ...

"van“会在用户名中吗？

这也不是一个非常安全的查询-但是SQL注入是另一个问题！