Changed web form details not saving to database

Question

4.00/5 (1 vote)

See more:

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

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);
                      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

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

Posted 4-Mar-14 5:37am

Member 10609511

Add a Solution

Comments

Krunal Rohit 4-Mar-14 12:38pm

What is the error ? Have you tried wit breakpoint ?

-KR

Member 10609511 5-Mar-14 7:44am

I am getting no error in the C# code it runs properly without error and when I debug it the updated values from the textboxes are carried into the parameters it just isn't saving to the databsase

4 solutions

Solution 2

Check Tadit's solution . Also , suggest you to have a look on below links

Configuring Parameters and Parameter Data Types[^]

data-providers/cs-procedure with parameter[^]

Posted 4-Mar-14 6:32am

JoCodes

Solution 1

Update your code like below...

C#

SqlCommand comm1 = new SqlCommand("SaveBusinessChanges", connWrite);
comm1.CommandType = CommandType.StoredProcedure;

// Then define the Parameters and their values.

Posted 4-Mar-14 6:05am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Solution 4

You can add command type in your code.

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.CommandType = CommandType.StoredProcedure; // add CommandType

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);

Posted 4-Mar-14 19:40pm

Mehul Thummar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amalraj Ramesh · Accepted Answer · 2014-03-04T06:34:00

Try this if issue let me know

SqlConnection connWrite;
connWrite = new SqlConnection(connectionString);
SqlCommand comm1 = new SqlCommand("SaveBusinessChanges", 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);
connWrite.Open();
comm1.ExecuteNonQuery();
connWrite.Close();

Happy coding.. :)