• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 888
  • Last Modified:

Error converting data type nvarchar to float.

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
zachvaldez
Asked:
zachvaldez
1 Solution
 
ispalenyCommented:
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
 
Scott PletcherSenior DBACommented:
So what are the datatypes of each column involved?
0
 
BillAn1Commented:
(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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
zachvaldezAuthor Commented:
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
 
BillAn1Commented:
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
 
zachvaldezAuthor Commented:
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
 
BillAn1Commented:
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
 
zachvaldezAuthor Commented:
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
 
BillAn1Commented:
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

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now