<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
T-SQL
»
Error converting data type nvarchar to float
29 posts, Page 2 of 3
««
1
2
3
»»
Error converting data type nvarchar to float
Rate Topic
Display Mode
Topic Options
Author
Message
peter478
peter478
Posted Tuesday, May 19, 2009 9:29 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46,
Visits: 174
Yes John, this is it, what I was looking for :)
Thank you very much for your help!
Peter
Post #719975
peter478
peter478
Posted Thursday, May 21, 2009 5:40 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46,
Visits: 174
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
Jan Van der Eecken
Jan Van der Eecken
Posted Saturday, May 23, 2009 7:22 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 2,319,
Visits: 6,106
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
--------------------------------------------------------------------------
The function of good software is to make the complex appear to be simple. (Grady Booch)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #722567
peter478
peter478
Posted Saturday, May 23, 2009 2:04 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46,
Visits: 174
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
Sergiy
Sergiy
Posted Saturday, May 23, 2009 4:46 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 8:01 AM
Points: 4,557,
Visits: 8,237
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
peter478
peter478
Posted Sunday, May 24, 2009 2:50 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46,
Visits: 174
so what would you suggest ? How to solve this problem, because I need these triggers...
Post #722778
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Sunday, May 24, 2009 4:38 PM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 5:09 AM
Points: 31,526,
Visits: 13,864
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
Sergiy
Sergiy
Posted Sunday, May 24, 2009 5:10 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 8:01 AM
Points: 4,557,
Visits: 8,237
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
peter478
peter478
Posted Monday, May 25, 2009 2:04 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46,
Visits: 174
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
Jan Van der Eecken
Jan Van der Eecken
Posted Monday, May 25, 2009 3:02 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 2,319,
Visits: 6,106
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.
--------------------------------------------------------------------------
The function of good software is to make the complex appear to be simple. (Grady Booch)
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 »
29 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
