asp-classic - 不使用 CreateParam 返回 SCOPE_IDENTITY()

Question

我正在尝试此操作，但出现错误ADODB.Recordset错误'800a0e78'关闭对象时不允许操作。在带有此代码的行If ScopeID.EOF Then

请不要回答使用CreateParam方法，寻找没有此方法的解决方案。谢谢。

<%  
    set Cmd = Server.CreateObject("ADODB.Command")
    Cmd.ActiveConnection = conn

    Cmd.CommandText = "INSERT INTO TABLE (NAME) VALUES ('test')  SELECT SCOPE_IDENTITY() AS ID"
    Cmd.CommandType = 1
    Cmd.CommandTimeout = 0
    Cmd.Prepared = true

    Set ScopeID = Cmd.Execute()

    If ScopeID.EOF Then
        Response.Write "There was an Error in your request, Please try again"
        Response.End
    Else
        ID= ScopeID(0).Value
    End IF

    ScopeID.Close
    Set ScopeID = Nothing
    Set Cmd = Nothing

    Response.Write ID

%>

Answer 1

试试这个:

Cmd.CommandText = "SET NOCOUNT ON; INSERT INTO TABLE (NAME) VALUES ('test'); SET NOCOUNT OFF; SELECT SCOPE_IDENTITY() AS ID"

据我了解，如果 SQL Server 没有通过 SET NOCOUNT ON “隐藏”，则 SQL Server 会为实际插入语句返回一个(空)记录集。我认为错误消息是指记录集被关闭，而不是连接。