SqlCommand ExecuteNonQuery() not committing changes

I have a function in which i pass through some SQL and attempt to execute it against a database.

The function completes fine and a SQL trace shows me that the query is being executed against the database, but when i refresh my select statement, the data has not updated. In this instance, the query is a simple:

update *table* set *columnA* = *columnA* + 1

No errors are generated in SQL or Visual Studio, but as mentioned, the update is not committed and the data remains the same.

This is my function code below:

Public Function RunSQL(TaskCommand As String) As String
    Try
      'Opens connection to DB
      OpenDatabase()

      'Start an SQL Transaction to ensure data integrity
      Dim trn As SqlTransaction = mCn.BeginTransaction

      Using trn

        If trn Is Nothing _
          OrElse trn.Connection.State <> ConnectionState.Open Then
          Throw New ApplicationException("SaveProjectInfo: SQL Transaction object is either Nothing or has no connection")
        End If

        Try
          Using mCn
            Dim command As New SqlCommand()
            'command = mCn.CreateCommand()
            command.CommandType = CommandType.Text
            command.CommandText = TaskCommand
            command.Transaction = trn
            command.ExecuteNonQuery()
          End Using
        Catch ex As Exception
          Config.LogError(ex)
          trn.Rollback()
        End Try

        'trn.Commit()

      End Using
    Catch ex As Exception
      Config.LogError(ex)
      'TODO – Bubble this back to the UI
    Finally
      CloseDatabase()
    End Try


    Return 0
  End Function

I have tried forcing a commit (currently commented out above) but this results in an “‘This SqlTransaction has completed; it is no longer usable.'” error message caught by the exception catcher. I have spent hours scouring google for answers but i am at my wits end now.

Can anyone please help and explain why my ExecuteNonQuery is executing the SQL query but not committing the change??

I am using Visual Studio Community 2017 and SQL Server 2019.

Edit: mCn comes from another function, OpenDatabase(), shown below:

  Private Sub OpenDatabase()
    mCn = New SqlConnection(Config.SQLConnectionString)
    If mCn.State <> ConnectionState.Open Then
      mCn.Open()
    End If
  End Sub

mCn is what i am using as the database connection – aka “My Connection”

Answer

Seems like you had not commited your transaction anywhere,Try commiting after ExecuteNonQuery

Using mCn
        Dim command As New SqlCommand()
        'command = mCn.CreateCommand()
        command.CommandType = CommandType.Text
        command.CommandText = TaskCommand
        command.Transaction = trn
        command.ExecuteNonQuery()
        trn.Commit()
      End Using

Advertisement

Answer