SQL多个更新语句将影响两个表 [英] SQL Multiple Update Statements that will affect two tables

查看：132
本文介绍了SQL多个更新语句将影响两个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的数据库中有两个表：Business和User_Acc。



商家有以下栏目：

Business_ID

Business_Name

Address_Line_1

Address_Line_2

Address_Line_3

县

普罗旺斯

Owner_Name

Vat_No

电话



User_Acc包含以下栏目：

User_ID

用户名

密码

角色

电邮

Business_ID



我有一个关系设置，通过business_ID链接两个表



我有一个使用C＃和Asp.net的webform，允许用户输入详细信息到文本框中以修改数据库中已有的记录 - 即更新业务详细信息，如果他们更改了密码，请更新User_acc表中的密码



这是我的程序但它不起作用：

 ALTER PROCEDURE dbo.SaveBusinessChanges 
（
 @Business_Name varchar （ 50 ），
 @ Address_Line_1 varchar（ 50 ），
 @ Address_Line_2 varchar （ 50 ），
 @ Address_Line_3 varchar（ 50 ），
 @County varchar （ 50 ），
 @Provence varchar（ 50 ），
 @Telephone varchar （ 50 ），
 @Username varchar（ 50 ），
 @PasswordNew varchar （ 50 ），
 @Email varchar（ 50 ）
）
 
 AS 
 BEGIN TRANSACTION 
 
 
更新
 T1 
 SET 
 T1.Business_Name = @Business_Name 
 FROM 
业务T1 
 INNER JOIN User_Acc T2 
 ON 
 T1.Business_ID = T2.Business_ID 
 WHERE 
 T2.Username = @Username 
 
 UPDATE 
 T1 
 SET 
 T1.Address_Line_1 = @ Address_Line_1 
 FROM 
业务T1 
 INNER JOIN User_Acc T2 
 ON 
 T1.Business_ID = T2.Business_ID 
 WHERE 
 T2.Username = @Username 
 
 UPDATE 
 T1 
 SET 
 T1.Address_Line_2 = @ Address_Line_2 
 FROM 
业务T1 
 INNER JOIN User_Acc T2 
 ON 
 T1.Business_ID = T2.Business_ID 
 WHERE 
 T2.Username = @Username 
 
 UPDATE 
 T1 
 SET 
 T1。 Address_Line_3 = @ Address_Line_3 
 FROM 
业务T1 
 INNER JOIN User_Acc T2 
 ON 
 T1.Business_ID = T2.Business_ID 
 WHERE 
 T2。 Username = @Username 
 
 UPDATE 
 T1 
 SET 
 T1.County = @County 
 FROM 
商业T1 
 INNER JOIN User_Acc T2 
 ON 
 T1.Business_ID = T2.Business_ID 
 WHERE 
 T2.Username = @Username 
 
 UPDATE 
 T1 
 SET 
 T1.Provence = @Provence 
 FROM 
业务T1 
 INNER JOIN User_Acc T2 
 ON 
 T1.Business_ID = T2.Business_ID 
 WHERE 
 T2.Username = @Username 
 
 UPDATE 
 T1 
 SET 
 T1.Telephone = @Telephone 
 FROM 
业务T1 
 INNER JOIN User_Acc T2 
 ON 
 T1.Business_ID = T2.Business_ID 
 WHERE 
 T2.Username = @Username 
 
 UPDATE 
 T2 
 SET 
 T2.Email = @Email 
 FROM 
 User_Acc T2 
 INNER JOIN业务T1 
 ON 
 T2 .Business_ID = T1.Business_ID 
 WHERE 
 T2.Username = @Username 
 
 UPDATE 
 T2 
 SET 
 T2.Password = @PasswordNew 
 FROM 
 User_Acc T2 
 INNER JOIN业务T1 
 ON 
 T2.Business_ID = T1.Business_ID 
 WHERE 
 T2.Username = @Username 
 
 COMMIT





记录通过Business_ID和参数@Username之间的链接来识别，该参数取值来自Session变量'User'



我应该如何解决这个问题？

解决方案

< blockquote> Ok ...找出错误的第一步尝试...

打印 @Username 的内容（如果它是空的那么它是你处理会话变量的方式）



假设没问题再运行这个sql 

 SELECT * FROM User_Acc T2 WHERE Username ='xxxxx '

将xxxxx替换为用户名的实际值。

检查是否有一行返回（如果没有则那么你的数据是错误的 - 没有匹配）



假设工作然后尝试以下sql 

 SELECT * FROM Business T1 INNER JOIN User_Acc T2 ON T1.Business_ID = T2.Business_ID



（如果没有返回任何内容，那么你的桌子的链接是有问题的）



一旦你得到它工作你需要更改过程以使其更高效（并且更容易阅读!!）...如果更新基于相同的标准，您可以一次更新多个列。

例如，所有T1更新都可以表示为

 更新 T1 
  SET  T1.Business_Name =  @ Business_Name ，Address_Line_1 =  @ Address_Line_1 ，Address_Line_2 =  @ Address_Line_2 ，
 Address_Line_3 =  @ Address_Line_3 ，县=  @County ，普罗旺斯=  @普罗旺斯，Telephone =  @电话 
  FROM 业务T1 
  INNER   JOIN  User_Acc T2  ON  T1.Business_ID = T2.Business_ID 
  WHERE  T2.Username =  @ Username







以前的注意事项 - 我无法测试这个

 protected void SaveChanges（object sender，EventArgs e）
 {
 SqlConnection conn; 
 SqlCommand comm; 
  SqlCommand comm1;  
 String connectionString = ConfigurationManager.ConnectionStrings [ApplicationServices]。ConnectionString; 
 conn = new SqlConnection（connectionString）; 
 comm = new SqlCommand（SELECT * from Business a，User_Acc c where c.Username = @Username AND c.Business_ID = a.Business_ID，conn）; 
 comm.Parameters.Add（@ Username，System.Data.SqlDbType.VarChar）.Value = Session [User]; 
 conn.Open（）; 
 SqlDataReader reader = comm.ExecuteReader（）; 
 while（reader.Read（））
 {
 if（txtOldPassword.Text.Equals（reader [Password]））
 {
 // I'使用与读取相同的连接并不舒服 - 特别是因为我们看起来
 //在读者循环中，所以我将为存储的proc更新创建一个新连接
 SqlConnection connWrite; 
 connWrite = new SqlConnection（connectionString）; 
 //有关using语句的信息，请参阅http://msdn.microsoft.com/en-us/library/htd05whh.aspx 
 using（ SqlCommand  comm1 = new SqlCommand （exec SaveBusinessChanges @ Business_Name，@ Address_Line_1，@ Address_Line_2，@ Address_Line_3，@ County，@ Provence，@ Telephone，@ Username，@ PasswordNew，@ Email，connWrite））
 {
 // Let命令知道它是运行存储过程
 comm1.CommandType = CommandType.StoredProcedure; 
 //与OP代码
 comm1.Parameters.AddWithValue（@ Business_Name，txtChangeBusinessName.Text）相比没有变化。 
 comm1.Parameters.AddWithValue（@ Email，txtChangeBusinessEmail.Text）; 
 comm1.Parameters.AddWithValue（@ Telephone，txtChangeBusinessTelephone.Text）; 
 comm1.Parameters.AddWithValue（@ Address_Line_1，txtChangeBusinessAddress.Text）; 
 comm1.Parameters.AddWithValue（@ Address_Line_2，txtChangeBusinessAddress2.Text）; 
 comm1.Parameters.AddWithValue（@ Address_Line_3，txtChangeBusinessAddress3.Text）; 
 comm1.Parameters.AddWithValue（@ Provence，DDLProvince.Text）; 
 comm1.Parameters.AddWithValue（@ County，DDLCounty.Text）; 
 comm1.Parameters.AddWithValue（@ Username，Session [User]）; 
 comm1.Parameters.AddWithValue（@ PasswordNew，txtChangeBusinessPassword.Text）; 
 //您需要执行命令来运行存储过程
 comm1.ExecuteNonQuery（）; 
} //因为我使用了using语句，所以我不需要connWrite.Close（）; 
} 
} 
 reader.Close（）; 
 conn.Close（）; 
}



