I am having trouble using parameters in a stored procedure. I believe I am not passing the parameters in correctly with the C#.
My code is roughly equivalent to:
x
public static string GetCustomer(string storedProcedure, string connectionString)
{
DataTable dt = new DataTable();
using (MySqlConnection con = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand(storedProcedure, con))
{
cmd.Parameters.AddWithValue("_table1", "table1");
cmd.Parameters.AddWithValue("_table2", "table2");
con.Open();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
IDataParameter[] temp = da.GetFillParameters();//returns 2 parameters
da.Fill(dt);//Breaks here with the error below
//Irrelevant code
}
}
return "";
}
Incorrect number of arguments for PROCEDURE tspos.get_customer; expected 2, got 0
DROP PROCEDURE IF EXISTS get_customer;
DELIMITER //
CREATE PROCEDURE get_customer
(
IN _table1 VARCHAR(25),
IN _table2 VARCHAR(25)
)
BEGIN
SET @t1 = CONCAT('SELECT a.*, b.* FROM ', _table1, ' a, ', _table2, ' b');
PREPARE statement FROM @t1;
EXECUTE statement;
DEALLOCATE PREPARE statement;
END //
DELIMITER ;
The calls below work as expected, so I would imagine my issue is in the C#
CALL get_customer('table1', 'table2');
CALL get_customer('table3', 'table4');
Advertisement
Answer
I believe the name of your parameters should be prefixed with an @
sign:
cmd.Parameters.AddWithValue("@_table1", "table1");
cmd.Parameters.AddWithValue("@_table2", "table2");
Also, make sure to set the command type appropriately:
using (MySqlConnection con = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand(storedProcedure, con))
{
//Set command type
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
cmd.Parameters.AddWithValue("@_table1", "table1");
cmd.Parameters.AddWithValue("@_table2", "table2");
IDataParameter[] temp = da.GetFillParameters();//returns 2 parameters
da.Fill(dt);
//Irrelevant code
}
}