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
C#Microsoft SQL Server
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();
            }

Open in new window

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

Open in new window

ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 22 Comments.
Start Free Trial
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