链接到

上的MS参考 using Statement [ ^ ]



- 我（非常）不好。我在using语句之外声明了comm1 - 这意味着在语句完成后它仍然具有范围。 * facepalm *在上面的修正中看到粗体和突破。

虽然我在这里我应该指出GUI元素应该与数据库元素分开 - 我可能会通过文本通过作为单独类的方法的参数。


I have two tables in my database : Business and User_Acc.

Business has the following columns:
Business_ID
Business_Name
Address_Line_1
Address_Line_2
Address_Line_3
County
Provence
Owner_Name
Vat_No
Telephone

User_Acc has the following columns:
User_ID
Username
Password
Roles
Email
Business_ID

I have a relationship set up that links both tables through the business_ID

I have a webform using C# and Asp.net that will allow the user to input details into textboxes to modify the record already in the database - i.e update the business details and if they change their password update the password in the User_acc table

Here is my procedure but it is not working:

ALTER PROCEDURE dbo.SaveBusinessChanges
(
	@Business_Name varchar(50),
	 @Address_Line_1 varchar(50), 
	 @Address_Line_2 varchar(50),
	  @Address_Line_3 varchar(50),
	   @County varchar(50),
	    @Provence varchar(50),
		 @Telephone varchar(50),
		 @Username varchar(50),
		   @PasswordNew varchar(50),
		   @Email varchar(50)
		   )
	
AS
BEGIN TRANSACTION


UPDATE
    T1
SET
    T1.Business_Name = @Business_Name
FROM
    Business T1
INNER JOIN User_Acc T2
ON	
	T1.Business_ID = T2.Business_ID
WHERE
	T2.Username = @Username

UPDATE
    T1
SET
    T1.Address_Line_1 = @Address_Line_1
FROM
    Business T1
INNER JOIN User_Acc T2
ON	
	T1.Business_ID = T2.Business_ID
WHERE
	T2.Username = @Username

UPDATE
    T1
SET
    T1.Address_Line_2 = @Address_Line_2
FROM
    Business T1
INNER JOIN User_Acc T2
ON	
	T1.Business_ID = T2.Business_ID
WHERE
	T2.Username = @Username

UPDATE
    T1
SET
    T1.Address_Line_3 = @Address_Line_3
FROM
    Business T1
INNER JOIN User_Acc T2
ON	
	T1.Business_ID = T2.Business_ID
WHERE
	T2.Username = @Username

UPDATE
    T1
SET
    T1.County = @County
FROM
    Business T1
INNER JOIN User_Acc T2
ON	
	T1.Business_ID = T2.Business_ID
WHERE
	T2.Username = @Username

UPDATE
    T1
SET
    T1.Provence = @Provence
FROM
    Business T1
INNER JOIN User_Acc T2
ON	
	T1.Business_ID = T2.Business_ID
WHERE
	T2.Username = @Username
		
UPDATE
    T1
SET
    T1.Telephone = @Telephone
FROM
    Business T1
INNER JOIN User_Acc T2
ON	
	T1.Business_ID = T2.Business_ID
WHERE
	T2.Username = @Username

UPDATE
    T2
SET
    T2.Email = @Email
FROM
    User_Acc T2
INNER JOIN Business T1
ON	
	T2.Business_ID = T1.Business_ID
WHERE
	T2.Username = @Username

UPDATE
    T2
SET
    T2.Password = @PasswordNew
FROM
    User_Acc T2
INNER JOIN Business T1
ON	
	T2.Business_ID = T1.Business_ID
WHERE
	T2.Username = @Username
	
	COMMIT



The record is identified through the link between Business_ID and a parameter @Username which takes the value from the Session variable 'User'

How should I go about solving this issue?

解决方案

