Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

Why isn't a dataset updating

I'm at a loss and don't even know what code to share to have someone help me.

I have 3 tables: client, spouse, addresses.  I have datasets for each one.  The client record is the primary table and has its primary key in spouse and addresses.  

I can create a client and modify as I wish.

After creating the client, I can add a spouse and modify as needed.

After creating the client, I can add an address with no problem.  Once I try to modify I get the exception, Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

I am at a loss of where to begin to solve this.  For spouse and addresses I have added identical sql queries, where spouse.clientid = client.id and addresses.clientid = client.id (otherwise I would be able to pull the correct data to match the client, which is happening just fine).

Can someone help?  I've spent hours on this and have given us.
  • 2
1 Solution
Fernando SotoRetiredCommented:
Hi vlvawter;

Have you added a CommandBuilder object to the DataAdapter? It sounds like you do not have the commands set up in the adapter.

If not add a line of code line this after you have created the DataAdapter.

If using SQL classes

    Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

If using OleDB classes

    Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)

Where the da in the above commands are the respective DataAdapters, SqlDataAdapter or OleDbDataAdapter.

Hope that this is of some help.

vlvawterAuthor Commented:

I have created the dataset with the built-in wizards, which is supposed to set up all the lower level commands for me.  I have created all three tables from the same database.  Two of them work every time (without my having to create a CommandBuilder) and one works intermittantly.  

I can go the route you're talking about, but I'd rather know first of all if there is a quirk in the auto-dataset routine that I don't know about.  I'm going to wait and see if anyone knows the answer to this.  If not, I'll have to go your route and code on a lower level.  I was wanting to avoid that.  If I have to go that route, I'll award you the points.
Bob LearnedCommented:
When you dragged the data adapter to your form, and went through the wizard, did it say that it successfully created all 4 command types (Select, Delete, Insert, and Update)?  In order to use the Select SQL statement to build the other 3 commands, you need to include primary key information.

vlvawterAuthor Commented:

Your comments put me onto the path that finally solved my problem.  I wanted more than a solution; I wanted the reason why I was having problem.  Finally I realized that if I were updating a record, unless I created a sql query that found my record, then I had to have a primary key.  The adapter would not know which record to update otherwise.  Once I added a primary key, deleted and recreated the datasets, everything worked fine.


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now