Stored procedure called from C# doesn't update record

Question

I have written a simple stored procedure for updating a record which isn't working but I can't work out why. No exceptions are thrown but the record doesn't update either.

See code below:

public int IMGId;

protected void btnUpdate_Click(object sender, EventArgs e)
    {
        string result = "";
        string sSQL = "usp_imageloader_update";


        using (SqlConnection dbConnection = new SqlConnection(CKS_app_settings.sql_conn_string_db))
        {
            // SqlTransaction tn=null;   
            try
            {
                dbConnection.Open();
                //start Transaction
                // tn = dbConnection.BeginTransaction();
                SqlCommand command = new SqlCommand(sSQL, dbConnection);
                //command.Transaction = tn;
                command.CommandText = sSQL;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 1024;
                command.Parameters.AddWithValue("@p_image_id", IMGId);
                command.Parameters.AddWithValue("@p_url", txtUrl.Text);
                command.Parameters.AddWithValue("@p_alt_text", txtAlt.Text);
                //command.Parameters.AddWithValue("@p_filepath", File1.Value);
                //command.Parameters.AddWithValue("@p_cntr_id", str_id);
                int rowsAffected = command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
               // throw ex;

                //If it failed for whatever reason, rollback the //transaction
                //tn.Rollback();                          
                //No need to throw because we are at a top level call and //nothing is handling exceptions
                result = ex.InnerException.Message;
            }
        }

Stored procedure in SQL SERVER

USE [smsdb_test_griffin2]
GO
/****** Object:  StoredProcedure [dbo].[usp_imageloader_update]    Script Date: 01/04/2012   09:05:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER    procedure [dbo].[usp_imageloader_update]
@p_image_id INT,
@p_url  VARCHAR(255),
@p_alt_text VARCHAR(255)
as 

UPDATE Image_Library_UK 
SET  
Url=@p_url,
Alt_text=@p_alt_text 

WHERE Image_id=@p_image_id

Does your SP work if you execute it in isolation, i.e. not from your C# code? — razlebe
– razlebe, Commented Jan 4, 2012 at 9:58
T-SQL you say? GO doesn't work in a stored procedure in T-SQL. — Mr Lister
– Mr Lister, Commented Jan 4, 2012 at 10:00
Have you tried exec procedure in SQL Managament with values passed in C#? — Mariusz
– Mariusz, Commented Jan 4, 2012 at 10:01

Schroedingers Cat · Accepted Answer · 2012-01-04 10:21:48Z

1

Having tried it in isolation, assuming that this works ( and there is nothing glaringly obvious wrong ), I would assume that one of your parameters is not being set correctly. It could be that the IMGid is not right - which would have this effect - or it coudl be that the the url and alttext have already be reset to their values by the load of the page.

Check the values at the point of calling. It may be you need to use !Page.IsPostBack to not reset these values on a postback. It may be that you need to access them using the request variable - it does depend on the rest of your code.

answered Jan 4, 2012 at 10:21

Schroedingers Cat

3,1392 gold badges19 silver badges34 bronze badges

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

1 Comment

nick gowdy Over a year ago

I hard coded an int value for the ID and the stored procedure worked. I guess something went wrong when grabbing the id from my code. Thanks to everyone that helped!

Collectives™ on Stack Overflow

Stored procedure called from C# doesn't update record

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related