DataSet problems

[Brandon Schenz]

I have created a typed dataset that represents my database. One of the
tables is Customers. The Dataset is empty on creation because I do not want
to load in 10's of thousands of customers.

I'm pulling orders down from my website and populating the empty dataset.
When I call the DataSet.update command I get a Primary Key violation because
it is trying to insert the customers into the database rather than update
it.

My guess is this is because the dataset assumes that it is new because my
dataset started as empty.

How do you suggest that I correct this so that I can take advantage of a
dataset (without loading the complete database) rather than dealing with
each individual customer/order.

Brandon

 

[William Ryan eMVP]

Hi Brandon

I have created a typed dataset that represents my database. One of the
tables is Customers. The Dataset is empty on creation because I do not want
to load in 10's of thousands of customers.

I'm pulling orders down from my website and populating the empty dataset.
When I call the DataSet.update command I get a Primary Key violation because
it is trying to insert the customers into the database rather than update
it.

Have you added any new rows? I only ask b/c if you are positive this is the
problme then the course of corrective action will be a bit different.
However, if you are adding a row or more, then that may in fact be the
problem and the value you think is the key may not be...that could
potentially be the problem.

My guess is this is because the dataset assumes that it is new because my
dataset started as empty.

That's the exact opposite of the default behavior. Normally,
..AcceptChangesDuringFill is set to true which effectively negates the
rowstate change. I'd check this property first and see if it isn't set to
false somewhere...that's really what this sounds like.

How do you suggest that I correct this so that I can take advantage of a
dataset (without loading the complete database) rather than dealing with
each individual customer/order.

If it's not set to true as mentioned above, then something is apparently
changing it, so before you edit the row, you can call .AcceptChanges on the
row (or groups of row) and then all changes in the world won't cause those
rows to be seen as Added. However, something is causing this and I'd get to
the bottom of it.

If you loop through your datatable and write out the rowstate, verify
everything is what you think it is (another easy approach is to create a
dataview and set the RowStateFilter .

foreach(DataRow dro in myDataSets.Tables[0].Rows){

Debug.WriteLine(dro.RowState.ToString());
}

Brandon

One of these should fix it.so let me know about the rowstate and we'll take
it from there

HTH<

Bill


www.devbuzz.com
www.knowdotnet.com

 

[Brandon Schenz]

I believe that I did not explain properly.

I have an empty dataset.

I manually add rows to the dataset from text based files that represent
orders from my website.

I create a DbAdapter, and appropriate commands to my database.

I attempt to call DbAdapter.Update(DataSet) and that is where I get a
primary key violation if a customer in my manually created dataset already
exists in the database.

Thank you for your swift reply on this matter.

Brandon

Hi Brandon

I have created a typed dataset that represents my database. One of the
tables is Customers. The Dataset is empty on creation because I do not want
to load in 10's of thousands of customers.

I'm pulling orders down from my website and populating the empty dataset.
When I call the DataSet.update command I get a Primary Key violation because
it is trying to insert the customers into the database rather than update
it.

Have you added any new rows? I only ask b/c if you are positive this is the
problme then the course of corrective action will be a bit different.
However, if you are adding a row or more, then that may in fact be the
problem and the value you think is the key may not be...that could
potentially be the problem.

My guess is this is because the dataset assumes that it is new because my
dataset started as empty.

That's the exact opposite of the default behavior. Normally,
.AcceptChangesDuringFill is set to true which effectively negates the
rowstate change. I'd check this property first and see if it isn't set to
false somewhere...that's really what this sounds like.

How do you suggest that I correct this so that I can take advantage of a
dataset (without loading the complete database) rather than dealing with
each individual customer/order.

If it's not set to true as mentioned above, then something is apparently
changing it, so before you edit the row, you can call .AcceptChanges on the
row (or groups of row) and then all changes in the world won't cause those
rows to be seen as Added. However, something is causing this and I'd get to
the bottom of it.

If you loop through your datatable and write out the rowstate, verify
everything is what you think it is (another easy approach is to create a
dataview and set the RowStateFilter .

foreach(DataRow dro in myDataSets.Tables[0].Rows){

Debug.WriteLine(dro.RowState.ToString());
}

Brandon

One of these should fix it.so let me know about the rowstate and we'll take
it from there

HTH<

Bill


www.devbuzz.com
www.knowdotnet.com

 

[Erik Frey]

I believe that I did not explain properly.

I have an empty dataset.

I manually add rows to the dataset from text based files that represent
orders from my website.

I create a DbAdapter, and appropriate commands to my database.

I attempt to call DbAdapter.Update(DataSet) and that is where I get a
primary key violation if a customer in my manually created dataset already
exists in the database.

Thank you for your swift reply on this matter.

Brandon

You are not using the DataAdapter in the way it was intended - that's why
you're having problems. You have two options:

1) Use the DataAdapter.Fill method to load the data first, then make the
changes from your text files, then call the DataAdapter.Update method.

2) Execute the UPDATE SQLCommand yourself. If you're using a DataAdapter
you probably already have an autogenerated SQLCommand object. You can loop
through your text file, fill the paramaters in the SQLCommand object, and
then call the SQLCommand.ExecuteNonQuery method.

