[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Conversion failed when converting the nvarchar value to data type int.

Posted on 2009-04-14
12
Medium Priority
?
1,040 Views
Last Modified: 2012-05-06
I get back the following SQL Error when I run the below mentioned code. Any help is appreciated.

Conversion failed when converting the nvarchar value 'A4216' to data type int.
update ProceduresFeeSchedule 
set ProceduresFeeSchedule.Allowed = DMEModified.Allowed 
FROM    DMEModified 
             INNER JOIN ProceduresFeeSchedule ON DMEModified.HPCPC = ProceduresFeeSchedule.ProceduresId
Where ProceduresFeeSchedule.FeeScheduleID = 11

Open in new window

0
Comment
Question by:Jeff S
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24143204
Either DMEModified.Allowed or DMEModified.Allowed has non-numeric data in it.

First try putting quotes in your where clause:

Where ProceduresFeeSchedule.FeeScheduleID = '11' (this makes the 11 a string instead of a number)

If that doesn't do it then let me know
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24143208
ok, in which field is the value A4216?

0
 
LVL 7

Author Comment

by:Jeff S
ID: 24143227
DMEModified.HPCPC
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24143246
then, this change should do:
update ProceduresFeeSchedule 
set ProceduresFeeSchedule.Allowed = DMEModified.Allowed 
FROM    DMEModified 
             INNER JOIN ProceduresFeeSchedule ON DMEModified.HPCPC = cast(ProceduresFeeSchedule.ProceduresId as varchar(100))
Where ProceduresFeeSchedule.FeeScheduleID = 11

Open in new window

0
 
LVL 15

Expert Comment

by:oobayly
ID: 24143254
You're trying to compare an int column to a varchar column
ie.
SELECT * FROM foo WHERE IntColumn = 'abc';

The above command causes an error as the server is attempting to convert 'abc' to an integer so it can be compared to int column.

Note, this works, as '123' can be converted to an int
SELECT * FROM foo WHERE IntColumn = '123';

Basically, either
ProceduresFeeSchedule.ProceduresId is a VARCHAR column and  DMEModified.HPCPC is an INT column
or
DMEModified.Allowed is a VARCHAR column and ProceduresFeeSchedule.Allowed is an INT column
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24143256
side note: please use table aliases to make your queries more readable:
update pfs
set pfs.Allowed = m.Allowed 
FROM ProceduresFeeSchedule pfs 
JOIN DMEModified m 
  ON m.HPCPC = cast(pfs.ProceduresId as varchar(100))
Where pfs.FeeScheduleID = 11

Open in new window

0
 
LVL 7

Author Comment

by:Jeff S
ID: 24143287
0 rows affected - should have been about 1800 rows. I am not getting the error though and thats positive news.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24143350
angel
I used your coding (snippet below). It looks like it is the cast you created, it is not linking the tables or at least not linking the fields. I get back 0 rows affected.
 

update pfs
set pfs.Allowed = m.Allowed 
FROM ProceduresFeeSchedule pfs 
JOIN DMEModified m 
  ON m.HPCPC = cast(pfs.ProceduresId as varchar(100))
Where pfs.FeeScheduleID = 11

Open in new window

0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24143377
Your are linking a text field to a number field.  How many rows did you expect to get back?

Is their a field in ProceduresFeeSchedule that would contain 'A4216'  or is there a field in DMEModified that contains ProceduresId?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24143387
well, the error has gone :)

if the query no returns 0 row, so it is.
the problem is likely a logical problem, for example are you sure you want to join on those fields?
it could be also that really, for that join, there are no rows matching...

please double-check
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24143519
OK there is no relationship between the 2 tables I found. When I put both tables in Query Designer nothing linked and i got a CROSS JOIN. Any ways around this?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24145416
can you show some sample row data from the 2 tables, and what the resulting output should be, please?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question