MySqlCommand.LastInsertedId return 0

Question

I'm trying to execute stored procedure and to return last instered id but each time the result is 0.

This is how I do it:

public long InsertActivity(cActivities objAct)
    {          
        long lastID = 0;

        try
        {
            MySqlCommand myCmd = connection.CreateCommand();
            connection.Open();

            myCmd.CommandText = "add_activity";
            myCmd.CommandType = CommandType.StoredProcedure;

            myCmd.Parameters.AddWithValue("@TGUID", objAct.TGUID);
            myCmd.Parameters.AddWithValue("@TItem", objAct.TItemID);
            myCmd.Parameters.AddWithValue("@ActDesc", objAct.ActivityDesc);
            myCmd.Parameters.AddWithValue("@DateStart", objAct.DateStart);
            myCmd.Parameters.AddWithValue("@DateEnd", objAct.DateEnd);
            myCmd.Parameters.AddWithValue("@ActNote", objAct.Note);
            myCmd.Parameters.AddWithValue("@Category", objAct.Category);

            if (myCmd.ExecuteNonQuery() > 0)
            {
                lastID = myCmd.LastInsertedId;

                myCmd.Parameters.Clear();

                connection.Close();

            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("ERROR: " + ex.Message);
        }

        return lastID;
    }

and here is the add_activity procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_activity`(IN `TGuid` VARCHAR(50), IN `TItem` INT, IN `ActDesc` VARCHAR(100), IN `DateStart` DATE, IN `DateEnd` DATE, IN `ActNote` VARCHAR(500), IN `Category` VARCHAR(200), OUT `_lastID` BIGINT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
INSERT INTO activities (TGUID, TItemID, Descr, DateStart, DateEnd, Note, Category) VALUES (TGuid, TItem, ActDesc, DateStart, DateEnd, ActNote, Category);

I'm thinking that the problem is because I'm calling stored proc. instead sql command.

Answer 1

In stored procedures, you must clearly return the id with output command. sometimes stored procedure return more than one rows, maybe your result have more than one rows.

CREATE PROCEDURE insertAndReturn
AS
BEGIN
    INSERT INTO activities (TGUID, TItemID, Descr, DateStart, DateEnd, Note, Category) OUTPUT inserted.TGUID VALUES (TGuid, TItem, ActDesc, DateStart, DateEnd, ActNote, Category);
END
GO