Solved

Error converting data type nvarchar to float.

Posted on 2004-08-12
9
854 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

747 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

18 Experts available now in Live!

Get 1:1 Help Now