Dataset Row State question

  • Thread starter Thread starter spebola
  • Start date Start date
S

spebola

I am using VB.Net 2003 Professional, .net Framework 1.1 writing a
Windows Form application.

My application requires that I provide an option to copy a set of data
from a data table, change the effective data and at least one other
item, and add this modified data to the data table. For example, a
data table contains a list of owners in an oil well and their
percentage of ownership. At some point, an owner sells his/her
interest to another entity effective at the beginning of the month.

Ownership Table.
12/2002 Owner A 25%
12/2002 Owner B 25%
12/2002 Owner C 25%
12/2002 Owner D 25%

Effective 03/2003 Owner A sells his interest to Owner B resulting in
the following table:

03/2003 Owner B 50%
03/2003 Owner C 25%
03/2003 Owner D 25%


I fill a dataset using the existing Table's data and display this data
using a data-bound datagrid. The user makes the appropriate changes,
and row 1 has a Row State of Deleted, row 2 has a Row State of
Modified, and rows 3-4 have a Row State of unchanged. If I update the
data table using this dataset, I replace the existing data. I need to
keep the existing data, and add 3 new rows with a different effective
date. The Row State is a read only property. One soultion that comes
to mind is to create a second data set with the schema for the data
table, add the rows, and use this dataset to update the data table. Is
there an easier method to accomplish this? I am using the data
adapter's command builder to update the dataset.
 
May be you can do this by checking rowstate properties. However, I would
like to suggest you to change your interface. you should not allow change
your existing rows in the interface, if you put new dates. You should it
allow by inserting rows.

Rajesh Patel.
 
Thanks for the response Rajesh.

I am not allowing changes to the existing rows. I want to start with
existing rows, since a well could have a thousand owners, so the user
does not have to re-key all the data. A new effective date will be
added to the the existings rows along with the changes the user makes.
The problem is the row state will show modified and I need it to show
added. I know I can use a different dataset and add the rows to it from
the first data set, and update the data table with the second dataset.
I was just hoping that someone had found an easier way.

I need to be able to access both sets of owners by effective date since
prior month adjustments ( as far back as 2 years) are common.
 
It's easy, if you want to insert instead of update statement.
ignore state of the row.

change dataadapter's updatecommand to insertcommand.

instead of "update <tablename> set <colname> = <value>" write, "insert into
<tablename>....."

if you are using SP, then call SP for insert.

in insert statement, just check newvalues against oldvalues in the db, if
there is a change in value of a single column, allow insert otherwise not.

Hope this would be your solution.

Rajesh Patel
 
Thanks for your reply, Rajesh.

Ben, I'd like to add some additional information on this issue.

Rajesh's suggestion works perfect when we need to add the modified and
added rows into the database. However, if you also want to add the
unchanged rows, you need to spend some additional efforts on this. Because
if the RowState is Unchanged, no command will be executed.

If you also need to add the unchanged rows, I think you can try to use the
DataAdapter.AcceptChangesDuringFill property to achieve this. The default
value for this property is true. Setting this property to false before you
call DataAdapter.Fill() will make all the RowState in the result set to be
Added. So when you call DataAdapter.Update() after you finished editing,
all the rows will be inserted into the database instead of being modified.
And if you also want to add the modified rows, modify UpdateCommand as
Rajesh metioned.

I've written a code snippet here: (Assuming that you're using SQL Server as
the database system.)

SqlDataAdapter sda = new
SqlDataAdapter("SELECT * FROM Table1", this SqlConnection1);
sda.AcceptChangesDuringFill = false;
this.ds = new DataSet();
sda.Fill(ds);

