May 18, 2009 at 7:00 am
Hi I am testing one stored procedure.
I have this Test_table :
id int
name varchar
data float
I would like to update all records by condidtion:
Update Test_table set data = '27.55' where name = 'Peter'
When I tested it and appiers error with converting nvarchar to float .
Could you help me please.
Thanks
May 18, 2009 at 7:29 am
Hi, can you post the stored procedure code?
May 19, 2009 at 2:15 am
ALTER Procedure dbo.UPdata
(
@e_Nplan char(20),
@e_Ncas char(20)
)
As
DECLARE @Nplan as char(20)
DECLARE @Ncas as float(2)
SET @Nplan = @e_Nplan
SET @Ncas = CONVERT(float(2) , @e_Ncas)
Update Cis_OP_NO SET N_Cas100ks = @Ncas where N_Kplan =@Nplan
return
May 19, 2009 at 2:34 am
Ok you need to make some var changes.
You are using a CHar(20) which has a set lenght and SQL is struggling to convert that.
Have you tried changed @e_Ncas to a Float in the Procedure declaration?
I can't see any reason why it is a char to begin with...
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 19, 2009 at 3:52 am
peter478 (5/19/2009)
ALTER Procedure dbo.UPdata(
@e_Nplan char(20),
@e_Ncas char(20)
)
As
DECLARE @Nplan as char(20)
DECLARE @Ncas as float(2)
SET @Nplan = @e_Nplan
SET @Ncas = CONVERT(float(2) , @e_Ncas)
Update Cis_OP_NO SET N_Cas100ks = @Ncas where N_Kplan =@Nplan
return
To much typing.
Keep it short, do only what needs to be done.
ALTER Procedure dbo.UPdata
@e_Nplan char(20),
@e_Ncas char(20)
As
Update Cis_OP_NO
SET N_Cas100ks = @e_Nplan -- I don't see any point of passing a char(20) value from one var to another char(20) one
where N_Kplan = CONVERT(float(2) , LTRIM(RTRIM(@Nplan))) -- extra spaces in char variables may cause conversion to fail
GO
_____________
Code for TallyGenerator
May 19, 2009 at 8:52 am
Hi Chris,
The reason, why I need to use type Char is that MS access doesnt work with Type float.For that reason I use Char.My entry for @e_Ncas is a number with comma. ( e.g. 15,79)
Of course when I change it to float it works but It doesnt solve my problem.
I only need to know how to convert char to float under SQL.Thats all.
Thank you
Peter
May 19, 2009 at 9:01 am
converting a char to a float will only work if the char string is in fact a valid number.
declare @string1 char(20), @string2 float
set @string1 = '15,76' -- THIS WILL FAIL
--set @string1 = '15.76' -- THIS WILL WORK
print @string1
set @string2 = convert(float,@string1)
print @string2
May 19, 2009 at 9:02 am
Hi Sergiy,
I have just tested your code but the same error message appiers.
When I use whole number e.g. 15 there is not problem, but if I use e.g. 15,28
appiers error message.
ALTER Procedure dbo.UpdateTB
@e_Nplan char(20), /* e.g. 112e */
@e_Ncas char(20) /* e.g. 15,28 */
As
Update Cis_OP_NO
SET N_Cas100ks = CONVERT(float(2) , LTRIM(RTRIM(@e_Ncas)))
Where N_Kplan = @e_Nplan
return
Thanks
May 19, 2009 at 9:14 am
Thank you John ,
now it works .... so I will have to modify my source code to substitute " , " to " . "
May 19, 2009 at 9:19 am
I was just gonna ask if your numbers were always formatted like '15,28' with commas then you could use
declare @string1 char(20), @string2 float
set @string1 = '152,23'
print @string1
set @string2 = convert(float,replace(@string1,',','.'))
print @string2
May 19, 2009 at 9:29 am
Yes John, this is it, what I was looking for 🙂
Thank you very much for your help!
Peter
May 21, 2009 at 5:40 am
Hi ,
one more thing:
ALTER Procedure dbo.UPdata
(
@e_Nplan char(20),
@e_Ncas char(20)
)
As
Update Cis_OP_NO
SET N_Cas100ks = convert(float,replace(@e_Ncas,',','.'))
Where N_Kplan = @e_Nplan
return
Error message:
Subquery returned more than 1 value. This is not premitted when the subquery follows =, =! , ....
Could you give some suggests how to solve it?
Thanks
May 23, 2009 at 7:22 am
peter478 (5/21/2009)
Hi ,one more thing:
ALTER Procedure dbo.UPdata
(
@e_Nplan char(20),
@e_Ncas char(20)
)
As
Update Cis_OP_NO
SET N_Cas100ks = convert(float,replace(@e_Ncas,',','.'))
Where N_Kplan = @e_Nplan
return
Error message:
Subquery returned more than 1 value. This is not premitted when the subquery follows =, =! , ....
Could you give some suggests how to solve it?
Thanks
Peter,
The code you posted above can't be the entire stored proc code, since the error message talks about a subquery. Please post the entire procedure as well as a script to create the table Cis_OP_NO and to populate it with some test data. Makes everyone's life easier and will get you proper answers much quicker.
Regards,
Jan
May 23, 2009 at 2:04 pm
Hi ,
There is the only stored procedure which is called through MS access.
On this table are some triggers but I think that it should not be the problem.
Triggers provides updates of values when someone change a specific field of table.
If I update row (id = 3) from the table there is not problem ...proc works.
but when I want to update more then one row with same value (N_kplan = 112a) then appiers error.
Tbale Cis_OP_No was created manualy via MS Access
Cis_OP_No:
------------
id int
N_Kplan varchar
N_Cas100ks float
etc...
id N_kplan N_Cas100ks
----------------------------------
1 112a 15.78
2 112a 78.56
3 456b 78
4 741C 30.24
May 23, 2009 at 4:46 pm
peter478 (5/23/2009)
Hi ,On this table are some triggers but I think that it should not be the problem.
That's exactly the problem.
Badly written triggers with subqueries built with an assumption that only 1 row is updated always cause this kind of problems.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply