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.CODEDUserBa se objUserData = Session["UserData"] as Authentication.CODEDUserBa se;
int iTenant = 0;
string stenant = " ";
iTenant = Convert.ToInt32(objUserDat a.Tenant);
stenant = iTenant.ToString();
SqlCommand cmd = new SqlCommand("MoveSource", new SqlConnection("server=some IP;databas e=someData base;UID=s omelogin;P WD=somepwd ;"));
//Label1.Text = "Sending {" + stenant + "} {" + s1 + "} {" + s2 + "} {" + sValue + "}"; //verify vars correct that are being sent
cmd.CommandType = CommandType.StoredProcedur e;
cmd.Parameters.AddWithValu e("@Subsys tem", iTenant);
cmd.Parameters.AddWithValu e("@FromPr oject", iFromInput);
cmd.Parameters.AddWithValu e("@Criter ia", iCriteria);
cmd.Parameters.AddWithValu e("@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!
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.CODEDUserBa
int iTenant = 0;
string stenant = " ";
iTenant = Convert.ToInt32(objUserDat
stenant = iTenant.ToString();
SqlCommand cmd = new SqlCommand("MoveSource", new SqlConnection("server=some
//Label1.Text = "Sending {" + stenant + "} {" + s1 + "} {" + s2 + "} {" + sValue + "}"; //verify vars correct that are being sent
cmd.CommandType = CommandType.StoredProcedur
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
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!
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);
TextBox1.Text = Convert.ToInt32(rowcnt);
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?
//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
@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
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
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().ToSt ring()
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().ToSt
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
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().ToSt ring()
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().ToSt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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
Thanks
ASKER
Just got this working - this was perfect. Thank you!