Prblem in getting the rows affected values in sqlserver to C# class.

[senthilnova]

hi all
i have c# class which calls a stored procedure having insert,update and delete method (using if to switch between them).

when i call the SP to EXECUTENONQUER() there is no response from the sqlserver. I too tested the stored proc seperately , it shows NO ROWS AFFECTED and 0 ROWS RETURNED. But the rows get inserted , or updated according to my call.(i found this by checking recs in table).what would be the trouble?? really confusing me.

below is my stored proc .
C# connectivity code:

sqlCon = new SqlConnection(strCon);
sqlCmd = new SqlCommand();
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlCmd.CommandText = "[SP Name]";
sqlCmd.Connection = sqlCon;
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
int result = sqlCmd.ExecuteNonQuery();
 

stored procedure

ALTER PROCEDURE SP_insertUserMaster
    (
    @UserName nvarchar(30),
    @LoginId nvarchar(50),
    @Password nvarchar(80),
    @SecurityLevel int,
    @Department    nvarchar(15),
    @TP nchar(1))
 
AS
     SET NOCOUNT ON 
     if @TP='I'
        BEGIN    
            DECLARE @nCnt NUMERIC
            SELECT @nCnt=(select count(*) from UserMaster Where LoginId=@LoginId)
            IF @nCnt<=0
            begin
                    INSERT INTO UserMaster
                    (UserName, LoginId, Password, SecurityLevel, Department)
                     VALUES
                    (@UserName,@LoginId,@Password,@SecurityLevel,@Department)    
            end
 
        END
return
 
 
thanku
 
 
 

[ck9663]

use @@rowcount

-- CK

[senthilnova]

hi ck
i am new to sqlserver, can u tell in detail to rectify my trouble.

thank u

ssk

[ck9663]

hi ck
i am new to sqlserver, can u tell in detail to rectify my trouble.

thank u

ssk

am not sure how c# works. but in ASP, you can execute stored proc and can return resultset. the following query can work in ASP. try it in c#. test it first.

ALTER PROCEDURE SP_insertUserMaster
    (
    @UserName nvarchar(30),
    @LoginId nvarchar(50),
    @Password nvarchar(80),
    @SecurityLevel int,
    @Department nvarchar(15),
    @TP nchar(1))
 
AS
     SET NOCOUNT ON
     if @TP='I'
        BEGIN   
            DECLARE @rCnt int
 
            if not exists(select 1 from UserMaster Where LoginId=@LoginId)
            begin
                    INSERT INTO UserMaster
                    (UserName, LoginId, Password, SecurityLevel, Department)
                     VALUES
                    (@UserName,@LoginId,@Password,@SecurityLevel,@Department) 
 
                    select @@rowcount
                    return1/9/2008
            end
 
        END
 
 

also, this will only return value if the loginid is not yet in usermaster.try passing a loginid that you know not existing yet...

-- CK