How to solve error conversion failed when converting the nvarchar value '24VAC/D

Question

1.00/5 (1 vote)

See more:

I work on SQL server 2014 I get error when run statement below

error say

Conversion failed when converting the nvarchar value '24VAC/DC' to data type int.

I got error when execut dynamic sql

EXEC (@SQL)
so how to solve this error please
data sample

SQL

IF OBJECT_ID('dbo.TAllfeatures') IS NOT NULL
 DROP TABLE dbo.TAllfeatures
 IF OBJECT_ID('dbo.TCondition') IS NOT NULL
 DROP TABLE dbo.TCondition
 IF OBJECT_ID('dbo.TPartAttributes') IS NOT NULL
 DROP TABLE dbo.TPartAttributes
 IF OBJECT_ID('dbo.TAllData') IS NOT NULL
 DROP TABLE dbo.TAllData
    
 CREATE TABLE [dbo].[TAllfeatures](
     [ZPLID] [int] NULL,
     [ZfeatureKey] [bigint] NULL,
     [FeatType] [int] NULL,
     [AcceptedValueID] [int] NULL,
     [IsNumericValues] [int] NULL
 ) ON [PRIMARY]
    
 GO
 INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, NULL, 0, 0, 0)
 INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730001, 2044, 155, 0)
 INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730011, 2044, 274, 1)
 INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730036, 2044, 271, 0)
    
    
 CREATE TABLE [dbo].[TCondition](
     [TradeCodeControlID] [int]  NOT NULL,
     [VersionYear] [int] NULL,
     [Version] [float] NULL,
     [CodeTypeID] [int] NULL,
     [RevisionID] [bigint] NULL,
     [Code] [varchar](20) NULL,
     [ZPLID] [int] NULL,
     [ZfeatureKey] [bigint] NULL,
     [ZfeatureType] [nvarchar](200) NULL,
     [EStrat] [nvarchar](2500) NULL,
     [EEnd] [nvarchar](2500) NULL
 ) ON [PRIMARY]
    
    
    
    
 INSERT [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (8123, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730001, NULL, N'In(''Surge Protector'',''Surge Protector for Media Lines Only'',''Outlet Strip, Surge Protector'',''PDU, Surge Protector'',''Surge Lightning Arrester'',''Surge Arrester'',''Surge Protection Module'',''Lightning Arrester'',''Lightning Current Arrester'',''Protection Device'',''Surge Voltage Equipment'',''Isolated'',''Surge Protection'',''Coaxial'',''Base Element'')', N'')
 INSERT [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (8124, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730036, NULL, N'>1000', N'')
    
    
    
 CREATE TABLE [dbo].[TPartAttributes](
     [PartID] [int] NOT NULL,
     [ZfeatureKey] [bigint] NULL,
     [AcceptedValuesOption_Value] [float] NULL,
     [Name] [nvarchar](500) NOT NULL
 ) ON [PRIMARY]
    
 GO
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128078, 1505730036, 24, N'24VAC/DC')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128078, 1505730001, NULL, N'Surge Voltage Equipment')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128089, 1505730036, 5, N'5V')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128089, 1505730001, NULL, N'Attachment Plug')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (27912821, 1505730001, NULL, N'Surge Protection Module')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (27912821, 1505730036, 480, N'480V')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (32817870, 1505730001, NULL, N'Surge Protector')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (32817870, 1505730036, NULL, N'120V, 240V')
    
 CREATE TABLE dbo.TAllData
         (
    
         PartID INT,    
         Code VARCHAR(20),    
         CodeTypeID INT,    
         RevisionID BIGINT,    
         ZPLID INT,    
         ConCount INT
    
         )
         SET ANSI_PADDING ON
 SET ANSI_WARNINGS ON
            
 DECLARE @ConStr nvarchar(max)=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , CAST(EStrat AS NVARCHAR(2500)),')')   --ValueName
                 FROM dbo.TCondition CC  INNER JOIN dbo.TAllfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
                 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
                
    
                    
                    
    
                    
                 DECLARE @Sql nvarchar(max)= CONCAT('INSERT INTO dbo.TAllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount)',' SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount
                    
                 FROM 
                 dbo.TPartAttributes PM with(nolock) 
                 INNER JOIN    dbo.TCondition Co with(nolock) ON Co.ZfeatureKey = PM.ZfeatureKey ',                
                 'Where (1=1 and  ' ,  @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
                 ' Having Count(1)>= ',(SELECT COUNT(1) FROM TCondition with(nolock)))
    
            
            --print @SQL
             EXEC (@SQL)

