Why is there no Mysql Stored Procedure Parameter 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

1
1
5/23/2017 12:21:13 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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$$
1
1/23/2012 10:27:18 AM

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

Related Questions

Why is my Entity Framework Code First proxy collection null, and I can't seem to get it to work?

As you correctly observed in the answer to your own question, removing the "virtual" keyword from the collection properties works around the problem, by preventing the Entity Framework from creating a change tracking proxy. However, this is not a solution for many people, because change tracking proxies can be really convenient and can help prevent...
collections entity-framework poco proxies
asked by Pando

Why is Entity Framework calling my stored procedure but returning an incorrect value?

Did you tried that? ...http://www.devtoolshed.com/using-stored-procedures-entity-framework-scalar-return-values...I think your procedure will look like this:...CREATE PROCEDURE [dbo].[GetAuditRecordPageCount] @Count INTEGER AS declare @retVal int set @retVal = ((SELECT COUNT(Id) FROM AuditRecords) / @Count) + 1 select @retVal ...And in the c# code:...
asp.net c# entity-framework sql-server
asked by wkudaka

Entity Framework calling stored procedure expects parameter which was not supplied

The issue is the SQL that EF is producing for NULL values must not be compatible with our actual Sql Server. I'm using EntityFramework 6, but I don't think the implementation has changed since 4.3. ...When I turned on tracing I get the following output after executing similar code to yours above:...exec sp_executesql N'super_group',N'@userName nvar...
c# entity-framework sql sql-server stored-procedures
asked by Dejan Vasic

Entity framework and stored procedure with output parameter returning row (EF will receive complex type)

You can try with ...static void Main(string[] args) { using (SchoolEntities context = new SchoolEntities()) { var outputParameter = new ObjectParameter(“sum”, typeof(decimal)); context.SchoolBudgetForDateRange(new DateTime(2007, 1, 1), new DateTime(2008, 1, 1), outputParameter); Console.WriteLine(outputParameter.Va...
complextype entity-framework-6 output-parameter row stored-procedures
asked by Estuardo Arévalo

Why do we have to write SET FMTONLY OFF in stored procedures when using Entity Framework

I believe the reason is similar to the one for stored procedures that run from SSRS. In summary, when FMTONLY is active, your stored procedure may have some unexpected results. Hence the reason for explicitly turning it off. For details read ...Dealing with the Evil of FMTONLY from SSRS
entity-framework sql-server
asked by David

Executing a stored procedure that returns a result set and has output parameters with Entity Framework

It turns out that the output parameters aren't available until all results are read. I fixed my problem by appending ....ToList()... to the tend of the ...ExecuteFunction()... call. I figured it out by browsing the metadata in Visual Studio. You can see the relevant documentation here: ...I don't see this mentioned in the actual documentation on...
c# entity-framework entity-framework-6 stored-procedures
asked by Tim Coker

Incorrect Syntax Near '0' - when using sp_executeSQL to execute stored procedure with output parameter

In SSMS (T-SQL) you cannot use parameter place-holders, you need to state them explicitly:...exec sp_executesql N'EXEC [dbo].[ApplyList] @oldlist, @username, @newlist, @errormessage output', N'@oldlist int,@username nvarchar(50), @newlist int,@errormessage varchar(2000) output', @oldlist='20',@username=N'mk',@newlist='18',@errormessage...
entity-framework-6 sp-executesql sql-server
asked by Alex K.

Call stored procedure using ExecuteSqlCommand (expects parameters which was not supplied)

This works:...var bookIdParameter = new SqlParameter(); bookIdParameter.ParameterName = "@BookId"; bookIdParameter.Direction = ParameterDirection.Output; bookIdParameter.SqlDbType = SqlDbType.Int; var authors = context.Database.ExecuteSqlCommand("usp_CreateBook @BookName, @ISBN, @BookId OUT", new SqlParameter("@BookName", "Book"), new SqlPa...
c# entity-framework sql-server stored-procedures
asked by Mill

How to call stored procedure from EntityFramework 6 with 'hierarchyid' parameter

Unfortunately, MetaType.GetMetaTypeFromValue does not allow to add types (all supported types are hardcoded). I think you can accomplish your goal with nvarchar parameters and conversions....In your C# code:...var param1 = new SqlParameter("@startingRoot", "/1/"); var param2 = new SqlParameter { ParameterName = "@return", Value = "", Size = 1000, D...
entity-framework entity-framework-6 hierarchyid sql-server stored-procedures
asked by OlegAxenow

Why stored procedure out parameter is not getting read by entity framework's ExecuteSqlCommand method?

out... parameters in SQL Server are fun. You have to repeat yourself ...everywhere.......Specifically, here, you've forgotten to mention it in the parameter list when calling the stored proc. ...dbo.InsertPhones @model, @out... should be ...dbo.InsertPhones @model, @out out.... There may also be a way to omit the parameter list from this string and...
c# entity-framework entity-framework-6 sql sql-server
asked by Damien_The_Unbeliever

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow