--- title: "Ack! My DataTable magically grew a new data column. Here's why." url: "https://osherove.com/blog/2004/4/21/ack-my-datatable-magically-grew-a-new-data-column-heres-why.md" canonical: "https://osherove.com/blog/2004/4/21/ack-my-datatable-magically-grew-a-new-data-column-heres-why.html" published: "2004-04-21T10:34:00" updated: "2004-04-21T10:34:00" tags: [".Net Original"] author: "Roy Osherove" description: "Ugh. That was almost fun :) I was showing some of the guys how to do unit testing against a database, and we got stuck on a test that does an insert using a dataset. Imagine this: you use a simple data adapter against…" usage: "Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page)." license: "https://creativecommons.org/licenses/by/4.0/" --- # Ack! My DataTable magically grew a new data column. Here's why. By Roy Osherove April 21, 2004 In [.Net Original](https://osherove.com/blog/category/net-original) **Historical article** · Originally published April 21, 2004; technical details may be outdated. Ugh. That was **almost** fun :) I was showing some of the guys how to do unit testing against a database, and we got stuck on a test that does an insert using a dataset. Imagine this: you use a simple data adapter against the “categories“ table in northwind, using a simple insert command with one parameter to be sent to an insertCategory stored procedure in the database. Couldn't be simpler, right? Yet a strange thing was happening: The data table that was being updated (there was only one of those in the DS, for Categories table) starts out before the update with 4 columns (for the 4 cols in the original categories table), but after calling data adapter.update(DS) , the table suddenly contains **5 columns(!).** I kid you not. We started trying to track this down, and after about 30 mins, we found what the problem was: The insert sproc was returning the identity value of the newly insert row like so: INSERT INTO categories.... bla Select scope\_identity() This was a problem for the data adapter which could not map back the returned value into the updated data row in question, so it automatically created a new data column inside the data table. Changing the syntax to this: INSERT INTO categories.... bla Select scope\_identity() **as CategoryID** fixed it up real nice. **beware**! Another interesting anecdote: this is one of those small annoying bugs that creep up on you. Because we were doing this whole exercise in a test-driven manner, we were able to discover it almost instantly (well, not the source, but the fact that there **was** a problem). Imagine having to wade through massive amounts of code in a production application, hunting this bug. ugh! How did we discover that the column counts were different? We were using a small testing utility class that was provided by the generous Mr. [Darrell Norton](http://dotnetjunkies.com/WebLog/darrell.norton), who's had lots of experience with TDD in the DB world. This small utility class compares two datasets in many ways, including all current values and row counts. Thanks [Darrell](http://dotnetjunkies.com/WebLog/darrell.norton)! Maybe you can post it? ## More in .Net Original [All posts in .Net Original](https://osherove.com/blog/category/net-original) - Apr 22, 2004 ### [VB.Net is diverging from C#. That can't be bad, or can it?](https://osherove.com/blog/2004/4/22/vbnet-is-diverging-from-c-that-cant-be-bad-or-can-it.html) - Apr 21, 2004 ### [\[Coding Contest\] Most Useful/Innovative VS.Net Add-in/Macro (and some huge prizes)](https://osherove.com/blog/2004/4/21/coding-contest-most-usefulinnovative-vsnet-add-inmacro-and-s.html) - Apr 20, 2004 ### [The Regulator v. 2.01 - now with Intellisense](https://osherove.com/blog/2004/4/20/the-regulator-v-201-now-with-intellisense.html) - Apr 15, 2004 ### [Misunderstanding timestamp](https://osherove.com/blog/2004/4/15/misunderstanding-timestamp.html) --- ## About the author **Roy Osherove**: Senior Solutions Architect at AWS working on AI for production and AI-driven development; author of The Art of Unit Testing and Elastic Leadership, with about 20 years of consulting and training on unit testing, TDD, team leadership and continuous delivery. ### Sites - [RobotPaper](https://robotpaper.ai/): This blog: AI patterns and anti-patterns, AI and coding agents, and engineering leadership. - [osherove.com](https://osherove.com): Roy's personal site. Author of The Art of Unit Testing and Elastic Leadership; consulting and training on unit testing, TDD, team leadership and continuous delivery, plus about 20 years of blog posts. - [The Art of Unit Testing](https://www.artofunittesting.com): The Art of Unit Testing, 3rd Edition (Manning): readable, maintainable, trustworthy unit tests, plus TDD workshops in C#, JavaScript, Java and Kotlin. - [5whys.com](https://www.5whys.com): Roy's writing on software team leadership: self-organizing teams, surviving chaos, learning mode and the Elastic Leadership model. - [Pipeline Driven](https://pipelinedriven.org): Continuous delivery and DevOps practices: build patterns, pipeline-friendly testing, DevOps metrics and enterprise DevOps. - [Elastic Leadership](https://www.elasticleadership.com): Elastic Leadership, Roy's framework for software team leaders (survival, learning and self-organizing modes). ### Social - X: https://x.com/royosherove - LinkedIn: https://www.linkedin.com/in/osherove/ ### Contact - Contact form: https://forms.gle/jQSx5KoXvrVA1JCK7 ### License Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page). License: https://creativecommons.org/licenses/by/4.0/