错误:操作必须使用可更新的查询

Question

我创建了一个程序，该程序将入住酒店并更新后端。我在连接和更改会议室桌上的用户 ID 时遇到问题。表格是：

---

用户：

UserID - PK, Auto Number
Username - Short Text
Password - Short Text

---

房间

RoomID - PK, Auto Number
Room_Number - Number
UserID - FK, Number

---

可更新查询

PARAMETERS parUsername Short, parRoom_Number Short;
UPDATE Rooms SET UserID = (SELECT UserID FROM Users WHERE Username = [parUsername])
WHERE Room_Number=[parRoom_Number];

---

C# 代码

表格1

MessageBox.Show("Login Successful");
Home home = new Home();
home.Show();
home.LabelText = this.txtUsername.Text;

---

主页表格

public string LabelText
        {
            get
            {
                return this.lblUsername.Text;
            }
            set
            {
                this.lblUsername.Text = value;
            }
        }

---

签入用户控件

 Home home = new Home();
            try
            {
                string ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "/Hotel.accdb";
                using (var con = new OleDbConnection(ConnString))
                {
                    con.Open();
                    using (var cmd = new OleDbCommand())
                    {
                        cmd.Connection = con;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Query2";
                        cmd.Parameters.Add("parUsername", home.LabelText);
                        cmd.Parameters.Add("parRoom_Number", lbRooms.SelectedItem);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

Answer 1

不要使用子查询，请尝试以下操作

PARAMETERS parUsername Text ( 255 ), parRoom_Number Long;
UPDATE Rooms, Users SET Rooms.UserID = Users.UserID
WHERE Rooms.Room_Number = [parRoom_Number] AND Users.Username = [parUsername];

Answer 2

首先，我更改了 Query2 存储过程。传递用户 ID 和房间号(将其更改为数字数据类型以匹配表定义(而不是像您的问题中那样的短。

PARAMETERS parUserID Number, parRoom_Number Number;
UPDATE Rooms SET UserID = [parUserID]
WHERE Room_Number=[parRoom_Number];

然后，我将代码修改为单独的查询以获取 UserId。

        Home home = new Home();
        int userID;
        try
        {
            string ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "/Hotel.accdb";
            using (var con = new OleDbConnection(ConnString))
            {
                con.Open();
                //Passing the query and the connection directly to the constructor
                using (OleDbCommand cmd1 = new OleDbCommand("SELECT UserID FROM Users WHERE Username = @UserName;", con))
                {
                    cmd1.Parameters.Add("@UserName", OleDbType.VarChar).Value = home.LabelText;
                    userID = (int)cmd1.ExecuteScalar();
                }
                using (var cmd = new OleDbCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Query2";
                    cmd.Parameters.Add("parUserID", OleDbType.Numeric).Value = userID;
                    cmd.Parameters.Add("parRoom_Number",OleDbType.Numeric ).Value = (int)lbRooms.SelectedItem;
                    cmd.ExecuteNonQuery();
                }
            }
         }
         catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

Answer 3

请尝试

以下选项，它应该可以工作

UPDATE Rooms A,(SELECT UserID FROM Users WHERE Username = 'Test') B SET A.UserID = B.UserID WHERE Room_Number=1;