Solved

Error converting data type nvarchar to float.

Posted on 2004-08-12
9
858 Views
Last Modified: 2008-01-09
I got tihis error running this: Error converting data type nvarchar to float.

update dbA.dbo.books
set listprice = b.listprice,
     costprice = b.costprice
from dbB.dbo.catalog b
where dbA.dbo.books.bookID = b.bookID
and proposalID = @proposalID
and listprice <> b.listprice

0
Comment
Question by:zachvaldez
9 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 11785437
select cast('1,0' as float)    ????

Try

select  distinct b.costprice prc from dbA.dbo.books a join dbB.dbo.catalog b
on a.bookID = b.bookID and a.proposalID = @proposalID and a.listprice <> b.listprice
union
select  distinct b.listprice prc from dbA.dbo.books a join dbB.dbo.catalog b
on a.bookID = b.bookID and a.proposalID = @proposalID and a.listprice <> b.listprice

to see what is the problem.


0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11785460
So what are the datatypes of each column involved?
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11785678
(at least) one of the pairs of varaibles in the query are of differnet data types - one is a float, the other is a varchar
e.g. dbA.dbo.books.listprice & dbB.dbo.books.listprice ....
most likey it is the prices? but it could be the proposalID ?
most of the time this will not be a problem, SLQServer will cast them correctly, but if there is a varchar which does not convert to a float, it will fail. This may be a string like '$10.00', or it may be something like an empty string like '' etc.
check all your data types to find the mismatch, then look at the data carefully to find the 'bad' record(s)


update dbA.dbo.books
set listprice = b.listprice,
     costprice = b.costprice
from dbB.dbo.catalog b
where dbA.dbo.books.bookID = b.bookID
and proposalID = @proposalID
and listprice <> b.listprice
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:zachvaldez
ID: 11785887
The listprice field ihas  a float datatype. because when i run it this way it works(without the listprice and that's what I figured ot the problem.

update dbA.dbo.books
set listprice = b.listprice,
     costprice = b.costprice
from dbB.dbo.catalog b
where dbA.dbo.books.bookID = b.bookID
and proposalID = @proposalID
----and listprice <> b.listprice

I tried doing this as I saw in the book
as in convert(varchar,b.listprice)
and it works. - no error

Now what's the implication converting it to a varchar ? to sql server data type definitions- It's ok it run but would I be
OK though?

0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11785935
if that works it means that in dbA it is a varchar, and in dbB it is a float.
You would probably be best, if you can, to change the data type to be consistent across both tables.
the convert will work OK, but it will be slower then a straight compare.
0
 

Author Comment

by:zachvaldez
ID: 11786343
Also the problem I noticed now in the Update routine.
the line
     ---and listprice <> b.listprice
In my resultset it displays the listprice even though there was not a change in price.

I test it and it did change the price for the first bookid but the second one it still captured it even though
no price change was initiated . They have same proposal id in DBA

update dbA.dbo.books
set listprice = b.listprice,
     costprice = b.costprice
from dbB.dbo.catalog b
where dbA.dbo.books.bookID = b.bookID
and proposalID = @proposalID
and listprice <> b.listprice

Why?? I expect only to display the item with a change and not both.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11786573
what do you mean "no price change was initiated"
if listprice is the same in both tables, then how do you know it 'updated' them?
Is there some other rule that it needs to determine which records to update?
0
 

Author Comment

by:zachvaldez
ID: 11787382
Let me test some more ...
The rule is that only records(specifically price) under a proposal id would change if a change in DBB cost  change and only for that bookID.
If  there are several bookIDs under 1 proposal, the store proc would only change the ones that have changes in dbB leaving other bookids unchange under that specific proposalID.

I tested by changing 1 of two records and I change the update statement to select to display what got changed but I saw 2 records displayed even though only one should change and displayed.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 125 total points
ID: 11787426
OK, so the rue is - only boooks where the COST price has changed?
IN that case you need to add that rule to the SQL : -

update dbA.dbo.books
set listprice = b.listprice,
     costprice = b.costprice
from dbB.dbo.catalog b
where dbA.dbo.books.bookID = b.bookID
and proposalID = @proposalID
and costprice <> b.costprice
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

679 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