Why is setting SQL variable is creating an error?

Question

Following SQL statement is creating an error with message : "Message: Fatal error encountered during command execution." "Inner exception: Parameter '@LastUserID' must be defined."

If I directly use LAST_INSERT_ID() instead of LastUserID, it always returns zero (hence fails at second insert) when executed like this.

I don't see my syntax is different than in mySQL document.

Could some one help me ?

string Query = @"INSERT INTO login (" +
                                    "LOGIN_EMAIL," +
                                    "LOGIN_PASSWORD," +
                                    "LOGIN_SALT," +
                                    "LOGIN_LAST_LOGIN_DATE," +
                                    //         "LOGIN_LAST_LOGIN_LOCATION," +
                                    "LOGIN_ACCOUNT_STATUS," +
                                    "LOGIN_LOGIN_ATTEMPTS," +
                                    "LOGIN_CREATED_DATE) " +
                                "VALUES (" +
                                    "@Parameter2," +
                                    "@Parameter3," +
                                    "@Parameter4," +
                                    "@Parameter5," +
                                    //                                        "@Parameter6," +
                                    "@Parameter6," +
                                    "@Parameter7," +
                                    "@Parameter8); " +
                                "SET @LastUserID = LAST_INSERT_ID(); " + 
                                "INSERT INTO user_role (" +
                                    "USER_ROLE_USER_ID," +
                                    "USER_ROLE_ROLE," +
                                    "USER_ROLE_STATUS," +
                                    "USER_ROLE_CREATED_DATE) " +
                                "SELECT " +
                                    "@LastUserID," +
                                    "@Parameter9," +
                                    "@Parameter10," +
                                    "@Parameter11 " +
                                "FROM dual WHERE NOT EXISTS (SELECT USER_ROLE_USER_ID FROM user_role " +
                                "WHERE USER_ROLE_USER_ID = @LastUserID AND USER_ROLE_ROLE = @Parameter9)";

                    MySqlCommand oCommand = new MySqlCommand(Query, oMySQLConnecion);

                oCommand.Transaction = tr;

"If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined." source: https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id Are you sure your first instance of the query is executing correctly and actually inserting data, so that `last_insert_id()` actually has a value? — Martin, Aug 23 '18 at 06:49
Yes, first part is executing correctly. Both inserts are working correctly when executed separately by reading the Userid from first and sending to second insert. I am trying to combine them with lastinsertid(). Error message says @LastUserID not defined. Why is that ? — PCG, Aug 23 '18 at 07:13
Try subquery it to retrieve the value instead, i.e. `SET @LastUserID = (SELECT last_insert_id() FROM table_name);` This should just retrieve the value of the last inserted ID of the table. — Martin, Aug 23 '18 at 07:18
Nop, it's not working either. I just checked "SET @LastUserID = (SELECT last_insert_id() FROM user_role);" commenting out all inserts. Also checked with an integer instead of function. Still the same exact error: "Inner exception: Parameter '@LastUserID' must be defined." — PCG, Aug 23 '18 at 14:02
Then I have no idea how it really ignores the last_insert_id(), because it 100% should work if the previous insert infact did a flawless insert. One concern could be that the database connection goes lost or something, and therefore the last_insert_id() function can't retrieve the id, but that would be weird. — Martin, Aug 24 '18 at 08:16
Finally, I was able to find the fix. Its related to how C# handles the variable "$LastUserID". Fix is to replace all occurrences of "$LastUserID" with "$'LastUserID'" ($'LastUserID'). Everything works fine then. By the way, I also removed "NOT EXISTS" with unique Index to avoid duplicates. — PCG, Aug 25 '18 at 15:13

PCG · Accepted Answer · 2018-08-26T19:04:10.377

0

Simple fix : Replace "$LastUserID" with "$'LastUserID'". The ephostophy makes the difference.

edited Aug 26 '18 at 19:04

answered Aug 23 '18 at 07:10

PCG

2,049
5
24
42

score 0 · Answer 2 · answered Aug 23 '18 at 14:48

0

Create a procedure in which you first do your insert, cache the last inserted id, do the other insert and let it print out your parameters with a bool if your last insert worked or not. That way you can debug it properly.

In general you should avoid concatinating strings to generate sql-commands or you might get troubles with parameters containing unexpected characters or be hit by a injection.

answered Aug 23 '18 at 14:48

DataVader

740
1
5
19

What's the best way to handle long SQL queries ? – PCG Aug 25 '18 at 07:45
Depends on what you plan to do with it. Sometimes I use procedures, sometimes I create views. When having the option, you could use a backend-framework to work with abstraction layers. – DataVader Aug 27 '18 at 16:19

Why is setting SQL variable is creating an error?

2 Answers2