0

Trying to get the Autoincrement ID of a row after TableAdapter.Update. The data is Binded so there's no row being added to the datatable as in this case.

Any ideas on how to solve this?

Thank you

Community
  • 1
  • 1
Gil
  • 33
  • 1
  • 11

2 Answers2

1

Why not use this after TableAdapter.Update:

CType(xBindingSource.Current, DataRowView).Item("ID")
George Brighton
  • 5,131
  • 9
  • 27
  • 36
Nocturnal
  • 386
  • 2
  • 8
0

You need to retrieve the newly created id by including a select statement in your insert query; you need to change the executemode to Scalar, too. This blog post has more detail

peterG
  • 1,651
  • 3
  • 14
  • 23
  • Thanks Peter, but how will I use it when the data that's about to be inserted is binded? I have a form that has all its fields binded to a data table. – Gil Feb 12 '13 at 08:58
  • When your grid is bound via a bindingsource, the data will be pushed back into the dataset or (BindingList of T etc ) by the bs, typically by calling EndEdit on the bs. But you still have to call update on your tableadapter to get the data back into the database itself. In other words, it's a two-step process, and I suspect you're getting those two stages blurred together. – peterG Feb 12 '13 at 10:50
  • I am using EndEdit, and then TableAdapter.Update(DataSet.Table). The solution you posted is useful when you're using TA.Insert, and when you use Insert you have to provide a row, which I don't have since it's binded. – Gil Feb 12 '13 at 13:25
  • sorry Gil on reflection that perhaps wasn't the piece of the jigsaw you are missing. You are right i nthat that is useful when using dbDirect. IN your scenario, what should happen is that, in the designer, your dataset should have 'Refresh the Datatable' checked in 'Advanced options'. Then when you create a new row in the grid then call update on your ta, the new row will be inserted and the datatable will be re-queried. This will pull the new row, complete with identity, back into the grid. – peterG Feb 12 '13 at 17:03
  • That's correct, however I'm not using a grid... how can I retrieve it manually? – Gil Feb 12 '13 at 17:25
  • OK I'm a bit confused by 'no new row' vs 'Autoincrement ID' - if it's a new identity, it must be a new row; conversely, if it's not a new row then you already have the id. In your initial question you said it was bound, I must have assumed to a grid; but anyway, when you have the 'refresh the datatable' checked as per my prev comment, then the identity will be pulled back into the datatable. You can read it from there manually. – peterG Feb 12 '13 at 17:36
  • Ok, I'll try to explain myself a little better: I have a form containing several textboxes, each bound to a specific column in a datatable. After the user fills each of these boxes, they press 'save' and then I use EndEdit & TA.Update. I'd like to retrieve the Autoincrement ID of this new row. – Gil Feb 13 '13 at 08:52
  • Right - well you should have created a new row first - that's what you're binding your textboxes to! A typical sequence would be: create a new strongly-typed row from the datatable; set the datasource of the bs to the new row; user enters data and clicks 'Save'; call bs.endedit to push the data from textboxes into the row; add the row to the datatable; call update on the ta. – peterG Feb 13 '13 at 12:36