how to get an auto incrementing stored procedure in mysql

Question

I'm running the below code to call p_CreateRequest. All i want is to get the ID Back but i get an Field "ID" Does not have a default value error. HELP

 using (var conn = Conn)
            {
                using (var cmd = new MySql.Data.MySqlClient.MySqlCommand("p_CreateRequest", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("CREATED_DATE", cREATED_DATE));
                    cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("WORKFLOW_ID", wORKFLOW_ID));
                    cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("CREATED_BY_USER_ID", cREATED_BY_USER_ID));
                    cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("ID", iD));
                    cmd.Parameters["ID"].Direction = System.Data.ParameterDirection.Output;
                    cmd.ExecuteNonQuery();

                    iD = (int)cmd.Parameters["ID"].Value;
                    return iD;
                }
            }

the sprocs code is

CREATE DEFINER="finnsch"@"%" PROCEDURE "p_CreateRequest"(CREATED_DATE datetime,
WORKFLOW_ID int, CREATED_BY_USER_ID varchar(50),INOUT ID int)
BEGIN
  DECLARE LASTCHANGEDATE Date;
  SET LASTCHANGEDATE = Now();
  INSERT INTO REQUEST 
  (CREATED_DATE,WORKFLOW_ID,CREATED_BY_USER_ID,
CURRENT_STEP_ID,LAST_ACTION_BY,LAST_EDIT_DATE)
 select 
 CREATED_DATE,WORKFLOW_ID,CREATED_BY_USER_ID,s.ID,
CREATED_BY_USER_ID,LASTCHANGEDATE
 from 
 STEP s where s.WORKFLOW_ID=WORKFLOW_ID and s.IS_INITIAL=1;
 set ID = Last_Insert_ID();
END

score 0 · Answer 1 · answered Jan 24 '19 at 00:58

Need to change the table and set ID to:

id int(20) NOT NULL AUTO_INCREMENT,

Also, check if MySql is in Strict SQL Mode. If it is, then need to run the query:

SET GLOBAL sql_mode=''.

You can also modify your my.cnf / my.ini to ensure you aren't setting STRICT_ALL_TABLES and/or STRICT_TRANS_TABLES. See this for more information:

Field 'id' doesn't have a default value?

how to get an auto incrementing stored procedure in mysql

1 Answers1