locked
Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. RRS feed

  • Question

  • User81789783 posted

    i run following

    select  CONVERT(varbinary(max), ISNULL([BinaryImage], '')) as [BinaryImage] from CenterInformation .and i m getting an error following

    Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

    here is table script

    USE [HMS]
    GO
    
    /****** Object:  Table [dbo].[CenterInformation]    Script Date: 12/19/2015 17:52:19 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[CenterInformation](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NULL,
    	[FirstName] [varchar](50) NULL,
    	[LastName] [varchar](50) NULL,
    	[ContactNo1] [varchar](50) NULL,
    	[ContactN02] [varchar](50) 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] [varbinary](max) NULL,
    	[MimeType] [nvarchar](50) NULL,
     CONSTRAINT [PK_CenterInformation] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    

    Saturday, December 19, 2015 12:52 PM

All replies

  • User452040443 posted

    Hi,

    Try:

    select  CONVERT(varbinary(max), COALESCE([BinaryImage], '')) as [BinaryImage] from CenterInformation

    Hope this helps

    Saturday, December 19, 2015 1:48 PM
  • User81789783 posted

    but sp

    what about when run following

    USE [HMS]
    GO
    
    /****** Object:  StoredProcedure [dbo].[SP_CenterInformation]    Script Date: 12/19/2015 19:01:20 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE  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 Varbinary(max)=null,
     @MimeType nvarchar(50)=  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,MimeType )
       values(@Name , @FirstName, @LastName, @ContactNo1, @ContactN02, 
    			@Email, @Address, @Address2, @City, @Website
    			, @LogoFileName
    			, @BinaryImage,@MimeType)
      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
    
    --exec [SP_CenterInformation] 0,'dsfsdfs','fsdfdsfs','dffsd','dfsd','dfsdfsdf','fdgfdgfdg','fdfsdf','dsfsdfs','','INSERT'
    --exec SP_CenterInformation 0 ,'','','','','','','','','','DELETE'
    
    
    --exec [SP_CenterInformation] 0,'sdas','','','','','','','',','','',System.Byte[],'text/plain','INSERT'
    --exec [SP_CenterInformation] 0,dasd','','','','','','','',','','',System.Byte[],'text/plain','INSERT'
    
    GO
    
    
    

    exec SP_CenterInformation  '','','','','','','','','','','','','',  CONVERT(varbinary(max), COALESCE([BinaryImage], '')),'GET'

    it gives error

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'CONVERT'.

    Saturday, December 19, 2015 2:02 PM
  • User452040443 posted

    I believe you can use null instead of convert.

    Hope this helps

    Saturday, December 19, 2015 4:04 PM
  • User77042963 posted

    You need to check the number of parameters and use a variable to pass the value:

    decLARE @BinaryImage varbinary(max)= CONVERT(varbinary(max),'')
    exec SP_CenterInformation '','','','','','','','','','','','', @BinaryImage ,'GET'

    Saturday, December 19, 2015 7:14 PM
  • User753101303 posted

    Hi,

    Or tell what you are trying to do. It doesn't seems to make sense.

    My understanding is that if the image is null you are trying to convert an "empty string" to a varbinary value which doesn't really make sense. IMO either you don"t have an image an store NULL or you do have an image and store its data.

    Edit: and you have still system.Byte[] as a parameter while I answered previously about this particular issue when you had causing to write the type name rather than the data in your SQL string.

    Saturday, December 19, 2015 7:55 PM