Stored procedure not updating table when called from C#

Question

I have a stored procedure that should update a table. When I run it directly I get “Command(s) completed successfully.”, but when called from C# it does not change the table. What is going wrong?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:   Mohamamd Ibrahim Tasal
-- Create date: 29-Octobar-2009
-- Description: This procedure is releasing Numbers....
-- =============================================
ALTER PROCEDURE [dbo].[Release_MassMSISDN]
(
    @Quantity int,
    @_SCP  int

)
AS
BEGIN
SET NOCOUNT ON;
set @Quantity=1;
set @_SCP=798000070;
DECLARE @_Filter nvarchar(1000)
DECLARE @sql nvarchar(4000)
IF @_SCP = 2
SET @_Filter = 'MSISDN LIKE ''799%'' OR MSISDN LIKE ''798%'''
IF @_SCP = 1
SET @_Filter = 'MSISDN LIKE ''797%'' OR MSISDN LIKE ''796%'' OR MSISDN LIKE ''7950%'' OR MSISDN LIKE ''7951%'' OR MSISDN LIKE ''7952'' OR MSISDN LIKE ''7953%'' OR MSISDN LIKE ''7954%'''
IF @_SCP = 3
SET @_Filter = 'MSISDN LIKE ''794%'''
IF @_SCP = 4
SET @_Filter = 'MSISDN LIKE ''793%''or MSISDN LIKE ''7955%'' OR MSISDN LIKE ''7956%'' OR MSISDN LIKE ''7957%'' or MSISDN LIKE ''7958%'' OR MSISDN LIKE ''7959%'''

UPDATE
    MSISDN_Master
    SET
    IMSI_HLR=NULL,
    IMSI_IN=NULL,
    GoldNumber=0,
    SERIAL=NULL,
    FK_AllocationTypeID = NULL,
    FK_ProfileTypeID = NULL,
    FK_ModelTypeID = NULL,
    FK_StatusID = NULL,
    FK_BatchID = NULL,
    Activation_Date = NULL, 
    Locked = 0,
    ICBlocked = 0,
    langcur = NULL,
    Credit = NULL
WHERE msisdn in ('SELECT TOP '+ CAST(@quantity AS varchar(10)) + ' MSISDN,IMSI_HLR,IMSI_IN,Serial,FK_BatchID BatchID,FK_AllocationTypeID AllocationTypeID,FK_ProfileTypeID ProfileTypeID,FK_ModelTypeID ModelTypeID,Activation_Date FROM MSISDN_Master
where imsi_hlr not like ''412%'' and imsi_hlr not like ''GOL%'' and imsi_hlr is not null and imsi_in is not null AND  ('+ @_Filter +') ORDER BY MSISDN')

END

Answer 1

You should have a look at the debugging options available. I believe if you set it up right you can debug straight from your C# into your SQL stored procedure. Never had to do this myself but I'm told it can be done.

Alterantively setup an equivalent stored procedure that just executes a SELECT of your UPDATE's WHERE clause to see if you are actually getting any records back, and if the columns you expect to change are different to the value you expect to change them to.

And of course check that the connection string is pointing to the right database. That one has caught me once before (and was so embaressing).

Otherwise, as the other guys are suggesting, a little more context and C# code would be useful. And what makes you sure rows are getting modified. Does the SQL mention rows were modified or just that commands were executed?

Answer 2

Are you certain that the table is updated correctly when you run the stored procedure directly?

Is it possible that in your C# code you begin a transaction before calling the stored procedure, but never commit it afterwards?