在Oracle INSERT中返回的值未返回正确的值 [英] Returning in Oracle INSERT is not returning proper value

问题描述

我有一个使用Oracle数据库的ASP.NET Web应用程序(VB.NET).在插入时，我需要重新获得插入行的标识.我正在尝试使用RETURNING，但我一直得到的返回值为1.

I have an ASP.NET web application (VB.NET) using an Oracle database. On an insert, I need to get the identity of the inserted row back. I am trying to use RETURNING, but I keep getting a value of 1 returned.

Dim strInsert As String = "INSERT INTO L.TRANSACTIONS (LOCATION_KEY, TRANS_CREATOR, TRANS_EMAIL, TRANS_PHONE) VALUES (:location_key, :trans_creator, :trans_email, :trans_phone) RETURNING TRANS_ID INTO :ukey"

Try
    If oConn.State <> ConnectionState.Open Then
        oConn.Open()
    End If

    Dim oCmnd As New OracleCommand(strInsert, oConn)
    oCmnd.Parameters.Add("location_key", Session.Item("location").ToString.Trim())
    oCmnd.Parameters.Add("trans_creator", Session.Item("userID").ToString.Trim())
    oCmnd.Parameters.Add("trans_email", Session.Item("mail").ToString.Trim())
    oCmnd.Parameters.Add("trans_phone", Session.Item("phone").ToString.Trim())
    oCmnd.Parameters.Add("ukey", Oracle.DataAccess.Client.OracleDbType.Varchar2, System.Data.ParameterDirection.ReturnValue)

    Dim strUkey As String = oCmnd.ExecuteNonQuery()

运行应用程序时，将插入记录，并且TRANS_ID正在递增，但返回值始终为"1".

When I run the application, the record gets inserted and the TRANS_ID is incrementing but the returned value is always "1".

推荐答案

您正在将ExecuteNonQuery的结果分配给变量，而不是获取分配给已创建参数的值.我相信您想将最后一行更改为以下内容(未经测试):

You're assigning the result of ExecuteNonQuery to the variable, rather than getting the value assigned to the parameter you've created. I believe you want to change the last line to something like this (untested):

oCmnd.ExecuteNonQuery
Dim strUkey As String = oCmnd.Parameters.GetParameter("ukey").Value

这篇关于在Oracle INSERT中返回的值未返回正确的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！