Click here to Skip to main content
15,881,600 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
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:
SQL
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


Here is my C# code behind code:

C#
      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();

            }
        }
Posted
Updated 25-Feb-14 2:25am
v3

1 solution

can you please try to modified your code

C#
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...

C#
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....

C#
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();
 
Share this answer
 
v5
Comments
Member 10609511 25-Feb-14 8:33am    
No luck with that change - still not committing the changes from the textboxes to the database....
Tejas Vaishnav 25-Feb-14 8:39am    
have you check your where cause with select statement?
Member 10609511 25-Feb-14 8:46am    
what would I change in the proc exactly?

Here is what I tried and still nothing:

WHERE T2.Username = @Username
AND T1.Business_ID=T2.Business_ID
Tejas Vaishnav 25-Feb-14 8:45am    
try with this store procedure

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
BEGIN TRANSACTION
DECLARE @Business_ID INT = 0

SELECT @Business_ID = Business_ID FROM User_Acc WHERE Username = @Username

UPDATE Business
SET 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
WHERE Business_ID = @Business_ID

UPDATE User_Acc
SET Email = @Email
,[Password] = @PasswordNew
WHERE Username = @Username
COMMIT
END
Tejas Vaishnav 25-Feb-14 8:46am    
"DECLARE @Business_ID INT = 0" here i assume that your Business_ID is INT type, you need to modified data type with your actual data type

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900