How to make use of the 'Refresh the data table' option in TableAdapter Configuration Wizard?

Question

I have a project that uses a plain vanilla typed dataset with wizard generated TableAdapter SQL Statement commands, but for some reason cannot get the auto-incrementing ID's in the database to refresh in my dataset after I add a new row. I have everthing checked in the Advanced Options dialog, and I have validated that the adapter is executing INSERT / SELECT pairs to add and then retrieve the new row.

Based on the Wizard, it would appear that no other setup is necessary. How do I get the adapter to refresh the data?

TIA,
Matthew

Answer 1

Hello Matthew,

 

Welcome to ADO.NET DataSet forum!

 

Could you please provide us with the command string of the InsertCommand?   Does the SELECT clause contains SCOPE_IDENTITY()?  

 

Do you check SQL Server Profiler to verify both the insert and select clauses are executed?   What’s the value of the auto-incrementing column after the insert operation?  

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum                                                                                         

If you have any feedback on our support, please contact [e-mail]

 

---

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! If you have any feedback, please tell us.

Answer 2

Lingzhi,

I found the problem. I have a trigger on some of my tables that executes a stored procedure for logging purposes. One of the statements was returning a result set, causing the following sequence:

1. Insert command executed

2. Trigger result set returned

3. Select command executed

Since the SELECT command wasn't the first result set, the 'Refresh the data table' feature didn't work. After fixing the Trigger so it would not return a result set, the refresh began working.

Sincerely,

Matthew