Refreshing the contents of a datagrid when records are inserted

[Nucleus]

I have a dataadapter feeding into a dataset that is bound to a datagrid. When the user adds a record I call the update method of the dataadapter which writes back to the database.

Now here is the problem even though I call the datagrid.refresh method, the id field in the datagrid is not being populated So it looks like the database is being written to, but the dataset doesn't get the ids of the new records back. Is there anyway to force the return of the id fields into the dataset that is better than recreating the dataset from scratch again?

[Pirate]

Refresh method of DataGrid just grahically redraw the Grid . For such problem , either I refill the dataset or just recall the procdure that feeds the grid with data . This will get data from source and back them up in the DataGrid .

[Nucleus]

ok I found a solution but it wasn't straight forward This is for an access db btw >2000.

Anyway what you need to do is declare the dataadapter that fills the datagrid with events like so and create a command object too oh and you need the connection of course.

Dim WithEvents da2 As New System.Data.OleDb.OleDbDataAdapter()
Dim ObjConn As New Data.OleDb.OleDbConnection()
Dim cmdGetIdentity As Data.OleDb.OleDbCommand

ok now in the forms load event set the text of your command object like this:

cmdGetIdentity = New Data.OleDb.OleDbCommand("SELECT @@IDENTITY", ObjConn)

finally add this code to your dataadapters row updated event:

Private Sub da2_RowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles da2.RowUpdated
        If e.Status = UpdateStatus.Continue AndAlso e.StatementType = StatementType.Insert Then
            ' If this is an INSERT operation...
            ' Execute the post-update query to fetch new @@Identity
            ObjConn.Open()
            e.Row("HrsWrkId") = CInt(cmdGetIdentity.ExecuteScalar)' you need to put the name of your id field for the table for it to work for you i.e change HrsWrkId to the relevant name.
            e.Row.AcceptChanges()
            ObjConn.Close()
        End If
    End Sub

and that works for me.

here is the article I worked off, although I had to fiddle to get it working which is why I posted the answer.

http://msdn.microsoft.com/library/de...anidcrisis.asp

[Nucleus]

Originally posted by Pirate
Refresh method of DataGrid just grahically redraw the Grid . For such problem , either I refill the dataset or just recall the procdure that feeds the grid with data . This will get data from source and back them up in the DataGrid .

Yeah i worked that out Pirate, the problem was that I had 2 linked tables being displayed in the grid and if the user didn't get the id back when they created a record in the main table then the user couldn't create records in the sub table, it also creates lots of traffic that can be avoided as well as navigation issues. Luckily it wasn't that hard after all

[Pirate]

Simply put , refilling the dataset and bind it back to the Grid is perfect I think . And for the solution you came up with , we'd discussed it here . You could have a look . http://www.vbforums.com/showthread.p...light=IDENTITY