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;
}
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...
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.
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
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 :
All-Star
45489 Points
7008 Posts
Microsoft
Re: Mysql output return error
Oct 02, 2018 05:39 AM|Zhi Lv - MSFT|LINK
Hi FookWah,
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:
Best regards,
Dillion