Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2009-04-14
12
Medium Priority
?
1,049 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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

607 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