Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Databound Datagridview not refreshing identity field when adding a new row

Posted on 2010-09-10
43
Medium Priority
?
1,656 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

972 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