Link to home
Start Free TrialLog in
Avatar of Shay10
Shay10

asked on

How do I get rowcount back from stored procedure and display on asp page.

I am sending 4 variables but now need to get the row count of what rows are effected from the sp and display them in a textbox.  I'm using VS2008 C# with MS-SQL2008.

Here's my C# code:

    public void Bind_MakeInActive(int iFromInput, int iCriteria, string sValue)
    {
        string s1 = " ";
        string s2 = " ";
        s1 = iFromInput.ToString();
        s2 = iCriteria.ToString();
        sValue = sValue.Replace(" ", "");
        try
        {
            Authentication.CODEDUserBase objUserData = Session["UserData"] as Authentication.CODEDUserBase;
            int iTenant = 0;
            string stenant = " ";
            iTenant = Convert.ToInt32(objUserData.Tenant);
            stenant = iTenant.ToString();
            SqlCommand cmd = new SqlCommand("MoveSource", new SqlConnection("server=someIP;database=someDatabase;UID=somelogin;PWD=somepwd;"));
            //Label1.Text = "Sending {" + stenant + "} {" + s1 + "} {" + s2 + "} {" + sValue + "}"; //verify vars correct that are being sent    
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Subsystem", iTenant);
            cmd.Parameters.AddWithValue("@FromProject", iFromInput);
            cmd.Parameters.AddWithValue("@Criteria", iCriteria);
            cmd.Parameters.AddWithValue("@Value", sValue);
            cmd.Connection.Open();
            cmd.ExecuteReader();
            cmd.Connection.Close();
            cmd.Connection.Dispose();

        }
        catch (Exception ex)
        {
            Label1.Text = ex.Message;
        }
    }


