Mysql output return error [Answered]RSS

• • Reply
  FookWah

  Member

  6 Points

  19 Posts

  Mysql output return error

  Oct 01, 2018 04:08 PM|FookWah|LINK

  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;
                  }

• • Reply
  PatriceSc

  All-Star

  42430 Points

  14547 Posts

  Re: Mysql output return error

  Oct 01, 2018 04:30 PM|PatriceSc|LINK

  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...
• • Reply
  Zhi Lv - MSF...

  All-Star

  45301 Points

  6972 Posts

  Microsoft

  Re: Mysql output return error

  Oct 02, 2018 05:39 AM|Zhi Lv - MSFT|LINK

  Hi FookWah,

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.