Reputation: 2403
I'm trying to call a simple stored procedure in c# 2010. With only a IN argument it's ok, but now with a OUT argument it's not working.
In phpmyadmin :
drop procedure if exists insert_artist;
delimiter $$
create procedure insert_student(IN name VARCHAR(100), OUT id INT)
begin
insert into student(name) values(name);
set id = last_insert_id();
end$$
delimiter ;
Then using
call insert_student("toto",@id);
select @id;
It's working fine.
Now, in c# :
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandText = "insert_student";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@name", "xxxx");
command.Parameters.AddWithValue("@id",MySqlDbType.Int32);
command.ExecuteNonQuery();
Console.WriteLine("**** " + command.Parameters["@id"].Value);
}
}
Gives me an exception when executing ExecuteNonQuery()
:
OUT or INOUT argument 2 for routine insert_student is not a variable or NEW pseudo-variable in BEFORE trigger
The same thing without the out argument in the stored procedure is working fine. Where is my mistake?
Upvotes: 3
Views: 12878
Reputation: 1
my below code works
pls check if it's ok for you.
InsertQuery = New MySqlCommand("xxxxxx")
InsertQuery.Connection = Connection
InsertQuery.CommandType = Data.CommandType.StoredProcedure
InsertQuery.Parameters.AddWithValue("IN_xxx", str_xxxx)
InsertQuery.Parameters.Add("OUT_LastID", MySqlDbType.Int32).Direction = ParameterDirection.Output
IQ = InsertQuery.ExecuteReader()
IQ.Read()
LASTID = InsertQuery.Parameters("OUT_LastID").Value
Upvotes: 0
Reputation: 14688
A fuller example:
if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(nameOfStoredRoutine, connection);
cmd.CommandType = CommandType.StoredProcedure;
//input parameters
for (int i = 0; i < (parameterValue.Length / 2); i++)
{
cmd.Parameters.AddWithValue(parameterValue[i, 0], parameterValue[i, 1]);
cmd.Parameters[parameterValue[i, 0]].Direction = ParameterDirection.Input;
parameterList = parameterList + parameterValue[i,0] + " " + parameterValue[i,1] + " ";
}
//single output parameter
cmd.Parameters.AddWithValue("@output", MySqlDbType.Int32);
cmd.Parameters["@output"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery(); //Execute command
this.CloseConnection(); //close connection
return Convert.ToInt32(cmd.Parameters["@output"].Value.ToString());
Upvotes: 3