Solved

Error converting data type nvarchar to float.

Posted on 2004-08-12
9
863 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

623 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