• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

VB.Net dadapater update

I'm trying to save changes to the database only if there are no exceptions.  Instead what is happening is that I'm getting a PK violation but the first record is being written to the database and the error is displayed.  What I'd like is that once I get an exception is to not write anyting to the database until the error is corrected.

This is my try try catch at the end of my save button.  


 Try
                dAdapter.Update(changes)

            Catch ex As SqlException
                If (ex.Number = 2627) Then
                    errormsgLabel.Visible = True
                    errormsgLabel.Text = "Duplicate Entry"
                    changes.RejectChanges()
              Else
                    errormsgLabel.Visible = True
                    errormsgLabel.Text = ex.Number & " " & ex.Message
                    changes.RejectChanges()
                End If
End Try
0
dev202
Asked:
dev202
1 Solution
 
OCDanCommented:
This is down to how the SQL commands are executed. It attempts to write a row at a time when using adapterupdate.

My preferred way to do this is to create a StoredProcedure in SQL that accepts the required data and in the StoredProcedure use Begin Tran and Rollback tran

If there are no errors then commit:
IF @@ERROR != 0
      BEGIN
      ROLLBACK TRAN
        RETURN
       END
   ELSE
COMMIT TRAN
GO

This link might be helpful: Pass Datarow to SQL

There are quite a few different ways around this you can make a long SQL command that has an update for each of the rows and then use a similar methodology to above.

Hope that helps
0
 
dev202Author Commented:
Thank you very much. It was also helpful that you mentioned the data is processed row by row.  Now I know exactly what I need to do.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now