以下代码在MySql SQL编辑器中工作:
SELECT currentqty INTO @cqty from items where itemid = 'FELABELS';
set @aqty = @cqty + 10;
UPDATE ITEMS
SET CURRENTQTY = @aqty
WHERE ITEMID = 'FELABELS';
但是在 VB.NET 应用程序中,我将上述代码放在.sql文件中并将其传递给命令执行,我收到错误"命令执行期间遇到致命错误"。
SQL 文件内容:
SELECT currentqty INTO @cqty from items where itemid = @ItemId;
set @aqty = @cqty + @Qty;
UPDATE ITEMS
SET CURRENTQTY = @aqty
WHERE ITEMID = @ItemId;
执行它的类,_sqlText
将是.sql文件的内容:
Using mdscmd = New MySqlCommand(_sqlText, MyConnection) With {
.CommandType = CommandType.Text}
mdscmd.Parameters.AddWithValue("@Qty", Quantity)
mdscmd.Parameters.AddWithValue("@ItemId", ItemId)
Try
mdscmd.ExecuteNonQuery()
Catch ex As Exception
End Using
Return False
End Try
End Using
有人看到问题吗?
SELECT currentqty INTO @cqty from items where itemid = 'FELABELS'; set @aqty = @cqty + 10; UPDATE ITEMS SET CURRENTQTY = @aqty WHERE ITEMID = 'FELABELS';
仅当itemid
是唯一时,此代码才是确定性的。
假设这是真的,我建议将这 3 个查询合并为单个
UPDATE items
SET currentqty = currentqty + 10
WHERE itemid = 'FELABELS';