Erik

 

[William Ryan eMVP]

Ok, that's not a big deal. Since your usage may result in a rowstate of
Added even though the record already exists in the db you have two choices I
can think of.

1) you can loop through each row and check if it exists in the DB. If it
does, call .AcceptChanges on it. If not, leave it alone. When you are
done, call update and all will be well. If you need to edit the rows at all,
call AcceptChanges at the onset, otherwise you can run this right before you
call update.
2) You can modify your Update logic w/ a if not exists statement or
something similar so that you ignore the record if it exists. AcceptChanges
will be called at the end so everything should be in synx after you call
update.

Either way you are going to have to check those values against the db if you
want to use .Update b/c that's the whole concept behind calling update is
predicated upon RowState matches/differences between the local data and the
back end. That's why you can call, for instance, AccceptChangesDuringFill
as true or false.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

I believe that I did not explain properly.

I have an empty dataset.

I manually add rows to the dataset from text based files that represent
orders from my website.

I create a DbAdapter, and appropriate commands to my database.

I attempt to call DbAdapter.Update(DataSet) and that is where I get a
primary key violation if a customer in my manually created dataset already
exists in the database.

Thank you for your swift reply on this matter.

Brandon

Hi Brandon


Have you added any new rows? I only ask b/c if you are positive this is the
problme then the course of corrective action will be a bit different.
However, if you are adding a row or more, then that may in fact be the
problem and the value you think is the key may not be...that could
potentially be the problem.

That's the exact opposite of the default behavior. Normally,
.AcceptChangesDuringFill is set to true which effectively negates the
rowstate change. I'd check this property first and see if it isn't set to
false somewhere...that's really what this sounds like.

If it's not set to true as mentioned above, then something is apparently
changing it, so before you edit the row, you can call .AcceptChanges on the
row (or groups of row) and then all changes in the world won't cause those
rows to be seen as Added. However, something is causing this and I'd

get

to
the bottom of it.

If you loop through your datatable and write out the rowstate, verify
everything is what you think it is (another easy approach is to create a
dataview and set the RowStateFilter .

foreach(DataRow dro in myDataSets.Tables[0].Rows){

Debug.WriteLine(dro.RowState.ToString());
}

Brandon

One of these should fix it.so let me know about the rowstate and we'll take
it from there

HTH<

Bill


www.devbuzz.com
www.knowdotnet.com

 

[William Ryan eMVP]

Hi Erik:

You are not using the DataAdapter in the way it was intended - that's why
you're having problems. You have two options:

While I agree that this is nontraditional usage, I respectfully disagree
about it not being the 'intended' way... there's not really an intended way
to use DataAdapters as such. Their job is to move data around. They make
that determination by looking at rowstate. If those values weren't in the
db, the rowstate locally would match the actual state in the db and all
would be well. Assuming that you call Fill first would only correct the
problem if the state of the data on the back end didn't change. If it did,
you could still have concurrency exceptions. To that end, this problem is
not much different than the scenario where you called Fill first but were in
a multiuser environment where the data could change.

1) Use the DataAdapter.Fill method to load the data first, then make the
changes from your text files, then call the DataAdapter.Update method.

2) Execute the UPDATE SQLCommand yourself. If you're using a DataAdapter
you probably already have an autogenerated SQLCommand object. You can loop
through your text file, fill the paramaters in the SQLCommand object, and
then call the SQLCommand.ExecuteNonQuery method.

