vlvawter
asked on
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.
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.
ASKER
Fernando,
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
LearnedOne,
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.
Thanks.
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.
Thanks.
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.
Fernando