Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

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 RSS subscribe by email Subscribe by Email

7 comments :

Dhiraj said...

Thanks Suresh .....

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

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.

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

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}'

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.

Anonymous said...

Thanks a lot. It worked like a charm

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.