Why Mysql Stored Procedure Parameter not found in the collection?

Question

Why Mysql Stored Procedure Parameter not found in the collection?

c# entity-framework mysql stored-procedures

Question

I have problem with entity framework and mysql. I have solved entity framework function parameters problem with this answer

But when I want to add some data with mysql stored procedure I have

Parameter 'kick_Prod_StateID' not found in the collection.

Error in C#.

My procedure like below:

CREATE  PROCEDURE CreateState_(

    IN kick_Prod_StateID INT(11),
    IN kick_ShortName VARCHAR(3),
    IN kick_StateName VARCHAR(50)
)

BEGIN INSERT INTO State_
(
    Prod_StateID_,
    ShortName_,
    StateName_
)
VALUES 
( 
    kick_Prod_StateID ,
    kick_ShortName ,
    kick_StateName
) ; 
END$$

and entity framework created C# code like below :

public ObjectResult<State_> AddState(Nullable<global::System.Int32> prod_StateID_, global::System.String shortName_, global::System.String stateName_)
    {
        ObjectParameter prod_StateID_Parameter;
        if (prod_StateID_.HasValue)
        {
            prod_StateID_Parameter = new ObjectParameter("Prod_StateID_", prod_StateID_);
        }
        else
        {
            prod_StateID_Parameter = new ObjectParameter("Prod_StateID_", typeof(global::System.Int32));
        }

        ObjectParameter shortName_Parameter;
        if (shortName_ != null)
        {
            shortName_Parameter = new ObjectParameter("ShortName_", shortName_);
        }
        else
        {
            shortName_Parameter = new ObjectParameter("ShortName_", typeof(global::System.String));
        }

        ObjectParameter stateName_Parameter;
        if (stateName_ != null)
        {
            stateName_Parameter = new ObjectParameter("StateName_", stateName_);
        }
        else
        {
            stateName_Parameter = new ObjectParameter("StateName_", typeof(global::System.String));
        }

        return base.ExecuteFunction<State_>("AddState", prod_StateID_Parameter, shortName_Parameter, stateName_Parameter);
    }

Error in this line ==>

return base.ExecuteFunction<State_>("AddState", prod_StateID_Parameter, shortName_Parameter, stateName_Parameter);

How can I solve this problem?

Thx

Bilgehan

Popular Answer

There is another solution edmx add or remove some content like "p_" or removed some characters as "kick_" in your case.

make sure name of parameters in both stored procedure and edmx function remain same. if they are different make them same.

MySql Procedure:

CREATE CommentsDelete(TCommentId int)
BEGIN

delete from SmComment where Commentid in
(
select commentid from smcomment where parentid= TCommentId or commentid=TCommentId;
);
END

First Edmx Created This Function:

public int CommentsDelete(Nullable<global::System.Int32> p_CommentId)
        {
            ObjectParameter p_CommentIdParameter;
            if (p_CommentId.HasValue)
            {
                p_CommentIdParameter = new ObjectParameter("p_CommentId", p_CommentId);
            }
            else
            {
                p_CommentIdParameter = new ObjectParameter("p_CommentId", typeof    (global::System.Int32));
            }

            return base.ExecuteFunction("RecursiveCommentsDelete", p_CommentIdParameter);
        }

i replace "p_CommentId" with "TCommentId" now this is working fine for me

Edmx Designer Function:

public int CommentsDelete(Nullable<global::System.Int32> p_CommentId)
        {
            ObjectParameter p_CommentIdParameter;
            if (p_CommentId.HasValue)
            {
                p_CommentIdParameter = new ObjectParameter("TCommentId", p_CommentId);
            }
            else
            {
                p_CommentIdParameter = new ObjectParameter("TCommentId", typeof    (global::System.Int32));
            }

            return base.ExecuteFunction("RecursiveCommentsDelete", p_CommentIdParameter);
        }

Nauman Sharif

View more on Stack Overflow

Bilgehan · Accepted Answer · 1/23/2012 10:27:18 AM

I solved the problem.

The Problem was stored procedure parameters.

When we use mysql and entity framework together, we should give same names to table and value parameters.

I mean the stored procedure should be:

CREATE  PROCEDURE CreateState_(

IN Prod_StateID_ INT(11),
IN ShortName_ VARCHAR(3),
IN StateName_ VARCHAR(50)
)

BEGIN INSERT INTO State_
(
Prod_StateID_,
ShortName_,
StateName_
)
VALUES 
( 
Prod_StateID_ ,
ShortName_ ,
StateName_
) ; 
END$$

Why Mysql Stored Procedure Parameter not found in the collection?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related