Elliott Finch Elliott Finch - 2 months ago 11
MySQL Question

What causes the "Subquery returns more than 1 row" error?

I'm trying to add a new Back Order to the Back Order table in my MYSQL database. The problem I'm getting is that when I try to select the Supplier_ID to add it to the Back Order Table, the program gives me an error that says "Subquery returns more than 1 value".

Supplier Table (Unsure how to properly structure it)


| Supplier_ID | SupName |

| 1| EWS |

| 6 | Franklin Bakery |


Back Order Table


| BackOr_ID | Supplier_ID | BackOr_Org_Date | Sup_Name |


The MYSQL query I'm currently using is:



CREATE DEFINER=`root`@`%` PROCEDURE `sp_BackOrder`(
in supname varchar(50),
in dat date
)
BEGIN
insert into Back_Order
(
Supplier_ID,
BackOr_Org_Date,
Sup_Name
)
values
(
(select Supplier_ID from Supplier Where SupName = supname),
dat,
supname
);

END





The C# Code that uses the query is:



MySqlCommand cmd = new MySqlCommand("sp_BackOrder", new MySqlConnection(*Connection String*));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("supname", Supp_Name)); //Supp_Name is the Supplier Name (a string variable)
cmd.Parameters.Add(new MySqlParameter("dat", DateTime.Now.ToShortDateString())); //dat is the date the Order is placed
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();





The only error I get is that the Subquery (Which I assume is the Select Query) returns more than 1 value. I've tried looking around and even looked on Stackoverflow but couldn't find any solution to the problem. Any help would be appreciated.

Answer

The parameter name supname of procedure and column name SupName are same. Change parameter name to something other than column name.

CREATE DEFINER=`root`@`%` PROCEDURE `sp_BackOrder`(
in selectedSupname varchar(50),
in dat date
)
BEGIN
insert into Back_Order
(
Supplier_ID,
BackOr_Org_Date,
Sup_Name
)
values
(
(select Supplier_ID from Supplier Where SupName = selectedSupname),
dat,
selectedSupname
);

END