更新语句以影响多个表而不将数据保存到数据库asp.net Web表单

问题描述

我的数据库中有一个存储过程，用于保存Web表单上已修改文本框的详细信息。已修改的文本框中的值已成功传递到参数中，但似乎并未保存这些新细节而不是旧细节。







这是我的过程：

I have a stored procedure in my database that will save details from modified textboxes on a web form. The values from the modified textboxes are successfully being passed into the parameters but it seems it is just not saving these new details in place of the old details.



Here is my proc:

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, 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

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


	
	COMMIT

这是我的代码背后的C＃代码：

Here is my C# code behind code:

      protected void SaveChanges(object sender, EventArgs e)
        {
            
                SqlConnection conn;
                SqlCommand comm;
                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 == reader["Password"].ToString())
                    {
                       
                        SqlConnection connWrite;
                        connWrite = new SqlConnection(connectionString);
                        // For info on the using statement see http://msdn.microsoft.com/en-us/library/htd05whh.aspx
                        SqlCommand comm1 = new SqlCommand("exec SaveBusinessChanges @Business_Name,@Address_Line_1,@Address_Line_2,@Address_Line_3,@County,@Provence,@Telephone,@Username,@PasswordNew,@Email", connWrite);
                        {
                            connWrite.Open();
                            // Let the command know it is to run a Stored Procedure
                            comm1.CommandType = CommandType.StoredProcedure;
                            // This bit unchanged from OPs code
                            comm1.Parameters.Clear();
                            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);
                            connWrite.Close();
                            // 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();

                Response.Redirect("Welcome.aspx");
            }
        }

protected void Page_Load(object sender, EventArgs e)
        {
            Label1.Text = "Welcome " + Session["User"];
            lblUser.Text = Convert.ToString(Session["User"]);

            if (!IsPostBack)
            {
                SqlConnection conn;
                SqlCommand comm;
                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())
                {

                    txtChangeBusinessName.Text = reader["Business_Name"].ToString();
                    txtChangeBusinessAddress.Text = reader["Address_Line_1"].ToString();
                    txtChangeBusinessAddress2.Text = reader["Address_Line_2"].ToString();
                    txtChangeBusinessAddress3.Text = reader["Address_Line_3"].ToString();
                    DDLProvince.SelectedItem.Text = "";
                    DDLCounty.SelectedItem.Text = "";
                    DDLProvince.SelectedItem.Text = reader["Provence"].ToString();
                    DDLCounty.SelectedItem.Text = reader["County"].ToString();
                    lblVatNo.Text = reader["Vat_No"].ToString();
                    txtChangeBusinessEmail.Text = reader["Email"].ToString();
                    txtChangeBusinessTelephone.Text = reader["Telephone"].ToString();


                }


                reader.Close();
                conn.Close();

            }
        }

推荐答案

SqlCommand comm1 = new SqlCommand("exec SaveBusinessChanges @Business_Name,@Address_Line_1,@Address_Line_2,@Address_Line_3,@County,@Provence,@Telephone,@Username,@PasswordNew,@Email", connWrite);

用这个再试一次......

with this and try again...

SqlCommand comm1 = new SqlCommand("SaveBusinessChanges",connWrite);

也做一件事，你已经用过了更新声明，您可以编写简单的选择查询，如果大小写并检查它将返回任何数据吗？





你需要取消你的林实际上会执行你的商店程序的代码，所以请修改你的代码....

also do one thing, you have used where cause in your Update statement, can you write simple select query with that where case and check it will return any data or not?


you need to uncomment your line of code which will actually going to execute your store procedure so please modified your code like this....

connWrite.Open(); 
comm1.CommandType = CommandType.StoredProcedure;
comm1.Parameters.Clear(); 
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(); 
connWrite.Close();

这篇关于更新语句以影响多个表而不将数据保存到数据库asp.net Web表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！