Erik

Bill

www.devbuzz.com
www.knowdotnet.com

 

[Brandon Schenz]

Ok, I must be more of a newbie than I thought.

I understand what you are saying in both cases, but I am unable to visualize
the code for how either would be accomplished.

I think that option one would be better because I would want to update the
local DS with the CustomerID if they are already in the DB, and looping
through my DS to see if they are in the DB gives me the opportunity to pull
back the CustomerID and add that to the DS then call .AcceptChanges. My
question is how do you code the looping and checking?

It is probably an obvoius answer, but I just can't see it right now.

Brandon

Ok, that's not a big deal. Since your usage may result in a rowstate of
Added even though the record already exists in the db you have two choices I
can think of.

1) you can loop through each row and check if it exists in the DB. If it
does, call .AcceptChanges on it. If not, leave it alone. When you are
done, call update and all will be well. If you need to edit the rows at all,
call AcceptChanges at the onset, otherwise you can run this right before you
call update.
2) You can modify your Update logic w/ a if not exists statement or
something similar so that you ignore the record if it exists. AcceptChanges
will be called at the end so everything should be in synx after you call
update.

Either way you are going to have to check those values against the db if you
want to use .Update b/c that's the whole concept behind calling update is
predicated upon RowState matches/differences between the local data and the
back end. That's why you can call, for instance, AccceptChangesDuringFill
as true or false.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

I believe that I did not explain properly.

I have an empty dataset.

I manually add rows to the dataset from text based files that represent
orders from my website.

I create a DbAdapter, and appropriate commands to my database.

I attempt to call DbAdapter.Update(DataSet) and that is where I get a
primary key violation if a customer in my manually created dataset already
exists in the database.

Thank you for your swift reply on this matter.

Brandon
is
the because
my

set

to

of

a

dataset (without loading the complete database) rather than dealing with
each individual customer/order.

If it's not set to true as mentioned above, then something is apparently
changing it, so before you edit the row, you can call .AcceptChanges

on
the

row (or groups of row) and then all changes in the world won't cause those
rows to be seen as Added. However, something is causing this and I'd

get

to
the bottom of it.

If you loop through your datatable and write out the rowstate, verify
everything is what you think it is (another easy approach is to create a
dataview and set the RowStateFilter .

foreach(DataRow dro in myDataSets.Tables[0].Rows){

Debug.WriteLine(dro.RowState.ToString());
}

Brandon



One of these should fix it.so let me know about the rowstate and we'll take
it from there

HTH<

Bill


www.devbuzz.com
www.knowdotnet.com

 

[William Ryan eMVP]

It would be something like:

foreach(DataRow dro in myDataTable.Rows){
cmd.Parameters["@whatever"].Value = dro[0].ToString();
bool b = cmd.ExecuteScalar();
if(b){
dro.AcceptChanges();
}
}

For your command text you could use a Select COUNT(*) from Table where
KeyValue = @whatever"

I'm hesitant to recommend Count(*) in general b/c it's preferable to use
Output params but since you are using Access, I haven't used them w/ Access
and not sure they're available. IF they are, they'd be more efficient, but
this shoudl work..

HTH,

Bill


www.devbuzz.com
www.knowdotnet.com

Ok, I must be more of a newbie than I thought.

I understand what you are saying in both cases, but I am unable to visualize
the code for how either would be accomplished.

I think that option one would be better because I would want to update the
local DS with the CustomerID if they are already in the DB, and looping
through my DS to see if they are in the DB gives me the opportunity to pull
back the CustomerID and add that to the DS then call .AcceptChanges. My
question is how do you code the looping and checking?

It is probably an obvoius answer, but I just can't see it right now.

Brandon

Ok, that's not a big deal. Since your usage may result in a rowstate of
Added even though the record already exists in the db you have two

choices

I
can think of.

1) you can loop through each row and check if it exists in the DB. If it
does, call .AcceptChanges on it. If not, leave it alone. When you are
done, call update and all will be well. If you need to edit the rows at all,
call AcceptChanges at the onset, otherwise you can run this right before you
call update.
2) You can modify your Update logic w/ a if not exists statement or
something similar so that you ignore the record if it exists. AcceptChanges
will be called at the end so everything should be in synx after you call
update.