What I have tried:

SQL

DECLARE @ConStr nvarchar(max)=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ''', CC.ZfeatureKey , '''', IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , LEFT(EStrat, 1), '''', SUBSTRING(EStrat, 2, LEN(EStrat) -1), ''')')   --ValueName
                       FROM dbo.TCondition  CC INNER JOIN dbo.TAllfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
                       FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')

Posted 1-Dec-21 7:43am

ahmed_sa

Updated 1-Dec-21 23:51pm

Add a Solution

Comments

0x01AA 1-Dec-21 13:48pm

The error message is very ok. Why do you think '24VAC/DC' can be converted to int?

Dave Kreskowiak 1-Dec-21 14:04pm

You can't convert that string an an integer. You have to trim the string down to just the digits before you can do the conversion.

ahmed_sa 1-Dec-21 14:08pm

so can you show me if possible

2 solutions

Solution 1

The error is pretty explicit: it is saying the the string "24VAC/DC" is not a number - and it isn't. It contains a number, yes - but it has other data in there and that can't be converted.

SQL is a database system, and it is very good at storing information - so one of the things it will not do automatically for you is throwing information away, as it has no idea what is "relevant" and what isn't. So if you tell if to convert a string and that would require throwing away info it refuses to do it.

You can't "solve this problem" because it isn't a problem: it's to do with your data, and the way you are trying to misuse it. If you want a field converted to a number, then you need to process the data in your presentation language - which knows about your actual data source - to extract the relevant numeric information and pass that to a numeric column in SQL (perhaps as well as the original text, but not necessarily). That way, you don't complicate your database and every app that uses it with data that may change format in the future: you normalise you data before it is stored so it's relevant.

Posted 1-Dec-21 20:24pm

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2021-12-01T23:51:00

How to solve the problem:
1. Uncomment the line that prints the SQL and comment out the Exec i.e.

SQL

print @SQL
             --EXEC (@SQL)

2. Run your code and examine the output

SQL

INSERT INTO dbo.TAllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount) SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount
                    
                 FROM 
                 dbo.TPartAttributes PM with(nolock) 
                 INNER JOIN    dbo.TCondition Co with(nolock) ON Co.ZfeatureKey = PM.ZfeatureKey Where (1=1 and   (PM.ZfeatureKey= 1505730001 And Name In('Surge Protector','Surge Protector for Media Lines Only','Outlet Strip, Surge Protector','PDU, Surge Protector','Surge Lightning Arrester','Surge Arrester','Surge Protection Module','Lightning Arrester','Lightning Current Arrester','Protection Device','Surge Voltage Equipment','Isolated','Surge Protection','Coaxial','Base Element')) Or (PM.ZfeatureKey= 1505730036 And Name >1000) ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID  Having Count(1)>= 2

I happened to spot the problem immediately but you asked how to solve the problem so
3. Remove the

SQL

INSERT INTO dbo.TAllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount)

and run the SELECT ... note you still get the error, so you are not trying to insert text into an Int column. Tick.

4. Remove the WHERE clause and run the SQL - The error goes away. Ergo the problem is in your WHERE clause

5. Put the 2nd part of the where clause back in place (I am going to completely ignore the pointless 1=1)

SQL

Where 
(
	(
		PM.ZfeatureKey= 1505730001 
		And [Name] In('Surge Protector','Surge Protector for Media Lines Only','Outlet Strip, Surge Protector',
		'PDU, Surge Protector','Surge Lightning Arrester','Surge Arrester','Surge Protection Module','Lightning Arrester',
		'Lightning Current Arrester','Protection Device','Surge Voltage Equipment','Isolated','Surge Protection','Coaxial','Base Element')
	)

6. Run the SELECT again - the error is not reported. Ergo the problem must be in the bit we haven't checked yet

SQL

Or 
	(
		PM.ZfeatureKey= 1505730036 
		and name >1000
	)

7. Now look at the columns used there ZfeatureKey is a BigInt so that is fine, but name is a varchar and you are comparing it to an integer value.

8. For the sake of proving that, change the code as below and run it again

SQL

and name >'1000'

You will notice there is now no error reported

9. Finding how that ended up in the SQL was a little harder. Search for name >1000 and you can't find it.

10. Search for >1000 and you discover the culprit is

SQL

INSERT [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (8124, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730036, NULL, N'>1000', N'')

11. You have entered the wrong data for the condition into the column EStrat. You're on your own now because I have no idea what you are trying to do with this code.