Stored procedure:
USE [Database]
GO
/****** Object:  StoredProcedure [dbo].[MoveSource]    Script Date: 04/20/2010 12:19:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--  test this sp
-- exec This database.dbo.MoveSource 54, 811, 812, '21529'
-- =============================================
ALTER PROCEDURE [dbo].[MoveSource]
      @Subsystem int,
      @FromProject int,
      @ToProject int,
      @LeadList varchar(6)

AS
BEGIN
      SET NOCOUNT ON;
      SET DEADLOCK_PRIORITY LOW
      set rowcount 500

      DECLARE @RowsRemaining INT
      DECLARE @RowsAffected INT

      SET @RowsAffected = 0
      SET @RowsRemaining = 1

      WHILE (@RowsRemaining > 0)
            BEGIN
                  update db.dbo.DEALs
                  SET ProjectID = @ToProject * CASE WHEN ABS(d.ProjectID) < 0 THEN -1 ELSE 1 END
                  FROM db.dbo.DEALs d with (nolock)
                  INNER JOIN db.dbo.Project p with (nolock) ON ABS(d.ProjectID) = p.ProjectID
                  WHERE p.SubsystemID = @Subsystem
                    AND d.ProjectID = @FromProject
                    AND SUBSTRING(d.Field1, 1, 5) = SUBSTRING(@LeadList, 1, 5)
      
                  SET @RowsRemaining = @@ROWCOUNT
                  SET @RowsAffected = @RowsAffected + @RowsRemaining
                  
                  print CONVERT(varchar(24), GetDate(), 109) + ' List move records processed: ' + CONVERT(varchar(24), @RowsRemaining);
            END

      RETURN @RowsAffected
            
END

Can anyone modify both the code and stored procedure so that it will retun and display the rows effected? - The 4 variables passed work properly - just need to get rows effected returned and displayd.
Thanks in advance!
Avatar of Ravi Vaddadi
Ravi Vaddadi
Flag of United States of America image

call cmd.ExecuteNonQuery and it returns the number of records affected
call cmd.ExecuteNonQuery instead of cmd.ExecuteReader and it returns the number of records affected
int rowcnt =  cmd.ExecuteNonQuery;
TextBox1.Text = Convert.ToInt32(rowcnt);
Avatar of Shay10
Shay10

ASKER

Just added this in andtried it:
            //cmd.ExecuteReader();
            iResults = cmd.ExecuteNonQuery();
            Label5.Text = "RESULTS RETURNED: " + iResults.ToString();

Here's the result:

RESULTS RETURNED: -1 THIS

What am I doing wrong?
ALTER PROCEDURE [dbo].[MoveSource]
      @Subsystem int,
      @FromProject int,
      @ToProject int,
      @LeadList varchar(6)
    @rCnt int OUTPUT
AS
BEGIN

....


SELECT @rCnt = @RowsAffected + @RowsRemaining

HTH
Ashok

without the OUTPUT keyword, but you won't be able to use the returned value in the calling program.

Return values can be used within stored procedures to provide the stored procedure execution status to the calling program. The return values -99 through 0 are reserved for SQL Server internal use. You can create your own parameters that can be passed back to the calling program. By default, the successful execution of a stored procedure (or any group of SQL statements) will return 0. The syntax of the return command is:
view source
print?

RETURN integer_value

From:

http://sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_&_Return_Values
This is wrong.

SET NOCOUNT ON;

if you set nocount on then you would not get the count
USE [Database]
GO
/****** Object:  StoredProcedure [dbo].[MoveSource]    Script Date: 04/20/2010 12:19:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--  test this sp
-- exec This database.dbo.MoveSource 54, 811, 812, '21529'
-- =============================================
ALTER PROCEDURE [dbo].[MoveSource]
      @Subsystem int,
      @FromProject int,
      @ToProject int,
      @LeadList varchar(6)

AS
BEGIN
      SET DEADLOCK_PRIORITY LOW


                  update db.dbo.DEALs
                  SET ProjectID = @ToProject * CASE WHEN ABS(d.ProjectID) < 0 THEN -1 ELSE 1 END
                  FROM db.dbo.DEALs d with (nolock)
                  INNER JOIN db.dbo.Project p with (nolock) ON ABS(d.ProjectID) = p.ProjectID
                  WHERE p.SubsystemID = @Subsystem
                    AND d.ProjectID = @FromProject
                    AND SUBSTRING(d.Field1, 1, 5) = SUBSTRING(@LeadList, 1, 5)
     
                 
         
           
END


TextBox.Text = cmd.ExecuteNonQuery().ToString()
Avatar of Shay10

ASKER

     
Not sure if I have this in the right place as I get -1 each time still
  ...
        ...
            cmd.Connection.Open();
            //cmd.ExecuteReader();
            int rowcnt = cmd.ExecuteNonQuery();
            Label5.Text = Convert.ToString(rowcnt);
            cmd.Connection.Close();
            cmd.Connection.Dispose();


      @Subsystem int,
      @FromProject int,
      @ToProject int,
      @LeadList varchar(6),
      @rCNT INT OUTPUT

            BEGIN
                                               ...
                                                SET @RowsRemaining = @@ROWCOUNT
                  SET @RowsAffected = @RowsAffected + @RowsRemaining

                  
                  print CONVERT(varchar(24), GetDate(), 109) + ' List move records processed: ' + CONVERT(varchar(24), @RowsRemaining);
            --SELECT @rCNT = @RowsAffected + @RowsRemaining
            END

      RETURN @RowsAffected
      SELECT @rCNT = @RowsAffected + @RowsRemaining
            
END
Avatar of Shay10

ASKER

HAve set everything exactly per this example and get -1

USE [Database]
GO
/****** Object:  StoredProcedure [dbo].[MoveSource]    Script Date: 04/20/2010 12:19:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--  test this sp
-- exec This database.dbo.MoveSource 54, 811, 812, '21529'
-- =============================================
ALTER PROCEDURE [dbo].[MoveSource]
      @Subsystem int,
      @FromProject int,
      @ToProject int,
      @LeadList varchar(6)

AS
BEGIN
      SET DEADLOCK_PRIORITY LOW


                  update db.dbo.DEALs
                  SET ProjectID = @ToProject * CASE WHEN ABS(d.ProjectID) < 0 THEN -1 ELSE 1 END
                  FROM db.dbo.DEALs d with (nolock)
                  INNER JOIN db.dbo.Project p with (nolock) ON ABS(d.ProjectID) = p.ProjectID
                  WHERE p.SubsystemID = @Subsystem
                    AND d.ProjectID = @FromProject
                    AND SUBSTRING(d.Field1, 1, 5) = SUBSTRING(@LeadList, 1, 5)
     
                 
         
           
END


TextBox.Text = cmd.ExecuteNonQuery().ToString()
ASKER CERTIFIED SOLUTION
Avatar of Ravi Vaddadi
Ravi Vaddadi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try to run

update db.dbo.DEALs
                  SET ProjectID = @ToProject * CASE WHEN ABS(d.ProjectID) < 0 THEN -1 ELSE 1 END
                  FROM db.dbo.DEALs d with (nolock)
                  INNER JOIN db.dbo.Project p with (nolock) ON ABS(d.ProjectID) = p.ProjectID
                  WHERE p.SubsystemID = @Subsystem
                    AND d.ProjectID = @FromProject
                    AND SUBSTRING(d.Field1, 1, 5) = SUBSTRING(@LeadList, 1, 5)

replacing with actual parameters values you are passing from in where clause directly in MS SQL Server Studio.

Is update failing?
Have you tried my solution? Feedback?
Avatar of Shay10

ASKER

Sorry to keep you guys in suspense - I have not had an opportunity as of yet to further pursue this problem but will update all when I get back in it-should be next week sometime.  I'll give credit to the solution that is most helpful or works at that time.

Thanks
Avatar of Shay10

ASKER

Just got this working - this was perfect.  Thank you!