SQL多个更新语句将影响两个表 [英] SQL Multiple Update Statements that will affect two tables
问题描述
我的数据库中有两个表: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 sqlSELECT * 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 sqlSELECT * 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屋!