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!
Shay10Asked:
Who is Participating?
 
SriVaddadiCommented:
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();
                SqlConnection conn = new SqlConnection("server=someIP;database=someDatabase;UID=somelogin;PWD=somepwd;")
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "MoveSource";
                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 = conn;
                conn.Open();
                int n = cmd.ExecuteNonQuery();
                conn.Close();

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

**********************************************
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

0
 
SriVaddadiCommented:
call cmd.ExecuteNonQuery and it returns the number of records affected
0
 
SriVaddadiCommented:
call cmd.ExecuteNonQuery instead of cmd.ExecuteReader and it returns the number of records affected
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
AshokCommented:
int rowcnt =  cmd.ExecuteNonQuery;
TextBox1.Text = Convert.ToInt32(rowcnt);
0
 
Shay10Author Commented:
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?
0
 
AshokCommented:
ALTER PROCEDURE [dbo].[MoveSource]
      @Subsystem int,
      @FromProject int,
      @ToProject int,
      @LeadList varchar(6)
    @rCnt int OUTPUT
AS
BEGIN

....


SELECT @rCnt = @RowsAffected + @RowsRemaining

HTH
Ashok

0
 
AshokCommented:
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
0
 
SriVaddadiCommented:
This is wrong.

SET NOCOUNT ON;

if you set nocount on then you would not get the count
0
 
SriVaddadiCommented:
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()
0
 
Shay10Author Commented:
     
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
0
 
Shay10Author Commented:
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()
0
 
AshokCommented:
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?
0
 
SriVaddadiCommented:
Have you tried my solution? Feedback?
0
 
Shay10Author Commented:
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
0
 
Shay10Author Commented:
Just got this working - this was perfect.  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.