CODEWITHSUNDEEP
c#mysql
tweetysat
tweetysat

Reputation: 2403

Stored Procedures with Mysql connector

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

Answers (2)

prestohuan
prestohuan

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

John M
John M

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

Related Questions