下面的SQL语句创建了一个错误,并显示以下消息:" message :在命令执行期间遇到致命错误。““内部异常:必须定义参数'@LastUserID‘。”
如果我直接使用LAST_INSERT_ID()而不是LastUserID,那么当像这样执行时,它总是返回0(因此在第二次插入时失败)。
我没有看到我的语法与mySQL文档中的语法有什么不同。
有人能帮帮我吗?
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;
发布于 2018-08-23 15:10:24
简单的解决方法:将"$LastUserID“替换为"$'LastUserID'”。ephostophy造成了不同。
发布于 2018-08-23 22:48:56
创建一个过程,在这个过程中,您首先执行插入操作,缓存最后插入的id,然后执行其他插入操作,并让它使用bool输出参数,无论最后一次插入操作是否有效。这样你就可以正确地调试它了。
通常,您应该避免通过连接字符串来生成sql命令,否则您可能会遇到包含意外字符的参数问题或被injection击中。
https://stackoverflow.com/questions/51979748
复制相似问题