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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
AshokSr. Software EngineerCommented:
int rowcnt =  cmd.ExecuteNonQuery;
TextBox1.Text = Convert.ToInt32(rowcnt);
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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
AshokSr. Software EngineerCommented:
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
AshokSr. Software EngineerCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AshokSr. Software EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.