Solved

Databound Datagridview not refreshing identity field when adding a new row

Posted on 2010-09-10
43
1,557 Views
Last Modified: 2012-05-10
IHi experts, Hope some one will come to my rescue..
I am working on desktop app in vb.net with SQL server as backend. I have adtagridview bound to bindingsource.
In the grid, AllowUserToAddRow is set to false. I have a button to add new row. In that buttonclick I have the following code:
       BindingSource1.MoveLast()
        BindingSource1.AddNew()
User then enters the data in that newly added row and when moves to next row,  In binding source event I have the following code(see code window). In rowvalidating even i update the dataset via dataadapter.update method. It add the row in the table, but immediately, it dow not show the identity field. How can I refresh so that datagridview will show the newly added records's identity field ( I call it MID:) ?
 I have been working on this for last two day with no luck. I will appreciate  your help. Thanks. This is the o nly issue that is keeping me from releasing this application to my users. It is kind of urgent.

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

                    '  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 BindingSource1_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles BindingSource1.ListChanged

        If dsHotel.HasChanges Then

            Me.UpdatePending = True

        End If

    End Sub

Open in new window

0
Comment
Question by:RekhaShah
  • 23
  • 17
  • 2
  • +1
43 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33652086
After updatind database, please put "AcceptChanges"

-------------
Me.daHotel.Update(dsHotel.Tables(0))
Me.daHotel.AcceptChanges



0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33652090
ohh daHotes is not your dataset, accept change will work with dataset

put dsHotel.AcceptChanges
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33652257
Hi ...
If you are using a BindingSource everytime the dataset has chnages you have to EndEdit the Bingisource...So...
Me.BindingSource1.EndEdit
Me.daHotel.Update(dsHotel.Tables(0))
Me.daHotel.AcceptChanges
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33652328
Can you please tell us how do you populate the datagridview?
if you are using an sql command,Because you are using sqlcommandbuilder  you may call directly the updatecommand GetUpdateCommand ...
if you are using dataset (draged into your form ) there is a simple way to save changes...
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33652363
try also this
Me.daHotel.Update(dsHotel.Tables(0))=command_builder.GetUpdateCommand()
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33652727
This is a complete code according to your approach..
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

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33652733
this was for a northwind database......
Also in your case you do not have to declare the datagridview if you already have it in your Form
0
 

Author Comment

by:RekhaShah
ID: 33653171
It saves in the sql table. But it does not refreshe the gridview.  When I close the app and reopen the app, i see my added record.
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653176
after that you have to call again the table adapter...so the new data will be displayed in the datagridview.....
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653183
What i mean is to  repeat the manner you are filling the datagridview after your changes have made....
0
 

Author Comment

by:RekhaShah
ID: 33653212
Here is my code to populate the datagridview and fill the dataadapter. I am not sure if my sequence of saving data is right. DELETE works fine. EDIT works fine. ADD works correctly as far as the adding a record to the table is concerned. It just does not refresh my datagridview ( One of the column is Identity field - that does not get refreshed).

I am not sure on what even I am suppose to save data. I am doing it in RowValidated event.
SO MY PROBLEM IS REFRESHING THE GRID.

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

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653246
Could you please try ....
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

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653249
And a question....Why don't you have a button click for saving data?
0
 

Author Comment

by:RekhaShah
ID: 33653263
The user wants to be able to modify the data in the grid. But wants buttons to add and delete records.
I can have a button to save and in save_click i can call a stored procedure just like i do for "DELETE" and reload the grid . But the user  wants to be able to copy and paste data from the existing rows in to this newly added row. so the question is which even would I prompt the user to save the data?
0
 

Author Comment

by:RekhaShah
ID: 33653276
I have a flag bAdding. So I know user is in ADD mode. Can I some how prevent the user from switching rows unless he/she clicks save or cancel button?
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653280
did you try the code?
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653285
for your last comment ....
you can use cell validating event oreven row validating if the add new procedure does not finish...
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653294
with a button even if user moves between the rows there is no problem ...
when you press save then you can check everything you want...let the user do what he want ...but prevent him if something is wrong or missing !!!!
0
 

Author Comment

