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

3 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.

Give your Valuable Comments

Other Related Posts

© 2010-2012 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.