Using a stored procedure to update columns in C#

Question

I am trying to update columns in my table by filling out text boxes and clicking save; I don't get an error or anything. Just nothing happens!

Here is my stored procedure:

ALTER PROCEDURE [dbo].[sp_UpdateProj]
    @ORAID INT = NULL,
    @FullTitle NVARCHAR(250) = NULL
AS
BEGIN
    UPDATE tbl_ProjectFile
    SET FullTitle = @FullTitle
    WHERE ORAID = @ORAID
END

and it works when I run it in SQL Server Management Studio, given an ID and Title name

Here is my C# code

protected void Button_Save_Click(object sender, EventArgs e)
{
    string connectionStr = ConfigurationManager.ConnectionStrings["ORAProjectConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(connectionStr))
    {
        con.Open();

        string query = "sp_UpdateProj Where ORAID=" + int.Parse(TextBox_ORAID.Text);

        SqlCommand cmd = new SqlCommand(query, con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;

        cmd.Parameters.AddWithValue("@ORAID", Convert.ToInt32(TextBox_ORAID.Text));
        cmd.Parameters.AddWithValue("@FullTitle", TextBox_FullTitle.Text);

        con.Close();
    }
}

you created object, filled some arguments, but haven't actually executed it, check ExecuteNonQuery method — Iłya Bursov, Aug 03 '18 at 18:31
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Aug 03 '18 at 18:31
Also: when executing a stored procedure using `SqlCommand`, the command text should be **just** the stored procedure's name (preferably with the schema - `dbo.xxxxx`) - no `WHERE` clause !! — marc_s, Aug 03 '18 at 18:32

score 3 · Answer 1 · answered Aug 03 '18 at 18:34

You're setting everything up (almost) correctly - but you're never actually executing the stored procedure!

Try this code:

protected void Button_Save_Click(object sender, EventArgs e)
{
    string connectionStr = ConfigurationManager.ConnectionStrings["ORAProjectConnectionString"].ConnectionString;
    // the query string should be **ONLY** the stored procedure name - nothing else!
    string query = "dbo.sp_UpdateProj";

    // you should put **both** SqlConnection and SqlCommand in "using" blocks
    using (SqlConnection con = new SqlConnection(connectionStr))
    using (SqlCommand cmd = new SqlCommand(query, con))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        // fill the parameters - avoiding "AddWithValue"
        cmd.Parameters.Add("@ORAID", SqlDbType.Int).Value = Convert.ToInt32(TextBox_ORAID.Text);
        cmd.Parameters.Add("@FullTitle", SqlDbType.NVarChar, 250).Value = TextBox_FullTitle.Text;

        con.Open();
        // you need to **EXECUTE** the command !
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

@Anderson: can you show us the **connection string** you're using? — marc_s, Aug 03 '18 at 19:29

score 1 · Answer 2 · edited Aug 03 '18 at 20:48

There are some errors in the Button_Save_Click event handler:

When you use commandType is StoredProcedure you have to pass just the stored procedure name
With having a stored procedure with sp_ prefix create performance issue (Using sp_ as prefix for user stored procedures in SQL server causing performance impact)
You forgot to call the ExecuteNonQuery method

Try this code:

protected void Button_Save_Click(object sender, EventArgs e)
{
 string connectionStr = ConfigurationManager.ConnectionStrings["ORAProjectConnectionString"].ConnectionString;
 string procedureName = "dbo.UpdateProj";


 using (SqlConnection con = new SqlConnection(connectionStr))
 using(SqlCommand cmd = new SqlCommand(procedureName , con))
 {

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@ORAID", Convert.ToInt32(TextBox_ORAID.Text));
    cmd.Parameters.AddWithValue("@FullTitle", TextBox_FullTitle.Text);
    con.Open();
    cmd.ExecuteNonQuery()
    con.Close();
 }
}

thanks, I did this and even renamed my procedure and it still doesn't work — Anderson, Aug 03 '18 at 18:51
@Anderson Can you debug to the ExecuteNonQuery step and see the parameter values passing ? — mukesh kudi, Aug 03 '18 at 18:56
I figured it out-- i had to add if (IsPostBack) {return; } in my page load lmao — Anderson, Aug 03 '18 at 19:30

WildJoe · Answer 3 · 2018-08-03T18:34:03.153

0

Your query line should just be:

string query = "sp_UpdateProj";

You already have the parameters as objects below that.

Then add

cmd.ExecuteNonQuery();

to execute

edited Aug 03 '18 at 18:34

answered Aug 03 '18 at 18:33

WildJoe

5,740
3
26
30

Edited to add that too. – WildJoe Aug 03 '18 at 18:34

score 0 · Answer 4 · answered Aug 03 '18 at 18:35

0

Here is the brief info bout executing stored procedure using C#

Call a stored procedure with parameter in c#

answered Aug 03 '18 at 18:35

Bambam Deo

148
1
4
15

Using a stored procedure to update columns in C#

4 Answers4