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.
![balexander](https://i.stack.imgur.com/qEVrC.png?s=256&g=1)
Author by
balexander
Updated on June 27, 2022Comments
-
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.