locked
Mysql output return error RRS feed

  • Question

  • User-27405682 posted

    my program keep displaying the id is null. how to get the last id

    CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insertlogin`(
     IN fristName VARCHAR(300), 
     IN surname VARCHAR(300),
     IN emailAddress VARCHAR(500),
     IN phoneNumber INT(11), 
     IN profileImg VARCHAR(300),
     OUT id INT(11)
    )
    BEGIN
    
         INSERT INTO user_access.user_account  
              ( 
                fristName                   ,
                surname                     ,
                emailAddress                ,
                phoneNumber                 ,
                profileImg                   
              ) 
         VALUES 
              ( 
                fristname                   ,
                surname                     ,
                emailAddress                ,
                phoneNumber                 ,
                profileImg                   
              );
    		SELECT id = LAST_INSERT_ID();
              
    END
     using (MySqlConnection sqlConn = _dbConnection.OpenConection())
                    {
                        //-- prepares command
                        Dictionary<string, string> Parameters = new Dictionary<string, string>();
                        Parameters.Add("@fristname", objsr.fristName);
                        Parameters.Add("@surname", objsr.surName);
                        Parameters.Add("@emailAddress", objsr.email);
                        Parameters.Add("@phoneNumber", objsr.phone);
                        Parameters.Add("@profileImg", objsr.profileImg);
    
                        MySqlParameter output = new MySqlParameter("@id",MySqlDbType.Int32);
                        output.Direction = ParameterDirection.Output;
    
                        MySqlCommand cmd = _dbConnection.PrepareCommand(sqlConn, "sp_insertlogin", CommandType.StoredProcedure, Parameters);
                        
    
                        result = cmd.ExecuteNonQuery();
                        int outval = (int)cmd.Parameters["@id"].Value;
                        long idd = cmd.LastInsertedId;
    
                        return result;
                    }

    Monday, October 1, 2018 4:08 PM

Answers

  • User-271186128 posted

    Hi FookWah,

    my program keep displaying the id is null. how to get the last id

    The LAST_INSERT_ID() function returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table.

    So, please check your table definition and make sure it contains the auto_increment id column. Then refer to the following code to get the last insert id.

    store procedure's body:

    BEGIN
      INSERT INTO table1(column1, column2) VALUES ('value1', 'value2');
      SET out_param = LAST_INSERT_ID();
    END

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 2, 2018 5:39 AM

All replies

  • User753101303 posted

    Hi,

    I'm using rather SQL Server but it should be similar :

    • try rather SET id=LAST_INSERT_ID(); according to  https://dev.mysql.com/doc/refman/8.0/en/set-variable.html
    • currently you perhaps return a resultset (with an old alias syntax) which would explain you don't get anything. It seems SELECT id:=LAST_INSERT_ID() could perhaps work (bnot the additional : character) but it is likely best to always use SET over SELECT when you can for variable assignments...
    Monday, October 1, 2018 4:30 PM
  • User-271186128 posted

    Hi FookWah,

    my program keep displaying the id is null. how to get the last id

    The LAST_INSERT_ID() function returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table.

    So, please check your table definition and make sure it contains the auto_increment id column. Then refer to the following code to get the last insert id.

    store procedure's body:

    BEGIN
      INSERT INTO table1(column1, column2) VALUES ('value1', 'value2');
      SET out_param = LAST_INSERT_ID();
    END

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 2, 2018 5:39 AM