locked
Error converting data type varchar to int. RRS feed

  • Question

  • User81789783 posted

    i have following sp

    USE [HMS]
    GO
    /****** Object:  StoredProcedure [dbo].[SP_CenterInformation]    Script Date: 11/13/2015 20:09:39 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[SP_CenterInformation]
     @ID int = null,
     @Name varchar(50) = null,
     @FirstName varchar(50) = null,
     @LastName varchar(50) = null,
     @ContactNo1 varchar(20) = null,
     @ContactN02 varchar(20) = null,
     @Email varchar(50) = null,
     @Address varchar(50) = null,
     @Address2 varchar(50) = null,
     @City varchar(50) = null,
     @Website varchar(50) = null,
     @LogoFileName varchar(50) = null,
       --@BinaryImage [image] =null,
     @ModeType varchar(20) = null
    AS
    BEGIN
     if(@ModeType = 'INSERT')
      begin
       INSERT INTO CenterInformation(Name, FirstName,LastName, ContactNo1, ContactN02,
    			--Email, Address, Address2, City, Website, LogoFileName, BinaryImage )
    				Email, Address, Address2, City, Website, LogoFileName)
       values(@Name , @FirstName, @LastName, @ContactNo1, @ContactN02, 
    			--@Email, @Address, @Address2, @City, @Website, @LogoFileName, @BinaryImage)
    			@Email, @Address, @Address2, @City, @Website, @LogoFileName)
      end
     if(@ModeType = 'UPDATE')
      begin
       --UPDATE CenterInformation 
       --set Name = @Name ,
       -- FirstName = @FirstName ,
       -- LastName = @LastName ,
       -- ContactNo1 = @ContactNo1 ,
       -- ContactN02 = @ContactN02 ,
       -- Email = @Email , 
       -- Address = @Address , 
       -- Address2 = @Address2 , 
       -- City = @city , 
       -- Website = @Website , 
       -- LogoFileName = @LogoFileName , 
       -- BinaryImage = @BinaryImage
       -- where ID = @ID
      end
     
     if(@ModeType ='DELETE')
      begin
      -- DELETE FROM CenterInformation where ID = @ID
      end
     
     if(@ModeType = 'GET')
      begin
      -- if(@ID > 0)
      --  select * from CenterInformation where ID = @ID 
      -- else
      --  select * from CenterInformation 
      --end
     
    END
    
    

    and run sp like this

    SP_CenterInformation 'asdd','sadasd','sadad','sadasd','sadasd','asdsa','dasd','asdas','sadasd','asdasd','sadads','INSERT'

    it gives error

    Msg 8114, Level 16, State 5, Procedure SP_CenterInformation, Line 0
    Error converting data type varchar to int.
    please help

    Friday, November 13, 2015 11:48 AM

Answers

  • User753101303 posted

    I meant the problem is that the first parameter of your SP is @ID int = null and that you are passing 'asdd for this parameter. For now it's not related to the content of your SP but just about the SP declaration and how you call it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 13, 2015 12:11 PM

All replies

  • User753101303 posted

    Hi,

    The first parameter is expected to be an int. Use SP_CenterInformation NULL,'asdd','sadasd','sadad','sadasd','sadasd','asdsa','dasd','asdas','sadasd','asdasd','sadads','INSERT'

    If you want to make the ID parameter optional, you would have to name your parameters in the call or to  just place it at the end.

    Friday, November 13, 2015 11:56 AM
  • User81789783 posted

    please note down the table structure

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT TOP 1000 [ID]
          ,[Name]
          ,[FirstName]
          ,[LastName]
          ,[ContactNo1]
          ,[ContactN02]
          ,[Email]
          ,[Address]
          ,[Address2]
          ,[City]
          ,[Website]
          ,[LogoFileName]
          ,[BinaryImage]
      FROM [HMS].[dbo].[CenterInformation]

    where id primary key autogenerated and ignoring image in sp

    Friday, November 13, 2015 11:59 AM
  • User753101303 posted

    I meant the problem is that the first parameter of your SP is @ID int = null and that you are passing 'asdd for this parameter. For now it's not related to the content of your SP but just about the SP declaration and how you call it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 13, 2015 12:11 PM
  • User77042963 posted

    To create separate stored procedures for select, insert, update and delete is a lot easy to maintain. 

    Friday, November 13, 2015 2:45 PM