Question: I added a new table in my MS SQL database, and set up relationships between the new table and an existing table. I went to Visual Studio’s dataset designer (one I had already started) and added the new table, but I’m not seeing the relation line as I would expect. What gives?
Answer: Go to your project’s settings and make sure there isn’t a new connection string made when you tried to add the new table. This happened to me and took me a while to realize VS created a new connection string for me when I tried adding a new tableadapter using Windows Integrated Security (My original connection string has plain username and password)
I had a recent need of populating a bunch of rows in a database table. Instead of doing the usual For Each… Insert routine, which is slow as heck, I tried the SQLBulkCopy.WriteToServer method.
I’ve prepared a datatable as my copy source, but when it tried to execute .WriteToServer method, I came across an exception “column ‘****’ does not allow dbnull.value”
I looked into my datatable and saw nothing wrong! After doing some Googling and Stackoverflowing I found out my source datatable I’ve prepared came from copying a dataset tableadapter’s schema, which had different column order than the actual database table’s, because I’ve made updates to the database table, but the column order got screwed up when tableadapter got re-configured.
I use tableadapters whenever I need to talk to a SQL database.
There will be times when I needed to add/remove a field in my table and whenever you “refresh” the tableadapter, the newly added field tend to become the last column in the column order. For example, I added step_type_id field into my existing table:
As you can see, I inserted a new column where I want it to be in tblTreeStep in MSSQL Server Management, and instead of doing the same in Visual Studio’s dataset designer, I take the lazy man’s approach of updating my tableadapter by going thru “Configure” again (I just don’t like doing duplicate work of adding the same column in my dataset designer, setting the type, nullable properties again).
When you do that, Visual Studio tags on the new columns at the end of your table, thus not reflecting the true column order of your actual database table.
A quick way to fix this would be to delete all columns but your 1st column (in this example, my obj_id column), go through “Configure” again and add back all your columns.
Your dataset tableadpter should take on the same column order as your database table’s.
I came across a dreaded exception when using tableadapter to update a datarow:
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
I pulled my hair out on this one – read up on concurrency violation and what could cause this exception. MSDN and stackoverflow users would tell you it’s due to a datarow being “detached” from the dataset, or somewhere in code you are doing an update to dataset and your copy of datarow is not the current one.
Whatever, not going to pretend I understand it. Not going to pretend I don’t have a hole in my code somewhere. I just want to update my record Goddammit!
So, here is what you do… go to your dataset designer, right click on the property of your tableadapter and expend on the UpdateCommand property. You’ll want to change the SQL query text’s WHERE cause to only match the primary key ONLY.
For example, the auto-generated update command may be:
SET field1=value1, field2=value2, etc.
WHERE id=@original_id, field1=@original_field1, field2=@originalfield2…
All you got to do is change it to update row with a row ID:
SET field1=value1, field2=value2, etc.
Problem solved! No more concurrency violation exception, but this doesn’t mean you shouldn’t look for places in your code where concurrent updates may take place and put in some safety measures.