错误:操作必须使用可更新的查询
错误:操作必须使用可更新的查询
提问于 2019-04-08 04:44:49
我创建了一个程序，它将入住酒店并更新后端。我在连接和更改房间桌上的UserID时遇到问题。表包括：
用户：
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#代码
Form1
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;
}
}CheckIn用户控件
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);
}回答 3
Stack Overflow用户
回答已采纳
发布于 2019-04-08 05:30:19
与其使用子查询，不如尝试如下所示
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];Stack Overflow用户
发布于 2019-04-08 08:37:50
首先，我修改了Query2存储过程。传递用户ID和房间号(将其更改为number数据类型，以便与表定义匹配(不是您问题中的缩写)。)
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);
}Stack Overflow用户
发布于 2019-04-08 16:55:33
请尝试下面的选项，它应该可以工作
UPDATE Rooms A,(SELECT UserID FROM Users WHERE Username = 'Test') B SET A.UserID = B.UserID WHERE Room_Number=1;
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55563529
复制相关文章
相似问题