Solved

cant catch returned value from stored procedure ...

Posted on 2014-02-21
9
270 Views
Last Modified: 2014-02-23
Hello,
I have this code c# .net4.51
			var conString = System.Configuration.
				 ConfigurationManager.ConnectionStrings["cn"];
			string strConnString = conString.ConnectionString;

			using (SqlConnection cn = new SqlConnection(strConnString))
			using (SqlCommand cmd = cn.CreateCommand())
			{
				cmd.CommandText = "DicRegisterUser";
				cmd.CommandType = CommandType.StoredProcedure;
				
				cmd.Parameters.AddWithValue("@UserDeviceKey", UserDeviceKey);
				cmd.Parameters.AddWithValue("@UserFullName", UserFullName);
				cmd.Parameters.AddWithValue("@UserEmail", UserEmail);
				var returnParam = new SqlParameter
				{					
					ParameterName = "@PBKey",
					Direction = ParameterDirection.ReturnValue,
					SqlDbType = SqlDbType.NVarChar					 
				};
				returnParam.SqlDbType = SqlDbType.NVarChar;

				cmd.Parameters.Add(returnParam);
				cn.Open();				 
				cmd.ExecuteNonQuery() ;
				cn.Close();				
				return  (string)returnParam.Value;

			}

Open in new window


and This SP in sql sqerver 2008 :
SET NOCOUNT OFF;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DicRegisterUser] 
	@UserDeviceKey as nvarchar(200),
	@UserFullName  as nvarchar(150),
	@UserEmail  as nvarchar(50)
AS
BEGIN

	declare @PBKey as nvarchar(50)
	select @PBKey = 'welcom'
	 insert into PersonalBase(UserKey, FullNameEN, Email) values 
	 (@UserDeviceKey, @UserFullName, @UserEmail)
     
     select @PBKey = PBKey from PersonalBase where PBID = SCOPE_IDENTITY()

if (@PBKey <> '' )
   RETURN @PBKey
else
	RETURN '0'

	
END

Open in new window


But I got error at :
cmd.ExecuteNonQuery() ;
An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code
Additional information: Conversion failed when converting the nvarchar value 'BC8334AD-833C-4D94-88C6-54821FD67C85' to data type int.

btw, the record added successfully,,,
0
Comment
Question by:ethar1
9 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 200 total points
ID: 39878661
Return codes must be integers.  If you want to return a string you need to do it in an OUTPUT parameter or as a result set.

http://technet.microsoft.com/en-us/library/ms188655.aspx
0
 

Author Comment

by:ethar1
ID: 39878926
I change the sp to accept output param
and use this in c#:
				var returnParam = new SqlParameter
				{					
					ParameterName = "@PBKey",
					Direction = ParameterDirection.ReturnValue,
					SqlDbType = SqlDbType.NVarChar
								 
				};
				returnParam.SqlDbType = SqlDbType.NVarChar;
				cmd.Parameters.Add(returnParam);

Open in new window


but I got this error:
Additional information: Procedure or function 'DicRegisterUser' expects parameter '@PBKey', which was not supplied.

the SP:
	@UserDeviceKey as nvarchar(200),
	@UserFullName  as nvarchar(150),
	@UserEmail  as nvarchar(50),
	@PBKey as nvarchar(50) output

AS
BEGIN

	--declare @PBKey as nvarchar(50)
	select @PBKey = 'welcom'
	 insert into PersonalBase(UserKey, FullNameEN, Email) values 
	 (@UserDeviceKey, @UserFullName, @UserEmail)
     
     select @PBKey = PBKey from PersonalBase where PBID = SCOPE_IDENTITY()

if (@PBKey <> '' )
   RETURN @PBKey
else
	RETURN 'ax'

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 39880030
That would probably be because you have defined your parameter in your C# code as a return parameter.  It is not.

Also you keep insisting in doing this:
RETURN 'ax'

That is simply not valid.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:ethar1
ID: 39880182
could you please elaborate mote with some code?
should I add out put param like this:
cmd.Parameters.AddWithValue("@PBKey", "");
0
 

Author Comment

by:ethar1
ID: 39880186
I use this code
				var returnParam = new SqlParameter
				{					
					ParameterName = "@PBKey",					
					SqlDbType = SqlDbType.NVarChar,
					Direction = ParameterDirection.Output,
					Size = 50							 
				};				
				cmd.Parameters.Add(returnParam);

Open in new window


and it give this error:Additional information: Conversion failed when converting the nvarchar value '750CCF51-224E-4EA0-AE5F-3202B925437E' to data type int.

means I got the correct value from the SP but somehow "Conversion failed "
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 150 total points
ID: 39880194
Hi

In the procedure the return should be an int. As in

	@PBKey as int output

AS
BEGIN
    -- etc

if (@PBKey <> 0 )
   RETURN @PBKey
else
	RETURN 
; 
end

Open in new window

0
 

Author Comment

by:ethar1
ID: 39880724
The return value should be nvarchar as required...
0
 

Author Comment

by:ethar1
ID: 39880725
anyway, I solve the problem by using set
set @PBKey= 'xxxx'

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39881250
The return value should be nvarchar as required...
Let's try it once again:  The return value cannot be anything other than numeric.  Period.

This is the definition from SQL Server's BOL for the RETURN (Transact-SQL)  statement (my emphasis):
Syntax
 RETURN [ integer_expression ]
Arguments
integer_expression
Is the integer value that is returned. Stored procedures can return an integer value to a calling procedure or an application.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question