asked on

VB.NET & SQL SERVER 2014

I have a VB.NET application that connects with a SQL Server DB, and inserts a new record of data into a table that is an auto-increment ID column. When the user enters the data, and decides that they do not want to save the information, the application is supposed to rollback the data and not insert it into the DB. However, if the user does not save the data for ID number 150, for example, the code works as it should and removes the information from the DB, however upon the next entry of data into the DB, the ID number is now 151, and not 150 as that should be the next number in the sequence, since it was not utilized.

Can someone tell me code wise, how to ensure that SQL Server does not increment to the next number if the user does not save the record into the DB? Below is the VB code that I am using for this functionality:

Private Sub frmWizard_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        If Not hasProfileBeenSaved Then
            If MsgBox("Save changes to the new profile?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                e.Cancel = True  
            Else
                'delete profile
                DbUtil.Provider.DeleteRecord(profile.ID)
            End If
        End If
    End Sub

Open in new window

Thanks in advance for your feedback.

Regards,
aj85

SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Arana (G.P.)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aj85

ASKER

Hello Eugene,

Thanks for your response. However, I understand your comments, and it may be that I have go modify my code. But just so I am clear on what is happening, let me expand a little further. If I have records 1, 2 and 3 in the DB, and user starts the process of adding record profile number 4, and does not complete the saving process, so record 4 should not be in the DB, the code deletes that records so it is not in the DB. In the auto-incrementing function in SQL Server that last record is number 3, but it moves on to record number 5, and skips number 4, although it was not inserted into the DB.

If I am misunderstanding something about how SQL Server increments, then I apologize in advance, as I may just have to add another set of columns and create the ID's from there.

Regards,
aj85

aj85

ASKER

Hello Arana,

Thanks for your reply. Perhaps I am not understanding your comment correctly, but the ID's are unique and incremental, already.

Regards,
aj85

SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aj85

ASKER

Hello All,

Thank you for your clear and concise feedback. I have fixed the problem as recommended by adding new code and columns that are separate from the auto-increment, which resolved the issue. Again I appreciate your guidance and expertise.

Regards,

aj85

VB.NET &amp; SQL SERVER 2014

VB.NET & SQL SERVER 2014