CODEWITHSUNDEEP
c#sqlstored-procedures
balexander
balexander

Reputation: 24359

ASP C# | Update Database using SQL and Stored Procedures

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.

Upvotes: 3

Views: 12309

Answers (2)

Babak Naffas
Babak Naffas

Reputation: 12561

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

Upvotes: 5

Dave
Dave

Reputation: 1234

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

Upvotes: 1

Related Questions