by:RekhaShah
ID: 33653333
yes, I tried calling GetMasterHotelData  after update, it give me "Invalid Operation in this EventHandler".  i guess i can not refresh the grid in rowvalidating even- it might be going into infinite loop. right now i am stuck, I  might have to reboot.. :(

I just wanted to make  clear, that it is not 'Adding' but 'Refreshing' is the problem

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653365
Yes I understand...
usually after update..if want t show the new data in the datagridview we call the tableadapter fill method...
so
messageBox.show("update success")
me.tableAdapter.Fill(me.Dataset.MYdatatable)
0
 

Author Comment

by:RekhaShah
ID: 33653459

I think, the problem is I am trying to mix up manual addition of row and automatic editing of row. May be two modes can not be mixed.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653470
There is another approach.......
1.Create a dataset with your desired table(s) (from Datasources tab)
2.Drag the datasource in your form ...this will createa datagridview in your Form ...
3.If you want to put a WHERE clause in your datagridview ,Open the dataset Designer ,RightClick on the table adapter -->add-->query ...follow the wizrard..
4.In your form load you will find a line with the tableadapter.Fill method..
0
 

Author Comment

by:RekhaShah
ID: 33653515
Does this require a rewriting of the app?

This is a simple app with one form and one table.
But  a lot is going on behind the scene. for example,
1. We need buttons to Add and Delete
2. User can  modify any cell in the grid, But upon leaving the row, must be prompted to save if the data is dirty.
3. User can Ctrl-c Ctrl V from the cell of row above like excel spreadsheet.
4. Certain cells must be formatted in certain manner.
5. user can filter on any one or many of the fields dynamically e.g. on hotel name, address, phone, state, zip etc. The filtering can be selected with "Contains" or "starts with.
I mean I have worked all this out.

So if mixing of the two modes(manual for add,delete and Autoamtic for editing is not possible, I can ask my manager to change the specs. I really don't want to modify the specs.
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653523
5.If you want to add a new row you can simply call in a button click Me.BindingSource.AddNew
6.use this function to update
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

            

Open in new window

0
 

Author Comment

by:RekhaShah
ID: 33653531
I am working on your suggestion...Created a new dataset and usign a new testform
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653532
Ok the let me check again your code ...and i 'll post a piece of code on how to show the new data after update!!
0
 

Author Comment

by:RekhaShah
ID: 33653716
I think i know what is going on, since, AlloUserToAddRow =false, may be my command_builder does not create command for adding a new record, it just updates it????
How do I check what command is being executed when I am executing  

Me.daHotel.Update(dsHotel.Tables(0))  ?
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653749
this is to modify data in DataSet
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653811
modify means ...deletion or changes or new data
0
 

Author Comment

by:RekhaShah
ID: 33653828
For me, Modify=make changes in existing data, Add - Add a new record, Delete, delete an existing record.I tried the following code in my RowValidated even, but no success!

 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

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33653842
please try also
                   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 

Open in new window

0
 

Author Comment

by:RekhaShah
ID: 33653934
Nop, it is stuck now.  
I think, there is a lot of code behind lot of events and that is messing things up .I will try to comment out everything until this works and slowly add things back. THIS has to work!!!!
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33654017
Can you give me your Select COmmand?
0
 

Author Comment

by:RekhaShah
ID: 33655535
My select statement is a stored procedure:
 SELECT * from Hotel_Master where DeletedFlag is null or deletedFlag = 'N' Order by [Hotel Name]


0
 

Author Comment

by:RekhaShah
ID: 33655538
If I add the record via stored procedure instead of dataadapter.update method, it shows the id field in the grid, but then it disappears.
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33656004
This Code Is Working Properly For Data deltion,Update,Insert..there is A button called Reload.When click it new data is displaying..Just take care the connection string
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

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33656045
In your case use just the Reload Button...with the GetData.
Do your update as you already did..
and then just add this

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
0
 

Author Comment

by:RekhaShah
ID: 33656662
I would like to thank itoutou for his constant efforts to help me.

I got this working...Finally!
This is by trial and Error only - don't quite understand.  Attaching the modified code.
I did the following:
Used stored procedure to get the identity field MID
Again used the Dataadapter.Update method
and Finally used
      BindingSource1.ResetBindings(False)



 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

Open in new window

0
 

Author Comment

by:RekhaShah
ID: 33656670
I would like to close this issue, Question is, can i partially award the points to itoutou since he did try very hard and guided me in the correct direction ?

 The problem was a different issue which i don't know, but I got it working by trial and error.
0
 
LVL 5

Expert Comment

by:David Christal CISSP
ID: 33656677
Award the points!  The coverage was well worth it.  This is a vaulable thread.
0
 
LVL 18

Accepted Solution

by:
John (Yiannis) Toutountzoglou earned 250 total points
ID: 33656734
@RekhaShah there is no need to close the question since all my examples working....is another approach you are following and i am still there if you want further comment...
0
 

Author Closing Comment

by:RekhaShah
ID: 33659251
Because Ihad lot of crap going behind the scene in datagridview events, for some reason, itoutou's solutiond did not work completely, but it helped me to reach the trial anderror stage. Just for any one else's reference, I had to modify the code in the following manner:

Used stored procedure to get the identity field MID
Again used the Dataadapter.Update method
and Finally used
      BindingSource1.ResetBindings(False)
0
 

Author Comment

by:RekhaShah
ID: 33659297
Sorry, I guess, I closed too prematurely. I checked my table and table had two records for each record I added. One record has real data and the other record has hotel name = DataGridViewTextBoxCell { ColumnIndex=1, RowIndex=15698 } , but it is not part of the datagridview. I don't see it in the datagridview after I add a record. I just happened to open the table to delete the test records and i noticed..... Any Idea?,
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

16 Experts available now in Live!

Get 1:1 Help Now