CODEWITHSUNDEEP
c#asp.netstored-procedures
who-aditya-nawandar
who-aditya-nawandar

Reputation: 1344

update stored procedure not working

I want to know what (if any) is wrong with the below stored procedure for updating address: I have also given my database image to get a clear idea....

ALTER PROCEDURE [dbo].[usp_UpdateAddress] 
    (@OriginalEmail nvarchar(50), @FirstName varchar(50), @LastName varchar(50), 
     @Country_ID int, @AddressLine1 varchar(50), @AddressLine2 varchar(50),
     @AddressLine3 varchar(50), @Telephone varchar(50), @Email nvarchar(50), 
     @City varchar(50), @State_ID int, @PostalCode varchar(50), @Mobile varchar(50))
AS
BEGIN
   DECLARE @User_ID INT

   SELECT @User_ID = ID FROM AUser Where Email = @OriginalEmail

   UPDATE [AUserAddress] 
   SET [AUser_ID] = @User_ID, [FirstName] = @FirstName, [LastName] = @LastName, 
       [Country_ID] = @Country_ID, [AddressLine1] = @AddressLine1, 
       [AddressLine2] = @AddressLine2, [AddressLine3] = @AddressLine3, 
       [Telephone] = @Telephone, [Email] = @Email, [City] = @City, 
       [State_ID] = @State_ID, [PostalCode] = @PostalCode, [Mobile] = @Mobile 
   WHERE
       Email = @OriginalEmail
END

Called from C#: 

private void UpdateAddress()
{
        try
        {

            string strSession = objGetSession.GetEmailFromSession();
            con.Open();
            SqlCommand sqlCmd = new SqlCommand("usp_UpdateAddress", con);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add("@OriginalEmail ", SqlDbType.NVarChar, 50).Value = strSession;
            sqlCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = txtFirstName.Text;
            sqlCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = txtLastName.Text;
            sqlCmd.Parameters.Add("@AddressLine1", SqlDbType.VarChar, 50).Value = txtAddressLine1.Text;
            sqlCmd.Parameters.Add("@AddressLine2", SqlDbType.VarChar, 50).Value = txtAddressLine2.Text;
            sqlCmd.Parameters.Add("@AddressLine3", SqlDbType.VarChar, 50).Value = txtAddressLine3.Text;
            sqlCmd.Parameters.Add("@Telephone", SqlDbType.VarChar, 50).Value = txtTelephone.Text;
            sqlCmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = txtEmailAddress.Text;
            sqlCmd.Parameters.Add("@Country_ID", SqlDbType.Int).Value = ddlCountry.SelectedItem.Value;
            sqlCmd.Parameters.Add("@City", SqlDbType.VarChar, 50).Value = txtCity.Text;
            sqlCmd.Parameters.Add("@State_ID", SqlDbType.Int).Value = ddlState.SelectedValue.ToString();
            sqlCmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 50).Value = txtPostalCode.Text;
            sqlCmd.Parameters.Add("@Mobile", SqlDbType.VarChar, 50).Value = txtMobile.Text;

            sqlCmd.Connection = con;

            sqlCmd.ExecuteNonQuery();

            con.Close();

            mpeTest.Show();
            Response.Write("<script> alert('Address Updated!') </script>");
        }
        catch (Exception e)
        {
            Response.Write("An Error Occurred" + e);
        }
    }

db

Is the stored procedure not executing? Debugging shows that in Update Address() it does take the new value but when execution is completed, the database is not updated...

Upvotes: 0

Views: 292

Answers (2)

Peter Henell
Peter Henell

Reputation: 2466

Your stored procedure is wrong.

According to your image the AUserAddress tables does not contain a column called Email.

Upvotes: 0

John Woo
John Woo

Reputation: 263693

What if you UPDATE it directly by joining the tables?

UPDATE  a
SET     a.[auser_id] = b.ID, 
        a.[firstname] = @FirstName, 
        a.[lastname] = @LastName, 
        a.[country_id] = @Country_ID, 
        a.[addressline1] = @AddressLine1, 
        a.[addressline2] = @AddressLine2, 
        a.[addressline3] = @AddressLine3, 
        a.[telephone] = @Telephone, 
        a.[email] = @Email, 
        a.[city] = @City, 
        a.[state_id] = @State_ID, 
        a.[postalcode] = @PostalCode, 
        a.[mobile] = @Mobile 
FROM    [auseraddress] a
        INNER JOIN AUser b 
            ON  a.Email = b.Email
WHERE   b.email = @OriginalEmail

Upvotes: 2

Related Questions