Solved

Error converting data type nvarchar to float.

Posted on 2004-08-12
9
855 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:ScottPletcher
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
 

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
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!

 
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

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!

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

914 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now