Databound Datagridview not refreshing identity field when adding a new row

Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class Form1
	Inherits System.Windows.Forms.Form
	Private dataGridView1 As New DataGridView()
	Private bindingSource1 As New BindingSource()
	Private dataAdapter As New SqlDataAdapter()
Private Sub Form1_Load(sender As Object, e As System.EventArgs)
		' Bind the DataGridView to the BindingSource
		' and load the data from the database.
		dataGridView1.DataSource = bindingSource1
		GetData("select * from YourTable")
End Sub
Private Sub submitButton_Click(sender As Object, e As System.EventArgs)
		' Update the database with the user's changes.
		dataAdapter.Update(DirectCast(bindingSource1.DataSource, DataTable)
End Sub

Private Sub GetData(selectCommand As String)
		Try
			' Specify a connection string. Replace the given value with a 
			' valid connection string for a Northwind SQL Server sample
			' database accessible to your system.
			Dim connectionString As [String] = "Integrated Security=SSPI;Persist Security Info=False;" & "Initial Catalog=.......;Data Source=localhost"

			' Create a new data adapter based on the specified query.
			dataAdapter = New SqlDataAdapter(selectCommand, connectionString)

			' Create a command builder to generate SQL update, insert, and
			' delete commands based on selectCommand. These are used to
			' update the database.
			Dim commandBuilder As New SqlCommandBuilder(dataAdapter)

			' Populate a new data table and bind it to the BindingSource.
			Dim table As New DataTable()
			table.Locale = System.Globalization.CultureInfo.InvariantCulture
			dataAdapter.Fill(table)
			bindingSource1.DataSource = table

			' Resize the DataGridView columns to fit the newly loaded content.
			dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
		Catch generatedExceptionName As SqlException
			MessageBox.Show("...........")
		End Try
	End Sub

Select allOpen in new window

Private Sub GetMasterHotelData()
        Try
            LoadDBData()

            BindingSource1 = New BindingSource(dsHotel, "Hotel_Data")
            If dsHotel IsNot Nothing Then
                BindingSource1 = New BindingSource(dsHotel, "Hotel_Data")

                BindingSource1.Filter = sBindingSQL
            End If
            DataGridView1.DataSource = BindingSource1
            DataGridView1.Refresh()

            If Me.MID >= 0 Then
                Dim itemFound As Integer = BindingSource1.Find("MID", Me.MID)
                BindingSource1.Position = itemFound
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message, "GetMasterHotelData", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub


Public Sub LoadDBData()
        Try
            Dim cmd As SqlCommand
            Dim MyValue As DBNull = Nothing
            cn = New SqlConnection(CONNECT_STRING)
            'select command
            cmd = New SqlCommand
            cmd.Connection = cn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "sp_HotelRFP_GetMasterHotelDataAll"

            'DataApapter
            daHotel = New SqlDataAdapter
            dsHotel = New DataSet
            daHotel.SelectCommand = cmd
            daHotel.Fill(dsHotel, "Hotel_Data")
            daHotel.ContinueUpdateOnError = True

        Catch ex As Exception
            MessageBox.Show(ex.Message, "LoadDBData", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
        End Try

    End Sub

Select allOpen in new window

Private Sub DataGridView1_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowValidated
        Dim lResponse As Long
        If UpdatePending Then
            lResponse = MessageBox.Show("Do you wish to save changes?", "Data has Changed", MessageBoxButtons.YesNo)
            If lResponse = vbYes Then
                Try

                    Dim command_builder As SqlCommandBuilder
                    command_builder = New SqlCommandBuilder(daHotel)
                    Me.daHotel.Update(dsHotel.Tables(0))
                    
                    Me.UpdatePending = False
                    GetMasterHotelData()     '<--------------
                    '  End If
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
                ' RefreshDataGrid()
            Else
                dsHotel.RejectChanges()
                DataGridView1.Refresh()
                Me.UpdatePending = False
            End If
        Else
            Me.UpdatePending = False
        End If
    End Sub

Select allOpen in new window

Private Function Save() As Boolean
        Dim Saved As Boolean = False
        If MyDataSet.HasChanges Then
            Try
                Dim MylUpdates() As DataRow = MyDataSet.My.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
                Me.MyTableAdapter.Update(MylUpdates)

                Saved = True


            Catch ex As SqlException
                If ex.Number = 2601 Or ex.Number = 2627 Then
                    MessageBox.Show("Dujplicates", "Duplicate Data", MessageBoxButtons.OK, MessageBoxIcon.Question)

                Else
                    MsgBox(ex.Message)
                End If
            End Try
        End If
        Return Saved
        MsgBox("No changes")
    End Function


'Update Button
            Me.Validate()
            Me.PMYBindingSource.EndEdit()
            If Me.Save() Then
            messageBox.Show("Update success")
          ' here you call again the table adapter as the form load to refresh the data 
            end if
            

Select allOpen in new window

 Private Sub DataGridView1_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowValidated
        If bRefreshing Then Exit Sub '<------------
        Dim lResponse As Long
        If UpdatePending Then
            lResponse = MessageBox.Show("Do you wish to save changes?", "Data has Changed", MessageBoxButtons.YesNo)
            If lResponse = vbYes Then
                Try
                    Me.BindingSource1.EndEdit() '<------------NEW
                    Dim command_builder As SqlCommandBuilder
                    command_builder = New SqlCommandBuilder(daHotel)
                    If bAdding Then  '<------------NEW
                        Me.daHotel.SelectCommand = command_builder.GetInsertCommand()
                        Me.daHotel.Update(dsHotel.Tables(0))
                        bAdding = False
                        Me.UpdatePending = False
                        RefreshDataGrid() <----New
                        Exit Sub
                    Else
                        Me.daHotel.SelectCommand = command_builder.GetUpdateCommand
                        Me.daHotel.Update(dsHotel.Tables(0))

                    End If
                    dsHotel.Tables(0).AcceptChanges() <------NEW 
                    ' DataGridView1.Refresh()
                    ' Me.daHotel.Update(dsHotel.Tables(0))
                    ' bAdding = False
                    Me.UpdatePending = False
                    '  End If
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
                ' RefreshDataGrid()
            Else
                dsHotel.RejectChanges()
                DataGridView1.Refresh()
                Me.UpdatePending = False
            End If
        Else
            Me.UpdatePending = False
        End If
    End Sub

rivate Sub RefreshDataGrid()
        bRefreshing = True

        LoadDBData()
        daHotel.Fill(dsHotel, "Hotel_Data") '\\Nyc-hqfps-01\home\rshah\Visual Studio 2005\Projects\MasterHotels\MasterHotels\SplashScreen1.vb
        If dsHotel IsNot Nothing Then
            BindingSource1 = New BindingSource(dsHotel, "Hotel_Data")
        End If
        DataGridView1.Refresh()
        bRefreshing = False
    End Sub

Select allOpen in new window

                   Try
                   
                    Dim command_builder As SqlCommandBuilder
                    command_builder = New SqlCommandBuilder(daHotel)
                    If bAdding Then  '<------------NEW
                       Me.Validate
                        Me.BindingSource1.EndEdit() '<------------NEW
                        Me.daHotel.SelectCommand = command_builder.GetInsertCommand()
                        Me.daHotel.Update(dsHotel.Tables(0))
                        bAdding = False
                        Me.UpdatePending = False
                        RefreshDataGrid() <----New
                        Exit 
'or 

Try
                   
                    Dim command_builder As SqlCommandBuilder
                    command_builder = New SqlCommandBuilder(daHotel)
                    If bAdding Then  '<------------NEW
                       Me.Validate
                        Me.BindingSource1.EndEdit() '<------------NEW
                        Me.daHotel.SelectCommand = command_builder.GetInsertCommand()
                        Me.daHotel.Update(dsHotel.Tables(0))
                        Me.daHotel.AcceptChanges()
                        bAdding = False
                        Me.UpdatePending = False
                        RefreshDataGrid() <----New
                        Exit 

Select allOpen in new window

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class Form1
    Inherits System.Windows.Forms.Form

    Private dataGridView1 As New DataGridView()
    Private bindingSource1 As New BindingSource()
    Private dataAdapter As New SqlDataAdapter()
    Private WithEvents reloadButton As New Button()
    Private WithEvents submitButton As New Button()

    <STAThreadAttribute()> _
    Public Shared Sub Main()
        Application.Run(New Form1())
    End Sub

    ' Initialize the form.
    Public Sub New()

        Me.dataGridView1.Dock = DockStyle.Fill

        Me.reloadButton.Text = "reload"
        Me.submitButton.Text = "submit"

        Dim panel As New FlowLayoutPanel()
        panel.Dock = DockStyle.Top
        panel.AutoSize = True
        panel.Controls.AddRange(New Control() {Me.reloadButton, Me.submitButton})

        Me.Controls.AddRange(New Control() {Me.dataGridView1, panel})
        Me.Text = "myDataGridView"

    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles Me.Load

        ' Bind the DataGridView to the BindingSource
        ' and load the data from the database.
        Me.dataGridView1.DataSource = Me.bindingSource1
        GetData("SELECT * from Hotel_Master where DeletedFlag is null or deletedFlag = 'N' Order by [Hotel Name]")

    End Sub

    Private Sub reloadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles reloadButton.Click

        ' Reload the data from the database.
        GetData(Me.dataAdapter.SelectCommand.CommandText)

    End Sub

    Private Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles submitButton.Click

        ' Update the database with the user's changes.
        Me.dataAdapter.Update(CType(Me.bindingSource1.DataSource, DataTable))

    End Sub

    Private Sub GetData(ByVal selectCommand As String)

        Try
            ' Specify a connection string. Replace the given value with a 
            ' valid connection string for a Northwind SQL Server sample
            ' database accessible to your system.
            

            ' Create a new data adapter based on the specified query.
            Me.dataAdapter = New SqlDataAdapter(selectCommand, My.Settings.mycONNECTIONsTRING)

            ' Create a command builder to generate SQL update, insert, and
            ' delete commands based on selectCommand. These are used to
            ' update the database.
            Dim commandBuilder As New SqlCommandBuilder(Me.dataAdapter)

            ' Populate a new data table and bind it to the BindingSource.
            Dim table As New DataTable()
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            Me.bindingSource1.DataSource = table

            ' Resize the DataGridView columns to fit the newly loaded content.
            Me.dataGridView1.AutoResizeColumns( _
                DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
        Catch ex As SqlException
            MessageBox.Show("...........")
        End Try

    End Sub
   
End Class

Select allOpen in new window

 Private Sub DataGridView1_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowValidated
        If bRefreshing Then Exit Sub
        Dim lResponse As Long
        If UpdatePending Then
            lResponse = MessageBox.Show("Do you wish to save changes?", "Data has Changed", MessageBoxButtons.YesNo)
            If lResponse = vbYes Then
                Try
                    If bAdding Then
                        BindingSource1.EndEdit()
                        DoAddRecord()
                        Exit Sub
                    End If
                    BindingSource1.EndEdit()
                    Dim command_builder As SqlCommandBuilder
                    command_builder = New SqlCommandBuilder(daHotel)
                    Me.daHotel.Update(dsHotel.Tables(0))
                    bAdding = False
                    Me.UpdatePending = False
                    BindingSource1.ResetBindings(False)
                    '  GetMasterHotelData()
                    '  End If
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
                ' RefreshDataGrid()
            Else
                dsHotel.RejectChanges()
                DataGridView1.Refresh()
                Me.UpdatePending = False
            End If
        Else
            Me.UpdatePending = False
        End If
    End Sub


Private Sub DoAddRecord()
        AddNewHotel()
        BindingSource1.EndEdit()
        Dim command_builder As SqlCommandBuilder
        command_builder = New SqlCommandBuilder(daHotel)
        Me.daHotel.Update(dsHotel.Tables(0))
        bAdding = False
        Me.UpdatePending = False
        BindingSource1.ResetBindings(False)
    End Sub

 Private Sub AddNewHotel()
        Dim cmd As New SqlCommand

        Try
            Dim nmid As Integer
            With cmd
                .Connection = cn
                .CommandType = CommandType.StoredProcedure
                .CommandText = "HotelRFP_Insert_MasterHotel"
                .Parameters.Add(New SqlClient.SqlParameter("@MID", SqlDbType.Int, 4, ParameterDirection.Output, True, 30, 0, "", DataRowVersion.Current, DataGridView1.Rows(mnRowNum).Cells("MID").Value)) 'txtMasterID.Text))
                .Parameters.Add(New SqlClient.SqlParameter("@HotelName", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 30, 0, "", DataRowVersion.Current, DataGridView1.Rows(mnRowNum).Cells("Hotel Name").ToString)) 'txtMasterID.Text))
                .Parameters.Add(New SqlClient.SqlParameter("@Address", SqlDbType.VarChar, 150, ParameterDirection.Input, True, 30, 0, "", DataRowVersion.Current, DataGridView1.Rows(mnRowNum).Cells("Address").Value)) 'txtMasterID.Text))
                .Parameters.Add(New SqlClient.SqlParameter("@City", SqlDbType.VarChar, 50, ParameterDirection.Input, True, 30, 0, "", DataRowVersion.Current, DataGridView1.Rows(mnRowNum).Cells("City").Value)) 'txtMasterID.Text))
                .Parameters.Add(New SqlClient.SqlParameter("@State", SqlDbType.VarChar, 50, ParameterDirection.Input, True, 30, 0, "", DataRowVersion.Current, DataGridView1.Rows(mnRowNum).Cells("State").Value)) 'txtMasterID.Text))
                .Parameters.Add(New SqlClient.SqlParameter("@Zip", SqlDbType.VarChar, 15, ParameterDirection.Input, True, 30, 0, "", DataRowVersion.Current, DataGridView1.Rows(mnRowNum).Cells("Zip").Value)) 'txtMasterID.Text))
                .Parameters.Add(New SqlClient.SqlParameter("@Country", SqlDbType.VarChar, 50, ParameterDirection.Input, True, 30, 0, "", DataRowVersion.Current, DataGridView1.Rows(mnRowNum).Cells("Country").Value)) 'txtMasterID.Text))
                .Parameters.Add(New SqlClient.SqlParameter("@HotelMainPhone", SqlDbType.VarChar, 20, ParameterDirection.Input, True, 30, 0, "", DataRowVersion.Current, DataGridView1.Rows(mnRowNum).Cells("Sabre Property Code").Value)) 'txtMasterID.Text))
                .Parameters.Add(New SqlClient.SqlParameter("@SabrePropertyCode", SqlDbType.VarChar, 50, ParameterDirection.Input, True, 30, 0, "", DataRowVersion.Current, DataGridView1.Rows(mnRowNum).Cells("Sabre Property Code").Value)) 'txtMasterID.Text))
            End With

            Using cn As New SqlClient.SqlConnection
                cmd.Connection.Open()
                nmid = cmd.ExecuteScalar()
                DataGridView1.Rows(mnRowNum).Cells("MID").Value = nmid
                cmd.Connection.Close()
                bAdding = False
                Me.UpdatePending = False

            End Using
            '  End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "AddNewHotel", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
        End Try
    End Sub

And my Stored procedure looks like

ALTER PROCEDURE [dbo].[HotelRFP_Insert_MasterHotel]
	@MID int output,
	@HotelName nvarchar(255),
	@Address  nvarchar(150),
	@City nvarchar(50),
	@State nvarchar(50),
	@Zip nvarchar(15),
	@Country  nvarchar(50),
	@HotelMainPhone nvarchar(20),
	@SabrePropertyCode nvarchar(50)
	
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

	BEGIN TRY
		INSERT INTO Hotel_Master
			([Hotel Name],
			Address, 
			City ,
			State ,
			Zip,
			Country ,
			[Hotel Main Phone],
			[Sabre Property Code])
			--PHP,
			--PHID)
		Values 
			(@HotelName,
			@Address,
			@City,
			@State,
			@Zip,
			@Country,
			@HotelMainPhone,
			@SabrePropertyCode)
			--@PHP,
			--@PHID)
		--SET @MID = @@IDENTITY
		select @MID = SCOPE_IDENTITY()
		COMMIT
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED
		select @MID
END TRY

	BEGIN CATCH
		SELECT ERROR_NUMBER() as ERROR_NUMBER,
			ERROR_SEVERITY() as ERROR_SEVERITY,
			ERROR_STATE() as ERROR_STATE,
			ERROR_MESSAGE() as ERROR_MESSAGE
	END CATCH
END

Select allOpen in new window