ASP C# | Update Database using SQL and Stored Procedures

Question

Here is my OnClick event:

protected void Submit_Click(object sender, EventArgs e)
{
    try
    {
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand("administratorUpdate", con))
            {
                cmd.Parameters.Add("@originalID", SqlDbType.VarChar).Value = IDHF.Value;
                cmd.Parameters.Add("@firstName", SqlDbType.VarChar).Value = firstNameTB.Text;
                cmd.Parameters.Add("@lastName", SqlDbType.VarChar).Value = lastNameTB.Text;
                cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = userNameTB.Text;
                cmd.Parameters.Add("@emailAddress", SqlDbType.VarChar).Value = emailAddressTB.Text;
                cmd.CommandType = CommandType.StoredProcedure;
            }
        }
        GetAllRPT.DataBind();
        Label ErrorMessageLabel = (Label)Master.FindControl("ErrorMessageLabel");
        new myFunctions().DisplayUserMessage("success", "Administrator Updated!", ErrorMessageLabel);
        AdminForm.Visible = false;
    }
    catch (Exception ex)
    {
        Label ErrorMessageLabel = (Label)Master.FindControl("ErrorMessageLabel");
        new myFunctions().DisplayUserMessage("error", ex.Message, ErrorMessageLabel);
    }
}

Here is my Stored Procedure [administratorUpdate]:

ALTER PROCEDURE [dbo].[administratorUpdate]
    @originalID UNIQUEIDENTIFIER,
    @firstName varchar (100),
    @lastName varchar (100),
    @userName varchar (100),
    @emailAddress varchar (100),
    @password varchar (100),
    @isActive bit
AS

UPDATE administrator
SET userName = @userName,
    emailAddress = @emailAddress,
    password = @password,
    firstName = @firstName,
    lastName = @lastName,
    isActive = @isActive
WHERE
    ID = @originalID

The code executes and does the success message but the datatable doesn't update. Who knows why? I am new to using Stored Procedures in ASP.

@Raj More Thanks, I didn't know how to format the SQL properly — balexander
– balexander, Commented Dec 15, 2010 at 23:57
'IDHF.Value' 'firstNameTB.Text' 'administratorUpdate' 'ErrorMessageLabel' 'myFunctions' It's all very inconsistent. txtFirstName, AdministratorUpdate, lblErrorMessage, MyFunctions. The two that stand out most to me is your function class and the server controls. — Phill
– Phill, Commented Dec 16, 2010 at 0:02

Kit Menke · Accepted Answer · 2012-05-20 17:19:23Z

5

Are you executing the command?

    using (SqlConnection con = new SqlConnection(conString))
    {
        conn.Open(); //this was missing

        using (SqlCommand cmd = new SqlCommand("administratorUpdate", con))
        {
            cmd.Parameters.Add("@originalID", SqlDbType.VarChar).Value = IDHF.Value;
            cmd.Parameters.Add("@firstName", SqlDbType.VarChar).Value = firstNameTB.Text;
            cmd.Parameters.Add("@lastName", SqlDbType.VarChar).Value = lastNameTB.Text;
            cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = userNameTB.Text;
            cmd.Parameters.Add("@emailAddress", SqlDbType.VarChar).Value = emailAddressTB.Text;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery(); //this was missing.
        }

        conn.Close();
    }

edited May 20, 2012 at 17:19

Kit Menke

7,0561 gold badge34 silver badges54 bronze badges

answered Dec 15, 2010 at 23:48

Babak Naffas

12.6k3 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dismissile Over a year ago

Also need to open the connection to the database by doing con.Open(); before doing cmd.ExecuteNonQuery();

balexander Over a year ago

@Dismissile I thought when I am using 'using' it's open whilest the data is in that statement. I can pull data down, but sending data i guess is different?

Babak Naffas Over a year ago

That too. I have modified the answer with the proper implementation.

balexander Over a year ago

@Babak Don't I have to close it?

Babak Naffas Over a year ago

@Bry4n, yes you are correct. My main point was that you're not executing.

|

Dave · Accepted Answer · 2010-12-15 23:49:50Z

1

My trick here is to do this in debug mode without the Try / Catch. The error(s) generated will point to the problem.

answered Dec 15, 2010 at 23:49

Dave

1,24413 silver badges24 bronze badges

Collectives™ on Stack Overflow

ASP C# | Update Database using SQL and Stored Procedures

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related