以下sql语句正在创建一个错误,消息为:“消息:在命令执行期间遇到致命错误。”“内部异常:必须定义参数'@lastuserid'。”
如果我直接使用last\u insert\u id()而不是lastuserid,它在这样执行时总是返回零(因此在第二次插入时失败)。
我看不出我的语法和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;
2条答案
按热度按时间dnph8jn41#
创建一个过程,首先执行插入,缓存最后一个插入的id,执行另一个插入,然后让它用bool打印出参数(如果最后一个插入成功与否)。这样你就可以正确地调试它了。
一般来说,您应该避免使用concating字符串来生成sql命令,否则您可能会遇到包含意外字符的参数的问题,或者被注入击中。
czfnxgou2#
简单的修复方法:将“$lastuserid”替换为“$'lastuserid'”。头颅造口术有区别。