VB.Net dadapater update

Posted on 2012-09-07
Last Modified: 2012-09-07
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.  


            Catch ex As SqlException
                If (ex.Number = 2627) Then
                    errormsgLabel.Visible = True
                    errormsgLabel.Text = "Duplicate Entry"
                    errormsgLabel.Visible = True
                    errormsgLabel.Text = ex.Number & " " & ex.Message
                End If
End Try
Question by:dev202
    LVL 9

    Accepted Solution

    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

    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

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now