none
Update command is not updating the tables RRS feed

  • 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

    Saturday, September 23, 2017 3:28 PM

Answers

  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by alobi Monday, September 25, 2017 12:36 PM
    Saturday, September 23, 2017 3:38 PM
    Moderator
  • Have you tried to execute ‘transaction.Commit()’ after the second ExecuteNonQuery?

    Saturday, September 23, 2017 4:33 PM
  • 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 MSDNFSF@microsoft.com.


    Monday, September 25, 2017 8:02 AM
    Moderator

All replies

  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by alobi Monday, September 25, 2017 12:36 PM
    Saturday, September 23, 2017 3:38 PM
    Moderator
  • Have you tried to execute ‘transaction.Commit()’ after the second ExecuteNonQuery?

    Saturday, September 23, 2017 4:33 PM
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, September 23, 2017 5:58 PM
    Moderator
  • 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 MSDNFSF@microsoft.com.


    Monday, September 25, 2017 8:02 AM
    Moderator
  • Works like Magic!  thanks for your Input
    Monday, September 25, 2017 12:37 PM
  • Awesome. Great
    Monday, September 25, 2017 12:41 PM