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"]))
{
SqlConnection connWrite;
connWrite = new SqlConnection(connectionString);
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))
{
comm1.CommandType = CommandType.StoredProcedure;
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();
}
}
}
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.