none
How to get indentity ID for a newly created DataGridView row from SQL Server table?

    Question

  • I have an application in which SQL Server communicates with a DataTable via a SqlDataAdapter. The adapter is loaded with the usual SqlCommands which are updated as needed. The SqlDataAdapter fills a DataTable. The DataTable is bound to the DataGridView. Pretty standard stuff.

    The problem comes from the primary key being generated back in the database. When the user triggers the creation of a new row in the DataGridView, a new row is added to the DataTable. The user data is copied to the SQL Server database which generates a primary key value for the newly added row. This PID does not automatically propagate back to the table so any actions downstream that are dependent on this primary key ID are going to fail.

    This is a problem that a lot of people have had and solved but their solutions do not seem to be working well for me. The solution that works least poorly is to refill the table after adding the new row. Given that the table could conceivably contain thousands of items, this is a sub-optimal solution.

    I'd like to continue to use bound controls but this particular issue is a deal breaker if it cannot be solved in a reasonable fashion. Does anyone have a suggestion?

    Monday, August 30, 2010 8:00 PM

Answers

  • Hi Richard,

     

    We have an official document talking about the requirement you ask. Please read the following document.

    http://msdn.microsoft.com/en-us/library/ks9f57t0%28VS.80%29.aspx

     

    Read “Retrieving SQL Server Identity Column Values” paragraph.

    When working with Microsoft SQL Server, you can create a stored procedure with an output parameter to return the identity value for an inserted row.

     

    The stored procedure can then be specified as the source of the InsertCommand of a SqlDataAdapter object. The CommandType property of the InsertCommand must be set to StoredProcedure. The identity output is retrieved by creating a SqlParameter that has a ParameterDirection of Output. When the InsertCommand is processed, the auto-incremented identity value is returned and placed in the CategoryID column of the current row if you set the UpdatedRowSource property of the insert command to UpdateRowSource.OutputParameters or to UpdateRowSource.Both.

     

    Hope it helps.

     

    Sincerely,

    Kira Qian

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by Richard.Haggard Wednesday, September 01, 2010 2:06 AM
    Tuesday, August 31, 2010 3:29 AM

All replies

  • Hi Richard,

     

    We have an official document talking about the requirement you ask. Please read the following document.

    http://msdn.microsoft.com/en-us/library/ks9f57t0%28VS.80%29.aspx

     

    Read “Retrieving SQL Server Identity Column Values” paragraph.

    When working with Microsoft SQL Server, you can create a stored procedure with an output parameter to return the identity value for an inserted row.

     

    The stored procedure can then be specified as the source of the InsertCommand of a SqlDataAdapter object. The CommandType property of the InsertCommand must be set to StoredProcedure. The identity output is retrieved by creating a SqlParameter that has a ParameterDirection of Output. When the InsertCommand is processed, the auto-incremented identity value is returned and placed in the CategoryID column of the current row if you set the UpdatedRowSource property of the insert command to UpdateRowSource.OutputParameters or to UpdateRowSource.Both.

     

    Hope it helps.

     

    Sincerely,

    Kira Qian

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by Richard.Haggard Wednesday, September 01, 2010 2:06 AM
    Tuesday, August 31, 2010 3:29 AM
  • The supplied link showed me how to connect the Insert command to a stored procedure which returns the newly inserted row's primary key and that seems to be working for me so far. Thank you.

    ----------

    Richard Lewis Haggard

    Wednesday, September 01, 2010 2:08 AM