troubleshooting Question

Returning a single character value from a SQL stored procedure using C#.

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL ServerC#
22 Comments1 Solution100 ViewsLast Modified:
I need to call an sp from C# that will return the next highest rank number. I want to do this the proper way. Using a dataset like my other calls is one way but is there a way to convert both of these so when I call the sp from C# it returns the value? The value in this case is a string with a value between '000' and '999'. I have both the C# and the sp listed below.
------------------------------------------------------------------------------------------------------------------
C# code
            try
            {
                NextRankDataSet = new System.Data.DataSet();          /* Define the data set for the list of assignments */
                NextRankDataSet.CaseSensitive = false;

                NextRankDataCommand = new System.Data.SqlClient.SqlCommand();
                NextRankDataCommand.Connection = GPDataConnection;

                NextRankDataCommand.CommandText =
                    "SELECT CONCAT(REPLICATE('0'," + ChrCount.ToString() + "-LEN(CONVERT(NVARCHAR(3),CONVERT(SMALLINT,MAX([RANKNMBR])+1)))),CONVERT(NVARCHAR(3),CONVERT(SMALLINT,MAX([RANKNMBR])+1))) AS NEXTRANK" +
                    "FROM [APDSDATA].[dbo].[PERSASGN] " +
                    "WHERE [GRUPFLAG] = '" + GroupFlag + "'";

                NextRankDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                NextRankDataAdapter.SelectCommand = NextRankDataCommand;
                _commandBuilder = new System.Data.SqlClient.SqlCommandBuilder(NextRankDataAdapter);

                NextRankDataAdapter.Fill(NextRankDataSet);

                txtRankNumber.Text = NextRankDataSet.Tables[0].Rows[0]["NEXTRANK"].ToString();
            }
--------------------------------------------------------------------------------------------------------------------------------------------
Stored procedure

/****** Object:  StoredProcedure [dbo].[GetNextRank]    Script Date: 1/29/2019 5:09:51 PM ******/
DROP PROCEDURE [dbo].[GetNextRank]
GO

/****** Object:  StoredProcedure [dbo].[GetNextRank]    Script Date: 1/29/2019 5:09:51 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ==========================================================================
-- Author:		Richard E. Wheeler - Revered Business Solutions, Inc.
-- Create date: 02/10/2019
-- Description:	Determine the next highest ranking of personnel within each group
-- @I_CHRCOUNT 2 for 2 digit rank numbers and 3 for 3 digit rank numbers
-- ==========================================================================
CREATE PROCEDURE [dbo].[GetNextRank]
	@I_GRUPFLAG NVARCHAR(50),
	@I_CHRCOUNT INT,
	@I_NEXTRANK NVARCHAR(50)
		
AS
BEGIN

	SET NOCOUNT ON

DECLARE @Err_Msg NVARCHAR(4000)
DECLARE @ErrSeverity INT
DECLARE @ErrCode INT

/* If an active transaction cannot be committed, roll it back */
IF XACT_STATE() = -1
	ROLLBACK TRANSACTION
ELSE
/* If an active transaction(s) can be committed, commit it(them). */
IF XACT_STATE() = 1
	WHILE @@TRANCOUNT > 0
		COMMIT TRANSACTION

BEGIN TRANSACTION

BEGIN TRY

	SELECT @I_NEXTRANK = CONCAT(REPLICATE('0',@I_CHRCOUNT-LEN(CONVERT(NVARCHAR(3),CONVERT(SMALLINT,MAX([RANKNMBR])+1)))),CONVERT(NVARCHAR(3),CONVERT(SMALLINT,MAX([RANKNMBR])+1))) 
	FROM [APDSDATA].[dbo].[PERSASGN]
	WHERE [GRUPFLAG]=@I_GRUPFLAG

	SELECT @I_NEXTRANK

	SET @ErrCode = 0

END TRY
BEGIN CATCH
	SELECT @Err_Msg = ERROR_MESSAGE(),
					@ErrSeverity = ERROR_SEVERITY();
	RAISERROR(@Err_Msg,@ErrSeverity,1)

	SET @ErrCode = 1
END CATCH

-- Complete the transaction
IF XACT_STATE() = 1
	COMMIT TRANSACTION
ELSE
IF XACT_STATE() = -1
	ROLLBACK TRANSACTION

RETURN @ErrCode
END



GO
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 22 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros