Conversion failed when converting the varchar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Conversion failed when converting the varchar

Author

Topic

eddy556
Starting Member

36 Posts

Posted - 2008-11-25 : 06:23:00

First of all I am BRAND new to stored procedures so please bare with me

I am getting the following error:

quote:

Conversion failed when converting the varchar value 'SELECT Company
FROM dbo.Supplier
WHERE dbo.Supplier.SUPPCODE = (SELECT dbo.Material.SUPPCODE
FROM dbo.Material
WHERE dbo.Material.mtrlcode = 52338)' to data type int.

I have tried everything to fix it, I know it must be soo obvious lol.

Below is my SP and my C# code.

Stored Procedure

USE [COSHH2008]
GO
/****** Object:  StoredProcedure [dbo].[sp_Material]    Script Date: 11/25/2008 09:36:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Material] @MtrlCode Integer
AS


DECLARE @SupplierName VARCHAR(1000)

SELECT @SupplierName =
'SELECT Company
FROM dbo.Supplier
WHERE dbo.Supplier.SUPPCODE = (SELECT dbo.Material.SUPPCODE
								FROM dbo.Material
								WHERE dbo.Material.mtrlcode = ' + CAST(@MtrlCode as VARCHAR(1000)) + ')'


RETURN @SupplierName;

C# Code

public String getValue(String StoredProcedureName, Int32 MaterialCode)
        {
            SqlCommand mycmd;
            SqlDataAdapter myad;
            DataTable dt = new DataTable();
            String result;
            SqlDataReader rdr;

            SqlConnection lsqlconn = new SqlConnection(sconnstr);

          

                      mycmd = new SqlCommand(StoredProcedureName, lsqlconn);

                mycmd.CommandType = CommandType.StoredProcedure;

                mycmd.Parameters.Add(new SqlParameter("@MtrlCode", System.Data.SqlDbType.Int, 4)).Value = MaterialCode;

                        
   
                myad = new SqlDataAdapter(mycmd);
                myad.Fill(dt);
                result = Convert.ToString(mycmd.Parameters["@SupplierName"].Value);
       
                lsqlconn.Close();

                return result;


        
        }

Many many thanks for your help

It's much appreciated!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-11-25 : 06:31:39

change your SP to read



USE [COSHH2008]
GO
/****** Object:  StoredProcedure [dbo].[sp_Material]    Script Date: 11/25/2008 09:36:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Material] @MtrlCode Integer
AS


DECLARE @SupplierName VARCHAR(1000)

SELECT @SupplierName =
'SELECT Company
FROM dbo.Supplier
WHERE dbo.Supplier.SUPPCODE = (SELECT dbo.Material.SUPPCODE
								FROM dbo.Material
								WHERE dbo.Material.mtrlcode = ' + CAST(@MtrlCode as VARCHAR(1000)) + ')'


RETURN

eddy556
Starting Member

36 Posts

Posted - 2008-11-25 : 06:45:20

Thanks thats got rid of the error - however I'm trying to return the SupplierName, whereas at the moment it is returning the MtrlCode

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-11-25 : 07:02:47

change your code to


USE [COSHH2008]
GO
/****** Object:  StoredProcedure [dbo].[sp_Material]    Script Date: 11/25/2008 09:36:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Material] @MtrlCode varchar(max) output
AS


DECLARE @SupplierName VARCHAR(1000)

SELECT @SupplierName =
'SELECT Company
FROM dbo.Supplier
WHERE dbo.Supplier.SUPPCODE = (SELECT dbo.Material.SUPPCODE
								FROM dbo.Material
								WHERE dbo.Material.mtrlcode = ' + @MtrlCode  + ')'


RETURN

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-11-25 : 07:04:52

Sorry use this


USE [COSHH2008]
GO
/****** Object:  StoredProcedure [dbo].[sp_Material]    Script Date: 11/25/2008 09:36:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Material] @MtrlCode varchar(max) output
AS




SELECT @@MtrlCode  =
'SELECT Company
FROM dbo.Supplier
WHERE dbo.Supplier.SUPPCODE = (SELECT dbo.Material.SUPPCODE
								FROM dbo.Material
								WHERE dbo.Material.mtrlcode = ' + @MtrlCode  + ')'


return

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources