代码之家 › 专栏 › 技术社区 › Geovani Martinez

OracleCommand更新不提交更改

oraclecommand c#

1

Geovani Martinez · 技术社区 · 14 年前

我正在使用以下代码更新Oracle中的表。它会执行,但更新不会提交。如果我使用OracleSqlDeveloper运行查询,它可以正常工作。我错过了什么?select语句按预期工作。

` dim billofladingnumber as string=txtbillofladingnumber.text.trim

    Dim TrailerNumber As String = txtTrailerNumber.Text.Trim
    Dim CarrierCode As String = txtCarrierCode.Text.Trim
   Dim TransportationMethod As String = txtTransportationMethod.Text.Trim 
   Dim OracleCommand As New OracleCommand()
    With OracleCommand
        .Connection = OracleConnection
        .CommandType = CommandType.Text
        .CommandText = "UPDATE XXF_ASN_HEADERS SET BILL_OF_LADING_NUMBER ='" + BillOfLadingNumber + "',TRAILER_NUMBER ='" + TrailerNumber + "',CARRIER_CODE ='" + CarrierCode + "',TRANSPORTATION_METHOD ='" + TransportationMethod + "' WHERE HEADERID ='" + Request.QueryString("HeaderId") + "'"
    End With

    OracleConnection.Open()
    Dim result As Integer = OracleCommand.ExecuteNonQuery()
    OracleConnection.Close()

    If result = 1 Then Response.Redirect("default.aspx")`

2 回复 | 直到 14 年前

1

2

Geovani Martinez 14 年前

谢谢大家指点我的方向。这是最终的工作代码。诀窍是使用oracletransaction,下面的代码非常有用。接下来使用参数。可以找到更多信息 here

            Using dbConnection As New OracleConnection(OracleConnectionString)

                'Open the connection
                dbConnection.Open()

                Dim dbCommand As OracleCommand = dbConnection.CreateCommand()
                Dim dbTransaction As OracleTransaction

                'Start a local transaction 
                dbTransaction = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted)
                'Assign transaction object for a pending local transaction
                dbCommand.Transaction = dbTransaction

                Try
                    dbCommand.CommandType = CommandType.Text
                    dbCommand.CommandText = "UPDATE XXF_ASN_HEADERS SET BILL_OF_LADING_NUMBER ='" + BillOfLadingNumber + "', TRAILER_NUMBER ='" + TrailerNumber + "', CARRIER_CODE ='" + CarrierCode + "', TRANSPORTATION_METHOD ='" + TransportationMethod + "' WHERE HEADERID ='" + Request.QueryString("HeaderId") + "'"
                    dbCommand.ExecuteScalar()
                    dbTransaction.Commit()
                    Response.Redirect("default.aspx")
                Catch ex As OracleException
                    'Rollback the transaction
                    dbTransaction.Rollback()
                    'display error details
                    lblUpdateQuery.Text = dbCommand.CommandText
                    lblDebug.Text = ex.Message.ToString
                End Try

            End Using

2

0

John Fisher 14 年前

我正在处理的项目也使用oraclecommand。最大的区别是,我们所有的数据库调用都指向存储过程,而不是直接更改数据库的动态SQL。最可能的情况是,您需要将update语句包装在一个“begin”和“end”块中,并可能添加一个“commit;”语句。

不过要当心。如果将回车和换行符放入commandtext中,那么至少一个用于Oracle的.NET库版本将失败。用分号分隔东西就行了。