Mysql output return error[Answered] RSS

• • Reply
  FookWah

  Member

  8 Points

  24 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

  48530 Points

  18075 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

  45489 Points

  7008 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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.