Click here to Skip to main content
12,503,679 members (58,180 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL-Server , +
Hi all,

I am new of the Ado.net.

I am writing sotred procedure and return string from sp. command successfully complete but when I execute error is came

my code is

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/02/2013 14:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)
 
      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            SELECT *
            FROM   sample
            WHERE  id = @Id
 
            SET @Message='record already exist'
 
            SELECT @Message
            return @Message
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'
 
            SELECT @Message
            return @Message
        END
  END

when execute error came
(1 row(s) affected)
Msg 245, Level 16, State 1, Procedure GetSamples, Line 25
Conversion failed when converting the varchar value 'record does not exist' to data type int.

tel me where I went wrong.

Thanks in advance......
Posted 1-Apr-13 23:12pm
Bojjaiah6.2K
Comments
patelvijaay 2-Apr-13 5:21am
   
If you want to return only string then you need to remove "SELECT * FROM ..." query from SP otherwise it will retrurn two tables.
and you don't need to write return statement as you have used "SELECT" and just one thing are you using "ExecuteScaler" method?
Bojjaiah 2-Apr-13 5:27am
   
this is testing how can return string? like that
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

two ways...
1. you can use output parameter
2. use execute scalar.

your sp should be like below(for second option)
USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/02/2013 14:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)
 
      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            SELECT *
            FROM   sample
            WHERE  id = @Id
 
            SET @Message='record already exist'
 
            SELECT @Message
            return -- ***************change
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'
 
            SELECT @Message
            return -- ***************change
        END
  END
  Permalink  
Comments
Bojjaiah 2-Apr-13 5:56am
   
hi aarti when I write c# it is not returning string my code is
try
{
string strconn = ConfigurationManager.ConnectionStrings["strconn"].ConnectionString;

using (SqlConnection SqlConn = new SqlConnection(strconn))
{
using (SqlCommand SqlComm = new SqlCommand("GetSamples", SqlConn))
{
SqlComm.CommandType = CommandType.StoredProcedure;
SqlComm.Parameters.AddWithValue("@Id", 1);
var returnParameters = SqlComm.Parameters.Add("@Message", SqlDbType.VarChar);
returnParameters.Direction = ParameterDirection.ReturnValue;
SqlConn.Open();
SqlComm.ExecuteScalar();
var SPreturnValue = returnParameters.Value;
SqlConn.Close();
}
}
}
catch (SqlException ex)
{
}
catch (Exception ey)
{
}
Aarti Meswania 2-Apr-13 6:00am
   
try
{
string strconn = ConfigurationManager.ConnectionStrings["strconn"].ConnectionString;

using (SqlConnection SqlConn = new SqlConnection(strconn))
{
using (SqlCommand SqlComm = new SqlCommand("GetSamples", SqlConn))
{
SqlComm.CommandType = CommandType.StoredProcedure;
SqlComm.Parameters.AddWithValue("@Id", 1);
SqlConn.Open();
string Message_ = SqlComm.ExecuteScalar(); // this will give you message
SqlConn.Close();
}
}
}
catch (SqlException ex)
{
}
catch (Exception ey)
{
}
Enter your reply belo
Bojjaiah 2-Apr-13 8:24am
   
Thank u Aarti
:)

5+
Aarti Meswania 2-Apr-13 8:49am
   
welcome!
Glad to help you! :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Play like this.......................

ALTER Procedure [dbo].[InsUpdGuest_Info]
@guest_id numeric(18, 0)=Null,
@membership_id numeric(18, 0)=null,
@guest_name nvarchar(100),
@contact1 nvarchar(20)=null,
@contact2 nvarchar(20)=null,
@landline nvarchar(20)=null,
@email nvarchar(50)=null,
@address nvarchar(500)=null,
@date_of_birth datetime=null,
@spouse_dob datetime=null,
@wed_aniversary datetime=null,
@reffered_by numeric(18, 0)=Null,
@IsActive bit = null,
@user_id numeric(18, 0)=null,
@RetVal As nvarchar(20)= Null output
As
Begin
If Not Exists(Select * From tblGuestInfo Where guest_id<>@guest_id And guest_name = @guest_name)
Begin
if Not Exists(Select * From tblGuestInfo Where guest_id=@guest_id)
Begin
Begin Try
begin tran;
DECLARE @InsertedRows TABLE (ID numeric(18,0))
Insert Into tblGuestInfo(membership_id
,guest_name
,contact1
,contact2
,landline
,email
,address
,date_of_birth
,spouse_dob
,wed_aniversary
,reffered_by
,IsActive
,created
,created_by)
/* To get output ID */
OUTPUT inserted.guest_id Into @InsertedRows
/*---------------*/
Values(@membership_id
,@guest_name
,@contact1
,@contact2
,@landline
,@email
,@address
,@date_of_birth
,@spouse_dob
,@wed_aniversary
,@reffered_by
,@IsActive
,getdate()
,@user_id);
Select @RetVal = Convert(nvarchar,ID) From @InsertedRows
commit;
return 1;
End Try
Begin Catch
rollback;
return 0;
End Catch
End
Else
Begin
Begin try
begin tran;
Update tblGuestInfo Set
membership_id = @membership_id,
guest_name = @guest_name,
contact1 = @contact1,
contact2 = @contact2,
landline = @landline,
email = @email,
address = @address,
date_of_birth = @date_of_birth,
spouse_dob = @spouse_dob,
wed_aniversary = @wed_aniversary,
reffered_by = @reffered_by,
IsActive = @IsActive,
updated = getdate(),
updated_by = @user_id
Where guest_id = @guest_id;

Delete From tblGuest_Family_Info Where guest_id = @guest_id;
Delete From tblGuest_Account Where guest_id = @reffered_by And trans_id=@guest_id And trans_type='REF';

Set @RetVal = Convert(nvarchar,@guest_id);
commit;
End try
Begin Catch
rollback;
Set @RetVal = 'False';
End Catch
End
End
Else
Begin
Set @RetVal = 'Already Existing';
End
End
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

If you want to just return the status message...alter your SP like this

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/02/2013 14:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)
 
      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            
            SET @Message='record already exist'
 
            SELECT @Message
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'
 
            SELECT @Message
        END
  END
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

SP never return any varchar value.If you want to take @Message value in your application then try bellow code.

Alter PROC [GetSamples](@Id INT , @Message varchar(100) output)
AS
BEGIN
IF EXISTS(SELECT *
FROM Author
WHERE id = @Id)
BEGIN
SELECT *
FROM Author
WHERE id = @Id

SET @Message='record already exist'
SELECT @Message
END
ELSE
BEGIN
SET @Message='record does not exist'
SELECT @Message
END
END
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

define an output parameter as
@RetVal As nvarchar(20)= Null output

and assign whatever you want...
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160919.1 | Last Updated 2 Apr 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100