ASP C# | Update Database using SQL and Stored Procedures

12,230

Solution 1

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

Solution 2

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

Share:
12,230
balexander
Author by

balexander

Updated on June 27, 2022

Comments

  • balexander
    balexander almost 2 years

    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.