SQL Server - Conversion Failed when Converting the Varchar Value '24,' to Data Type int - ASP.NET,C#.NET,VB.NET,JQuery,JavaScript,Gridview

SQL Server - Conversion Failed when Converting the Varchar Value '24,' to Data Type int

By: Suresh Dasari Jul 17, 2013

Introduction:

Here I will explain how to solve the problem of “COALESCE function: Conversion failed when converting the varchar value '24,' to data type int in SQL Server”.

Description:

In previous posts I explained many articles relating to solve errors in asp.net, SQL Server, IIS, etc. Now I will explain how to solve the problem of “COALESCE function: Conversion failed when converting the varchar value '24,' to data type int in SQL Server”.

Generally this problem will occur whenever we try to bind integer value to string like as shown below

DECLARE @userdetails VARCHAR(250)

SELECT @userdetails = COALESCE(@userdetails + ',', '') + UserId FROM UserDetails

SELECT @userdetails

In above query UserId is integer datatype because of that if I run above query I will get error like “Conversion failed when converting the varchar value '24,' to data type int.”

To solve this problem we need to convert that interger value to string explicitly like as shown below

DECLARE @userdetails VARCHAR(250)

SELECT @userdetails = COALESCE(@userdetails + ',', '') + CAST(UserId AS VARCHAR(15)) FROM UserDetails

SELECT @userdetails

Once we run above code our problem will get solve and return the output like as shown below

Output

UserDetails

24,25,26,29

I hope it helps you to solve your problem. Happy Coding………

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS

Subscribe by Email

7 comments :

Dhiraj said...: Thanks Suresh .....; July 17, 2013 at 10:32 PM
Anonymous said...: I have a problem into a procedure :
CAST(null as varchar(300)) as RefText,
set RefText = case when ContractNumber is null then ' ' else 'Contract N.' + ContractNumber + ' Period: from ' + convert (varchar(10), FromContractDate, 5 )+ ' to ' + convert (varchar(10), ToContractDate, 5 ) + char(13) + char(10) + char(10)end

When I try tu run the procedure I get the error :
Conversion failed when converting the varchar value 'Contract N.140002Period: from 20140130 to 20151231; February 2, 2014 at 10:27 PM
Anonymous said...: i have into a procedure :

CAST(null as varchar(300)) as RefText,

set RefText = case when NumeroContratto is null then '' else 'Rif. Contratto N.' + NumeroContratto + ' Periodo: dal ' + convert (varchar(10), DaDataContratto, 5 )+ ' al ' + convert (varchar(10), ADataContratto, 5 ) + char(13) + char(10) + char(10)end

when i run the procedure i get this error :

Conversion failed when converting the varchar value 'Rif. Contratto N.140002Periodo: dal20140130al20151231 'to data type int.; February 2, 2014 at 10:31 PM
Er Vikas Sangal said...: Error while executing below code:
Conversion failed when converting the varchar value ' and c.name not in (select columnName from dbo.ATMcol)' to data type int.

DECLARE @SQLQuery AS VARCHAR(4000)
set @SQLQuery=' and c.name not in (select columnName from dbo.ATMcol)'
SELECT c.name 'ColumnName',t.Name 'Datatype',
( case when upper(t.Name) ='VARCHAR' then c.max_length when upper(t.Name) ='CHAR' then c.max_length
else c.max_length/2 end) 'MaxLength',
(case c.is_nullable when 1 then 'NULL' else 'NOT NULL' end) 'IsNullable',
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = Object_ID('ATMCustomers') +@SQLQuery; February 9, 2015 at 2:35 AM
Anonymous said...: HELLO,i have problem regarding "Conversion failed when converting the varchar value '{SESSION|userID}' to data type int" issue. someone please help me

SELECT STF_NAMA
FROM HR_STAF_PERIBADI HP
JOIN PRUSER PR ON HP.STF_USERNAME=PR.USERNAME
WHERE PR.USERID= '{SESSION|userID}'; July 28, 2015 at 11:18 PM
Anonymous said...: declare @a as varchar(100)='Service_Name'
declare @b as varchar(100)='Amount'
declare @sql as varchar(MAX)

SET @sql='select' +CAST(@a as int) + ',' + count (@b) + ' As total from
CenterWise' + ' GROUP by ' + @a
EXEC (@sql)

Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'select' to data type int.; November 4, 2015 at 9:17 PM
Anonymous said...: Thanks a lot. It worked like a charm; February 24, 2016 at 11:11 PM