Ok ... first steps to finding out what's wrong try ...
Print the contents of @Username (If it's empty then it's the way you're handling the session variable)

Assuming that was fine then run this sql 

SELECT * FROM User_Acc T2 WHERE Username = 'xxxxx'

replacing the xxxxx with the actual value of the username.
Check that there is a row returned (If not then it's your data at fault - there are no matches)

Assuming that worked then try the following sql 

SELECT * FROM Business T1 INNER JOIN User_Acc T2 ON T1.Business_ID = T2.Business_ID


(If nothing is returned then it's the linking of your tables that is at fault)

Once you get it working you need to change the procedure to make it a little more efficient (and easier to read!!) ... you can update more than one column at a time if the updates are based on the same criteria.
For example all of your T1 updates can be expressed as 

UPDATE T1
SET T1.Business_Name = @Business_Name, Address_Line_1 = @Address_Line_1, Address_Line_2 = @Address_Line_2,
Address_Line_3 = @Address_Line_3, County = @County, Provence = @Provence, Telephone = @Telephone
FROM  Business T1
INNER JOIN User_Acc T2 ON T1.Business_ID = T2.Business_ID
WHERE T2.Username = @Username



[EDIT - Added suggestions for C# code - see comments in the code]
Caveats as before - I haven't been able to test this

protected void SaveChanges(object sender, EventArgs e)
{
    SqlConnection conn;
    SqlCommand comm;
    SqlCommand comm1;
    String connectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
    conn = new SqlConnection(connectionString);
    comm = new SqlCommand("SELECT * from Business a, User_Acc c Where c.Username = @Username AND c.Business_ID = a.Business_ID", conn);
    comm.Parameters.Add("@Username", System.Data.SqlDbType.VarChar).Value = Session["User"];
    conn.Open();
    SqlDataReader reader = comm.ExecuteReader();
    while (reader.Read())
    {
        if (txtOldPassword.Text.Equals(reader["Password"]))
        {
            // I'm not confortable using the same connection as the read - especially as we appear to 
            // be inside a reader loop so I'll create a new connection for the stored proc update
            SqlConnection connWrite;
            connWrite = new SqlConnection(connectionString);
            // For info on the using statement see http://msdn.microsoft.com/en-us/library/htd05whh.aspx
            using (SqlCommand comm1 = new SqlCommand("exec SaveBusinessChanges @Business_Name,@Address_Line_1,@Address_Line_2,@Address_Line_3,@County,@Provence,@Telephone,@Username,@PasswordNew,@Email", connWrite))
            {
                // Let the command know it is to run a Stored Procedure
                comm1.CommandType = CommandType.StoredProcedure;
                // This bit unchanged from OPs code
                comm1.Parameters.AddWithValue("@Business_Name", txtChangeBusinessName.Text);
                comm1.Parameters.AddWithValue("@Email", txtChangeBusinessEmail.Text);
                comm1.Parameters.AddWithValue("@Telephone", txtChangeBusinessTelephone.Text);
                comm1.Parameters.AddWithValue("@Address_Line_1", txtChangeBusinessAddress.Text);
                comm1.Parameters.AddWithValue("@Address_Line_2", txtChangeBusinessAddress2.Text);
                comm1.Parameters.AddWithValue("@Address_Line_3", txtChangeBusinessAddress3.Text);
                comm1.Parameters.AddWithValue("@Provence", DDLProvince.Text);
                comm1.Parameters.AddWithValue("@County", DDLCounty.Text);
                comm1.Parameters.AddWithValue("@Username", Session["User"]);
                comm1.Parameters.AddWithValue("@PasswordNew", txtChangeBusinessPassword.Text);
                // You need to execute the command to run the stored procedure
                comm1.ExecuteNonQuery();
            } // Because I've used a "using" statement I don't need connWrite.Close();
        }
    }
    reader.Close();
    conn.Close();
}


Link to the MS reference on the
using Statement[^] [Edit 2 - corrected the link]

[Edit 3] - My (very) bad. I'd declared comm1 outside of the using statement - meaning it still had scope after that statement completed. *facepalm* See bold and strike through in correction above.
While I'm here I should point out that the GUI elements should really be separated away from the Database elements - I would probably have the text passed through as parameters to methods on a separate class.


这篇关于SQL多个更新语句将影响两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