FirstReturnedRecord kicked my butt

After who knows how many hours of 'why is this not working', I have finally figured this out.  I hope my pain will lesson yours. 

We were using Output params when we needed the sproc (insert or update) to return data to us.  Due to some new requirements, we now need to fetch the entire record that was either inserted or updated.

So, I started on this rewrite journey last week.  I picked two tables to test with, both doing update and inserts.  I tweaked the sprocs to do a Select at the end and no longer return output params.  I updated our DAL setting the Command's UpdateRowSource to FirstReturnedRecord.

I tested the insert and update sprocs in Query Analyzer and all looked good.  The record was selected after the insert|update and I could see it in QA.

The problem, I never saw the record back in the .net client.  I tweaked, retweaked, tested, debugged, pulled hair, yelled, kicked and then screamed.  The scream finally made me realize the problem.  

The beginning of all of our insert and update sprocs do 1+ Selects preparing for the insert|update.  The problem with this?  The select returns a result set.  So, the FirstRetrunedrecord is the Select I am not interested in. I commented out all of these Selects, and viola, the .net client now has the record selected at the end of the insert|update.