Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Error converting data type nvarchar to float Expand / Collapse
Author
Message
Posted Tuesday, May 19, 2009 9:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:54 AM
Points: 48, Visits: 185
Yes John, this is it, what I was looking for :)

Thank you very much for your help!

Peter

Post #719975
Posted Thursday, May 21, 2009 5:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:54 AM
Points: 48, Visits: 185
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
Post #721246
Posted Saturday, May 23, 2009 7:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
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


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #722567
Posted Saturday, May 23, 2009 2:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:54 AM
Points: 48, Visits: 185
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
Post #722628
Posted Saturday, May 23, 2009 4:46 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
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.
Post #722647
Posted Sunday, May 24, 2009 2:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:54 AM
Points: 48, Visits: 185
so what would you suggest ? How to solve this problem, because I need these triggers...
Post #722778
Posted Sunday, May 24, 2009 4:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
You might need to rewrite the trigger. That error cannot magically go away, you need to write sub-queries that do not return more than one value.

If you need help, you need to post code.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #722798
Posted Sunday, May 24, 2009 5:10 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
peter478 (5/24/2009)
so what would you suggest ? How to solve this problem, because I need these triggers...

You may replace badly written triggers with better written ones.
Post #722805
Posted Monday, May 25, 2009 2:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:54 AM
Points: 48, Visits: 185
Here are 2 triggers which are applied on table.

ALTER TRIGGER Cis_OP_No_Trigger1
ON dbo.Cis_OP_No
FOR INSERT, UPDATE
AS
IF (UPDATE (N_Vyrobok) or UPDATE (N_Kplan))
update Cis_OP_No set N_Vyrobok2 = (select ( i.N_Vyrobok + '/' +i.N_KPlan) from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID)
from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID


ALTER TRIGGER Cis_OP_No_Trigger2
ON dbo.Cis_OP_No
FOR INSERT, UPDATE
AS
IF UPDATE (N_Cas100ks)
update Cis_OP_No set N_KS_Hod = (select (((450/i.N_Cas100ks)*100)/8) from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID)
from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID

Table Cis_OP_No:
N_ID int
N_Cas100ks float
N_Vyrobok nvarchar
N_Kplan nvarchar
N_KS_Hod int
N_vyrobok2 nvarchar
N_rucne int

N_ID N_Cas100ks N_vyrobok N_kplan N_KS_Hod N_Vyrobok2 N_rucne
1----------5.73----------456000------001------982-------------456000/001---0
2----------5.73----------123000------001------982-------------123000/001---0
3----------7.71----------888000------003b-----730-------------888000/003b--0

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 =, =! , ....

Thank you
Post #722877
Posted Monday, May 25, 2009 3:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
There is your problem. I bet you that when you run

select (((450/i.N_Cas100ks)*100)/8) from Cis_OP_No WHERE N_ID = @e_Nplan

you will get more than one row returned. As the error message tells you "This is not premitted when the subquery follows =, =! , ...."

As a matter of interest, why do you have two INSERT/UPDATE triggers, one for handling of updates to N_Vyrobok and one for N_Cas100ks? If I were you I would combine them into one.


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #722892
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse