Update command is not updating the tables

Question

I am using the following code to try to update a couple of  tables but it does not update and does not show any error message. I will appreciate all help. Thanks

Al

Try
                ' Start a local transaction with ReadCommitted isolation level.
                transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
                ' Assign transaction object for a pending local transaction.
                cmd.Connection = conn
                cmd.Transaction = transaction
                ' Execute the commands.

                cmd.CommandText = "Update Student set Programtbl= ?,Leveltbl= ? Where Student_ID=?"
                
                cmd.Parameters.AddWithValue("@P1", cboProgram.Text)
                cmd.Parameters.AddWithValue("@P2", cboLevel.Text)     
                cmd.Parameters.AddWithValue("@P3", txtEnterStuID.Text)
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()

                cmd.CommandText = "Update StudentNOK Set SurName=?,FirstName=? where Student_ID =?"
                cmd.Parameters.AddWithValue("@P1", txtSurnameNK.Text)
                cmd.Parameters.AddWithValue("@P2", txtFirstNameNK.Text)
                cmd.Parameters.AddWithValue("@P14", txtEnterStuID.Text)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                conn.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

            'Inform User that record has been edited
            MessageBox.Show("Updeted")
        End Using

Answer 1

Hello,

Try checking the value returned by ExceuteNonQuery as shown below either by setting a break-point on cmd.Dispose and seeing what result is or using a MessageBox.

Try
    ' Start a local transaction with ReadCommitted isolation level.
    transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
    ' Assign transaction object for a pending local transaction.
    cmd.Connection = conn
    cmd.Transaction = transaction
    ' Execute the commands.

    cmd.CommandText = "Update Student set Programtbl= ?,Leveltbl= ? Where Student_ID=?"

    cmd.Parameters.AddWithValue("@P1", cboProgram.Text)
    cmd.Parameters.AddWithValue("@P2", cboLevel.Text)
    cmd.Parameters.AddWithValue("@P3", txtEnterStuID.Text)
    cmd.ExecuteNonQuery()
    cmd.Parameters.Clear()

    cmd.CommandText = "Update StudentNOK Set SurName=?,FirstName=? where Student_ID =?"
    cmd.Parameters.AddWithValue("@P1", txtSurnameNK.Text)
    cmd.Parameters.AddWithValue("@P2", txtFirstNameNK.Text)
    cmd.Parameters.AddWithValue("@P14", txtEnterStuID.Text)

    '
    ' ExecuteNonQuery returns the number of rows affected. 
    ' So if you expected one row affected then result would equal 1 etc.
    '
    Dim result As Integer = cmd.ExecuteNonQuery()

    cmd.Dispose()
    conn.Close()
Catch ex As Exception
    MsgBox(ex.Message)
End Try

'Inform User that record has been edited
MessageBox.Show("Updeted")

If result is 0 then no rows where affected if more than 0 and you don't see changes check to make sure that the database is included (seen) in Solution Explorer and if so change it's property "Copy to output directory" to Copy if newer. Otherwise more specifics are needed e.g. where is the database located if the above does not work or match your project. 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 2

Have you tried to execute ‘transaction.Commit()’ after the second ExecuteNonQuery?

Answer 3

Have you tried to execute ‘transaction.Commit()’ after the second ExecuteNonQuery?

Jeez, I missed that :-)

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 4

Hi alobi,

Yes, Viorel's post can solved your issue.

By the way, we use transaction like the following code:

 Using connection As New SqlConnection(connectionString)
            connection.Open()

            Dim command As SqlCommand = connection.CreateCommand()
            Dim transaction As SqlTransaction

            ' Start a local transaction
            transaction = connection.BeginTransaction("SampleTransaction")

            ' Must assign both transaction object and connection
            ' to Command object for a pending local transaction.
            command.Connection = connection
            command.Transaction = transaction

            Try
                command.CommandText =
              "Update here"
                command.ExecuteNonQuery()
                command.CommandText =
              "Update here"

                command.ExecuteNonQuery()

                ' Attempt to commit the transaction.
                transaction.Commit()
                Console.WriteLine("Both records are written to database.")

            Catch ex As Exception
                Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
                Console.WriteLine("  Message: {0}", ex.Message)

                ' Attempt to roll back the transaction.
                Try
                    transaction.Rollback()

                Catch ex2 As Exception
                    ' This catch block will handle any errors that may have occurred
                    ' on the server that would cause the rollback to fail, such as
                    ' a closed connection.
                    Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
                    Console.WriteLine("  Message: {0}", ex2.Message)
                End Try
            End Try
        End Using

Best Regards,

Cherry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Works like Magic!  thanks for your Input

Answer 6

Awesome. Great