The code I have below is not saving details from my webform to the database. The user modifies textboxes with already populated data and clicks the save button. The below code is fired when that button is clicked. I believe it is not working because I have a connection already open in the outer code and then I make another connection to run the procedure in the inner bracket of 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);
SqlCommand comm1 = new SqlCommand("exec SaveBusinessChanges @Business_Name, @Email, @Telephone, @Address_Line_1, @Address_Line_2, @Address_Line_3, @Provence, @County, @Username, @PasswordNew ",connWrite);
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.Open();
comm1.ExecuteNonQuery();
connWrite.Close();
}
}
reader.Close();
conn.Close();
Response.Redirect("Welcome.aspx");
}
}
And here is my SQL 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
BEGIN TRANSACTION
DECLARE @Business_ID INT = 0
SET @Business_ID = (SELECT 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