Click here to Skip to main content
15,949,686 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am writing an insert query in mysql stored procedure .when i am trying to run i am getting error "?IDparam not found in collection" .I didnt give ID in insert as it should be autoincremented and i use it in update and delete later in this procedure.

This is Insert query from my proc: 
<pre>CREATE DEFINER=`root`@`%` PROCEDURE `insertproc`(
IN IDparam INT,
IN Pathparam VARCHAR(100),
IN fileNameparam VARCHAR(100),
IN Countparam VARCHAR(100),
IN CreatedBYparam DATETIME,
IN CreatedDateparam DATETIME,
IN Typeparam VARCHAR(100),
IN Statusparam VARCHAR(100),
IN crudparam VARCHAR(50))
BEGIN

IF(crudparam ='INSERT')
THEN
INSERT INTO pathmaster (
                        Path,
                        pathName,
                       fileName ,
                       Count,
                       CreatedBY,
                       CreatedDate,
                       Type,
                       Status
                        )
                        VALUES(
                             Pathparam,
                         (Select MAX(pathName) from pathmaster)+1,
                       FileNameparam,
                      Countparam,
                      CreatedBYparam,
                    CreatedDateparam,
                    Typeparam,
                    Statusparam
                );
END IF;


below is code to call SP
 MySqlParameter[] mysqlparam = new MySqlParameter[]
  {
     new MySqlParameter("Pathparam",Path.Trim()),

    new MySqlParameter("FileNameparam",filename.Trim()),
    new MySqlParameter("Countparam",count),
    new MySqlParameter("CreatedBYparam",date),
    new MySqlParameter("CreatedDateparam",date),
   new MySqlParameter("Typeparam",strType),
new MySqlParameter("Statusparam","pending"), 
    new MySqlParameter("ACTIONparam","INSERT")
   };
      ds = ExecuteProc("insertproc", mysqlparam);


so what may be the problem

What I have tried:

change ID column to varchar didnt work. there are no any whitespaces in parameters.
Posted
Updated 31-May-17 10:44am
Comments
Michael_Davies 31-May-17 7:52am    
Is ID an auto number column? if so it is not in your parameter list try putting ID in the parameters and put DEFAULT in the place where ID is in your field list;

INSERT INTO pathmaster (ID,
Path,
pathName,
fileName ,
Count,
CreatedBY,
CreatedDate,
Type,
Status
)
VALUES(DEFAULT,
Pathparam,
(Select MAX(pathName) from pathmaster)+1,
FileNameparam,
Countparam,
CreatedBYparam,
CreatedDateparam,
Typeparam,
Statusparam
);
Member 13206008 31-May-17 8:17am    
I tried but still i am getting same error
Member 13206008 31-May-17 8:26am    
ID is PK of table
while calling SP i have changed ACTIONparam to crudparam
new MySqlParameter("crudparam","INSERT")
still same error is repeting

1 solution

Your stored procedure declares a parameter called IDParam. Your C# code does not.

Your stored procedure declares a parameter called crudparam. Your C# code does not.

Your C# code passes in a parameter called ACTIONparam. Your stored procedure has no such parameter.

The list of parameters you pass to the stored procedure from your C# code MUST match the list of parameters for that stored procedure. You MUST pass a value for every inbound stored procedure parameter which does not have a default value.

Since you're not using IDParam within the stored procedure, you simply need to remove the parameter from the stored procedure definition.
 
Share this answer
 
Comments
Member 13206008 1-Jun-17 2:12am    
Thanks for your reply,

IDparam is a primary Key of pathmaster and i am using it in the same storedprocedure(insertproc) in an update query and also insert query for inserting another table taking IDparam as foreighnkey its also in same storedprocedure (insertproc). for that reason i have declared IDparam in SP(insertproc).below is update query where i am using IDparam in same storedproc(insertproc)

IF(crudparam='updatestatus')
THEN

UPDATE pathmaster SET Status= Statusparam Where ID=IDparam;

END IF;
so I cannot remove IDparam is there any way to do this.

and I also changed ACTIONparam to crudparam while calling from C#.now it is

new MySqlParameter("crudparam","INSERT")

still i am getting same error
Richard Deeming 1-Jun-17 6:51am    
It's perfectly simple - if you have an input parameter without a default value, then you MUST pass a value to that parameter when you call the stored procedure.

If you don't, you'll get the error you mentioned in your question.
Member 13206008 6-Jun-17 2:30am    
if i give default value to ID it will inserted by default value and we cannot have correct value for updating or deleting inserted record. how can we do this?
Richard Deeming 6-Jun-17 5:54am    
As I keep telling you, every parameter which does not have a default value MUST be specified in your C# code.

I don't know how else to explain this to you.

If your stored procedure has a parameter, and that parameter does not have a default value, your C# code MUST pass that parameter to the procedure.

If you don't, you'll get the error you mentioned in your question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900