尝试获取插入的 ID 时强制转换无效

Question

我试图获取最近插入的表 ID，但我不断收到转换错误。该项目总是包含 NULL，我不知道为什么。我按照此处的说明进行操作： http://www.mikesdotnetting .com/Article/54/Getting-the-identity-of-the-most-recently-added-record

这是我的 SqlDataSource：

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
    ConnectionString="<%$ ConnectionStrings:NH_SWAGConnectionString %>"             
    InsertCommand="INSERT INTO [tblOrders] ([OrderDate], [OrderTotal], [OrderAccount], [OrderCostCentre]) VALUES (@OrderDate, @OrderTotal, @OrderAccount, @OrderCostCentre); SET @OrderNewID = SCOPE_IDENTITY()" 
    SelectCommand="SELECT * FROM [tblOrders]" 
    UpdateCommand="UPDATE [tblOrders] SET [OrderDate] = @OrderDate, [OrderTotal] = @OrderTotal, [OrderAccount] = @OrderAccount, [OrderCostCentre] = @OrderCostCentre WHERE [OrderID] = @original_OrderID AND [OrderDate] = @original_OrderDate AND [OrderTotal] = @original_OrderTotal AND [OrderAccount] = @original_OrderAccount AND [OrderCostCentre] = @original_OrderCostCentre"            
    ConflictDetection="CompareAllValues" 
    OldValuesParameterFormatString="original_{0}"
    OnInserting="SqlDataSource2_Inserting"
    OnInserted="SqlDataSource2_Inserted">
    <InsertParameters>
        <asp:Parameter Direction="Output" Name="OrderNewId" Type="Int32" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="OrderDate" Type="DateTime" />
        <asp:Parameter Name="OrderTotal" Type="Decimal" />
        <asp:Parameter Name="OrderAccount" Type="String" />
        <asp:Parameter Name="OrderCostCentre" Type="String" />
        <asp:Parameter Name="original_OrderID" Type="Int32" />
        <asp:Parameter Name="original_OrderDate" Type="DateTime" />
        <asp:Parameter Name="original_OrderTotal" Type="Decimal" />
        <asp:Parameter Name="original_OrderAccount" Type="String" />
        <asp:Parameter Name="original_OrderCostCentre" Type="String" />
    </UpdateParameters>
</asp:SqlDataSource>

这是插入新记录并调用 SCOPE_IDENTITY( ) 获取新 ID：

protected void btnOrder_Click(object sender, EventArgs e)
{
    //Add entry to Order Table    

    SqlDataSource2.InsertParameters.Add("OrderDate", DateTime.Now.ToString("MMMM dd, yyyy"));
    SqlDataSource2.InsertParameters.Add("OrderTotal", "0");
    SqlDataSource2.InsertParameters.Add("OrderAccount", ItmUser);
    SqlDataSource2.InsertParameters.Add("OrderCostCentre", ItmCostCode.Text);

    SqlDataSource2.Insert();
}

protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
    OrderNewID = (int)e.Command.Parameters["@OrderNewId"].Value;
}

为什么上面的 @OrderNewId 为 NULL？

Answer 1

好吧，我明白了。我没有使用 SET，而是在 InsertQuery 中使用了 SELECT：

INSERT INTO [tblOrders] ([OrderDate], [OrderTotal], [OrderAccount], [OrderCostCentre]) VALUES (@OrderDate, @OrderTotal, @OrderAccount, @OrderCostCentre); SELECT @OrderNewID = SCOPE_IDENTITY()