c# - ASP.NET : SqlDataSource insert - identity parameter

Question

我有一个 asp.net 应用程序，我在其中使用链接到 SqlDataSource 的 GridView 插入到一个 sql 表中。问题是我不知道插入时要使用的“Identity”参数。 “Identity”列应该自动递增，但是在插入时，我得到一个异常，即 Identity 参数不能为 null。我如何制作插入语句以使其有效，或者我如何获得下一个正确的身份#来指定？谢谢。

这是我的 SqlDataSource 代码:

 <asp:SqlDataSource ID="VehiclesSqlDS" runat="server" 
        ConnectionString="<%$ ConnectionStrings:RamRideOpsConnectionString %>" 
        SelectCommand="SELECT * FROM [Vehicles]" 
        ConflictDetection="CompareAllValues" 
        InsertCommand="INSERT INTO [Vehicles] ([Identity], [CarNum], [MaxPassengers], [Status], [CurrPassengers], [StartAdd], [EndAdd], [AvgRideTime], [numRides]) VALUES (@Identity, @CarNum, @MaxPassengers, @Status, @CurrPassengers, @StartAdd, @EndAdd, @AvgRideTime, @numRides)" 
        OnInserting="VehiclesSqlDS_Insert"
        OldValuesParameterFormatString="original_{0}">         
        <InsertParameters>
            <asp:Parameter Name="Identity" Type="Int32 "/>
            <asp:Parameter Name="CarNum" Type="Int32" DefaultValue="-1"/>
            <asp:Parameter Name="MaxPassengers" Type="Int32" DefaultValue="3" />
            <asp:Parameter Name="Status" Type="String" DefaultValue="Ready" />
            <asp:Parameter Name="CurrPassengers" Type="Int32" DefaultValue="0" />
            <asp:Parameter Name="StartAdd" Type="String" />
            <asp:Parameter Name="EndAdd" Type="String" />
            <asp:Parameter DbType="Time" Name="AvgRideTime" />
            <asp:Parameter Name="numRides" Type="Int32" DefaultValue="0" />
        </InsertParameters>        
    </asp:SqlDataSource>

Answer 1

需要将参数direction设置为OUTPut，才能得到identity值。来自 MSDN

并处理 onInserted 事件以获取标识值

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        string sID = e.Command.Parameters["@Identity"].Value.ToString();
       //Display new ID

    }

你可以这样试试。

<asp:sqlDataSource ID="Datasource" 
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

  InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                 SELECT @EmpID = SCOPE_IDENTITY()"
  UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName 
                   WHERE EmployeeID=@EmployeeID"
  DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

  ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
  OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
  RunAt="server">

  <SelectParameters>
    <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
  </SelectParameters>

  <InsertParameters>
    <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
  </InsertParameters>

</asp:sqlDataSource>