Either way you are going to have to check those values against the db if you
want to use .Update b/c that's the whole concept behind calling update is
predicated upon RowState matches/differences between the local data and the
back end. That's why you can call, for instance, AccceptChangesDuringFill
as true or false.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

this

is set

advantage

of dealing
with

..AcceptChanges

on

the
row (or groups of row) and then all changes in the world won't cause those
rows to be seen as Added. However, something is causing this and

I'd
get

to
the bottom of it.

If you loop through your datatable and write out the rowstate, verify
everything is what you think it is (another easy approach is to

create
a

dataview and set the RowStateFilter .

foreach(DataRow dro in myDataSets.Tables[0].Rows){

Debug.WriteLine(dro.RowState.ToString());
}

Brandon



One of these should fix it.so let me know about the rowstate and we'll
take
it from there

HTH<

Bill


www.devbuzz.com
www.knowdotnet.com

 

[Erik Frey]

It would be something like:

foreach(DataRow dro in myDataTable.Rows){
cmd.Parameters["@Whatever"].Value = dro[0].ToString();
bool b = cmd.ExecuteScalar();
if(b){
dro.AcceptChanges();
}
}

For your command text you could use a Select COUNT(*) from Table where
KeyValue = @Whatever"

I'm hesitant to recommend Count(*) in general b/c it's preferable to use
Output params but since you are using Access, I haven't used them w/ Access
and not sure they're available. IF they are, they'd be more efficient, but
this shoudl work..

HTH,

Bill

This is a good suggestion - however, you have to keep in mind that the
autogenerated update commands follow the optimistic concurrency model. If
you look at the command strings generated, they usually have a long where
clause like this:

WHERE (SomeValue = @Original_SomeValue) AND (NullableSomeValue =
@Original_NullableSomeValue OR @Original_NullableSomeValue IS NULL AND
NullableSomeValue IS NULL) AND ...

The where clause can go on for miles and miles. What this means is that you
may try to update a row that has different Original values than the values
in the database, because you've ignored the values in the database by not
initially calling the Fill method.

Thereby not using the DataAdapter in its intended manner

You can get around this by limiting the WHERE clause in your commands to
just the primary key (WHERE MyKey = @Original_MyKey) but keep in mind that
working this way will introduce concurrency issues in high-volume
environments.

Erik

 

[William Ryan eMVP]

I agree with your point below but I recommended rolling his own logic for
updates. As far as using an Adapter with a Commandbuilder, then I agree
with you 100% on the usage issue. However there are a many scenarios that
you don't ever retrieve data from a db, or you want those concurrency issues
to pop up so you can prompt the user and have them respond specifically.
That's why you can call .AcceptChanges on a row basis or
continueUpdateOnError or myriad other methods. I definitely agree with you
though in that how you handle concurrency has a huge effect on what you are
able to do and what you may have to do to get where you're going.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

It would be something like:

foreach(DataRow dro in myDataTable.Rows){
cmd.Parameters["@Whatever"].Value = dro[0].ToString();
bool b = cmd.ExecuteScalar();
if(b){
dro.AcceptChanges();
}
}

For your command text you could use a Select COUNT(*) from Table where
KeyValue = @Whatever"

I'm hesitant to recommend Count(*) in general b/c it's preferable to use
Output params but since you are using Access, I haven't used them w/ Access
and not sure they're available. IF they are, they'd be more efficient, but
this shoudl work..

HTH,

Bill

This is a good suggestion - however, you have to keep in mind that the
autogenerated update commands follow the optimistic concurrency model. If
you look at the command strings generated, they usually have a long where
clause like this:

WHERE (SomeValue = @Original_SomeValue) AND (NullableSomeValue =
@Original_NullableSomeValue OR @Original_NullableSomeValue IS NULL AND
NullableSomeValue IS NULL) AND ...

The where clause can go on for miles and miles. What this means is that you
may try to update a row that has different Original values than the values
in the database, because you've ignored the values in the database by not
initially calling the Fill method.

Thereby not using the DataAdapter in its intended manner

You can get around this by limiting the WHERE clause in your commands to
just the primary key (WHERE MyKey = @Original_MyKey) but keep in mind that
working this way will introduce concurrency issues in high-volume
environments.

Erik