Update DataGridView after inserting a new row - new id of the row missing

[AndyLD]

Hello!

I have a DataGridView (myDataGridView.DataSource = myDataTableBindingSource) bound to a DataTable which is filled by an OleDbDataAdapter (myDataAdapter).
After the user has entered the data to a new row the row is saved to the database:

myDataAdapter.Update(myDataTable)

Now I need to update the DataGridView, because the id is assigned by the database.
In order to update the datagridview, I call this method

 myDataGridView.ResetBindings()

But the id does not get updated, it stays empty. At the same time the record has been added to the database and the id has been assigned correctly.
How can I make the dataGridView to really reread all records from the database?

[jmcilhinney]

As you're using an OleDbDataAdapter, I'm going to guess that you're using an Access database. If so, check this out:

http://www.vbforums.com/showthread.p...e-After-Insert

That solution was created in VS 2010 so you'll need VS 2010 or later installed to open and run it. If you don't already have them, VB Express 2010 and/or VS 2012 Express for Windows can both be installed without affecting you existing VS 2008 installation.

If you're not using Access then please post back with the name of the database you are using.

[AndyLD]

Dear Jmcilhinney,

thank you very much, I will have a look into your suggested solution. But my first idea is, that there must be a way to really reload the datagridview, in fact, I could dispose the datagridview and recall the method where I initialize my datagridview, but this would redo all the formatting etc., that seem to be unnecessary at this moment.

[jmcilhinney]

Dear Jmcilhinney,

thank you very much, I will have a look into your suggested solution. But my first idea is, that there must be a way to really reload the datagridview, in fact, I could dispose the datagridview and recall the method where I initialize my datagridview, but this would redo all the formatting etc., that seem to be unnecessary at this moment.

Your first idea is very, very wrong. You don't need to do anything to the grid at all. All you need to do is get the auto-generated values from the database and update your DataTable with those values. No other data needs to go anywhere. If you have 1000 records in your grid and you insert one new record, do you really want to retrieve that same 1000 records from the database again when the only thing that's changed is one number? Of course not. That would be silly. You simply get that one number from the database and update the DataTable. The grid is bound to that DataTable so it will inherently update as well, which is the whole point of data-binding.

[AndyLD]

Dear Jmcilhinney,
of course, what I want to do includes some waste of resources. But, in my case every datatable of my datagridview is physically limited to 8 rows, so the requery for the data is limited in terms of wasted resources.
I had a look at your suggested solution, but what I don't like, is the fact that I have to get the new ID of the new record and set the id for the record in my datatable bound to the grid to the fetched value.

So for now I will use my "bad" idea, I just needed not to resetbindings of the dgv, but I needed to refill the datatable bound to the dgv.

[jmcilhinney]

What you do is up to you but, if you worked for me and you knowing chose a dodgy solution over doing things the proper way, especially when doing things the proper way takes very little effort, you'd be getting a talking to. Anyone with the attitude that it works so it's OK would be changing that attitude quickly or not working for me for long.

[AndyLD]

Oh, that hurts... but the simple truth is, I am fighting to get this to work, and there are still plenty of other problems to be solved.
At the same time, I don't agree that my solution with reloading the datatable with all the new records is too wrong. I would consider wrong to set the id in the DGV to a value "calculated or estimated", but realoading shows always the correct data, just it is not the most efficient solution.

[jmcilhinney]

At the same time, I don't agree that my solution with reloading the datatable with all the new records is too wrong.

It is. The end result is correct so, if that's all you care about then fine. The way you're doing it is wrong though. It's wrong like using string concatenation to insert values into SQL code is wrong.

[AndyLD]

I just found this same topic in a tutorial here: http://support.microsoft.com/kb/815629
It is the same way described in Jmcilhinneys post, just for me this post was somehow easier to understand.

Edit: Oh, I only now noticed this :

This feature works only with Microsoft Jet OLEDB 4.0 databases

, while I have switched to ACE, but maybe this note is just for older JET engines.

[dunfiddlin]

The article was written for VS 2002. I think you can safely assume that Ace which didn't become the standard until Office 2007 was not being precluded!

[kareninstructor]

Hello,

Here is a extremely simply example that adds a new row to a MS-Access database table followed by adding the row to the DataGridView with the new identifier. Now if this works for you please take time to study the code rather than simply using the code so you learn from what is done.

VS2008 Project on SkyDrive

Complete code listing for project above

Public Class frmMainForm
    Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=demo.accdb;"
    Private NewIdentifer As Integer = 0
    Private InsertStatement As String = "INSERT INTO People (LastName) Values(@LastName)"
    Private IdentifierStatement As String = "Select @@Identity"

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

        If Not String.IsNullOrEmpty(txtLastName.Text) Then

            Using cn As New OleDbConnection(ConnectionString)
                Using cmd As New OleDbCommand(InsertStatement, cn)
                    cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = IdentifierStatement

                    NewIdentifer = CInt(cmd.ExecuteScalar())

                    Dim Row As DataRowView = CType(DataGridView1.DataSource, DataView).AddNew
                    Row("Identifier") = NewIdentifer
                    Row("LastName") = txtLastName.Text
                    Row.EndEdit()

                    DataGridView1.CurrentCell = DataGridView1(0, DataGridView1.RowCount - 1)
                    txtLastName.Text = ""
                End Using
            End Using
        Else
            MsgBox("Please enter a name")
        End If
    End Sub
    Private Sub frmMainForm_Load( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load

        DataGridView1.AutoGenerateColumns = False
        Using cn As New OleDbConnection(ConnectionString)
            Using cmd As New OleDbCommand("SELECT * FROM People", cn)
                Dim dt As New DataTable
                cn.Open()
                Dim Reader As OleDbDataReader = cmd.ExecuteReader()

                dt.Load(Reader)
                Dim dv = dt.DefaultView
                DataGridView1.DataSource = dv

                If DataGridView1.RowCount > 0 Then
                    DataGridView1.CurrentCell = DataGridView1(0, DataGridView1.RowCount - 1)
                End If

            End Using
        End Using
    End Sub
    Private Sub txtLastName_KeyDown( _
        ByVal sender As Object, _
        ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtLastName.KeyDown

        If e.KeyCode = Keys.Enter Then
            e.SuppressKeyPress = True
            cmdAddNewRow.PerformClick()
        End If

    End Sub
End Class

[AndyLD]

Thanks for this, but my problem was to get the new record id's while using commandbuilder commands and using dataadapter.Update method to update database and datasource for the DGV, and there one has to add events and handlers to the dataadapter update command like in the examples above.

[kareninstructor]

I believe jmcilhinney has an example under the code bank forum (not sure what version of VS it is but worth looking), the event of the adapter is rowupdated.

[AndyLD]

Yes, Jmcilhinney has an example and the link to it is in the second post in this thread, and I posted another link to MSDN tutorial on this ;-)