带有asp.net和SqlDataSource控件的SqlDataSource输出参数

Question

我正在将我的应用程序从MSSQL切换到MYSQL。当我使用MSSQL时，我通过

Private Sub dsImpoundInformation_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles dsImpoundInformation.Inserted
    _impoundId = e.Command.Parameters("impoundId").Value
End Sub

Private Sub dsImpoundInformation_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles dsImpoundInformation.Inserting
    Dim impoundIdparam As New SqlClient.SqlParameter()
    impoundIdparam.ParameterName = "impoundId"
    impoundIdparam.Direction = System.Data.ParameterDirection.Output
    impoundIdparam.DbType = DbType.Int32
    impoundIdparam.Value = 0
    e.Command.Parameters.Add(impoundIdparam)
End Sub

和

InsertCommand="INSERT INTO LotManager_impounds (accountId, truckId, createdBy, driver, locationId, dateArrived, towedFrom, reasonForImpound, reasonForImpoundOther, impoundCity, impoundCounty, timeOfImpound, dateDeemedAbandoned, ticketNumber) VALUES (@accountId,@truckId,@createdBy,@driver,@locationId,@dateArrived,@towedFrom,@reasonForImpound,@reasonForImpoundOther,@impoundCity,@impoundCounty,@timeOfImpound,@dateDeemedAbandoned,@ticketNumber); SET @impoundId = SCOPE_IDENTITY();"

现在当我尝试

InsertCommand="INSERT INTO LotManager_impounds (accountId, truckId, createdBy, driver, locationId, dateArrived, towedFrom, reasonForImpound, reasonForImpoundOther, impoundCity, impoundCounty, timeOfImpound, dateDeemedAbandoned, ticketNumber) VALUES (@accountId,@truckId,@createdBy,@driver,@locationId,@dateArrived,@towedFrom,@reasonForImpound,@reasonForImpoundOther,@impoundCity,@impoundCounty,@timeOfImpound,@dateDeemedAbandoned,@ticketNumber); SET @impoundId = LAST_INSERT_ID();"

我知道错误：

您的SQL语法出现了错误；请检查与MySQL服务器版本对应的手册，以便在第1行使用接近'0 = LAST_INSERT_ID()‘的正确语法。

当我试着：

InsertCommand="INSERT INTO LotManager_impounds (accountId, truckId, createdBy, driver, locationId, dateArrived, towedFrom, reasonForImpound, reasonForImpoundOther, impoundCity, impoundCounty, timeOfImpound, dateDeemedAbandoned, ticketNumber) VALUES (@accountId,@truckId,@createdBy,@driver,@locationId,@dateArrived,@towedFrom,@reasonForImpound,@reasonForImpoundOther,@impoundCity,@impoundCounty,@timeOfImpound,@dateDeemedAbandoned,@ticketNumber); SET impoundId = LAST_INSERT_ID();"

我知道错误：

未知系统变量“impoundId”

最终，我只是试图获得最后一个自动增量值，但在其他应用程序中，我还计划将代码的其他部分切换到MYSQL，这些部分依赖于输出参数。我还没有探索如何使用存储过程，但此时我想让它以类似于我如何使用MSSQL的方式工作。

提前谢谢。

Answer 1

最后，我崩溃了，决定使用存储过程。这是做任何事情的最好方法，并使代码更加简洁。对于遇到同样问题的人，我的建议是不要浪费时间让它正常工作，只需要使用一个存储过程。