Stored Procedures with Mysql connector

Question

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?

I don't remember how, but you need to set the `Direction` or `ParameterDirection` property *(or something like that)* to `Out` on the second parameter. — Anders Marzi Tornblad, Feb 08 '12 at 14:26
Ok, it's working with adding : command.Parameters["@id"].Direction = System.Data.ParameterDirection.Output; Thanks atornblad. — tweetysat, Feb 08 '12 at 14:48
For those interested, I showed a MySQL / c# Visual Studio 2015 working example [HERE](http://stackoverflow.com/a/38706288). That situation was one of IN and `OUT` parameters. The focus naturally was on the `OUT`. — Drew, Aug 01 '16 at 19:36

score 3 · Answer 1 · answered May 28 '12 at 18:27

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());

score 0 · Answer 2 · answered Mar 18 '13 at 16:10

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

Stored Procedures with Mysql connector

2 Answers2