For more information about DataAdapter.AcceptChangesDuringFill property,
please refer to the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatacommondataadapterclassacceptchangesduringfilltopic.asp

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: "Rajesh Patel" <[email protected]>
| References: <#[email protected]>
<[email protected]>
| Subject: Re: Dataset Row State question
| Date: Mon, 13 Oct 2003 21:39:27 -0400
| Lines: 40
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: pool-138-89-38-192.mad.east.verizon.net 138.89.38.192
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:63593
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| It's easy, if you want to insert instead of update statement.
| ignore state of the row.
|
| change dataadapter's updatecommand to insertcommand.
|
| instead of "update <tablename> set <colname> = <value>" write, "insert
into
| <tablename>....."
|
| if you are using SP, then call SP for insert.
|
| in insert statement, just check newvalues against oldvalues in the db, if
| there is a change in value of a single column, allow insert otherwise not.
|
| Hope this would be your solution.
|
| Rajesh Patel
|
|
| | > Thanks for the response Rajesh.
| >
| > I am not allowing changes to the existing rows. I want to start with
| > existing rows, since a well could have a thousand owners, so the user
| > does not have to re-key all the data. A new effective date will be
| > added to the the existings rows along with the changes the user makes.
| > The problem is the row state will show modified and I need it to show
| > added. I know I can use a different dataset and add the rows to it from
| > the first data set, and update the data table with the second dataset.
| > I was just hoping that someone had found an easier way.
| >
| > I need to be able to access both sets of owners by effective date since
| > prior month adjustments ( as far back as 2 years) are common.
| >
| >
| >
| >
| > Don't just participate in USENET...get rewarded for it!
|
|
|
 
Thanks Kevin and Rajesh for the responses. Both response include good
ideas.

In this case, all row states will be either modified, deleted or added
since the new effective date will be placed in each row. I need to
ignore the deleted rows, and the modified rows need a state of added.
The insert instead of update method of the data adapter would accomplish
this for modified rows and added rows. I don't know about the deleted
rows. I have decide to examine each row in the dataset, and if its
state is deleted ignore it, and for all other rows add them to another
dataset so the row state will be added for all non-deleted rows. Using
this method means I will not have to change my dll that passes changes
to the database. I can just pass the second ds to the dll and still use
the command bulider. FYI, I am codibg this application to work with
either SQL Server 2000 or Oracle 8i. All data retrieval, updates and
insertions occur in the dll.

Again, thanks for the responses. By the way Kevin, I tried to access
the link you provided and I got the message "display denied" or
something similar.
 
Hi Ben,

I don't know why you cannot access it. The URL was broken into two lines.
Did you only used one line? If you still cannot access it, please try to
search for "DataAdapter.AcceptChangesDuringFill property" in MSDN instead.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: anonymous <[email protected]>
| References: <#[email protected]>
| X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
| Subject: Re: Dataset Row State question
| Mime-Version: 1.0
| Content-Type: text/plain; charset="us-ascii"
| Content-Transfer-Encoding: 7bit
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Date: Tue, 14 Oct 2003 06:23:41 -0700
| NNTP-Posting-Host: actionjackson133.dsl.frii.net 216.17.147.133
| Lines: 1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:63620
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Thanks Kevin and Rajesh for the responses. Both response include good
| ideas.
|
| In this case, all row states will be either modified, deleted or added
| since the new effective date will be placed in each row. I need to
| ignore the deleted rows, and the modified rows need a state of added.
| The insert instead of update method of the data adapter would accomplish
| this for modified rows and added rows. I don't know about the deleted
| rows. I have decide to examine each row in the dataset, and if its
| state is deleted ignore it, and for all other rows add them to another
| dataset so the row state will be added for all non-deleted rows. Using
| this method means I will not have to change my dll that passes changes
| to the database. I can just pass the second ds to the dll and still use
| the command bulider. FYI, I am codibg this application to work with
| either SQL Server 2000 or Oracle 8i. All data retrieval, updates and
| insertions occur in the dll.
|
| Again, thanks for the responses. By the way Kevin, I tried to access
| the link you provided and I got the message "display denied" or
| something similar.
|
|
|
|
| Don't just participate in USENET...get rewarded for it!
|
 
Back
Top