Am I blind? UpdatedRowSource

  • Thread starter Thread starter jorge
  • Start date Start date
J

jorge

Hello all,
I'm coding with a typed dataset and adapters.
I cannot get the AutoIncrement to be updated to the dataset. Any help would
be gratelly appreciated.
Thanks
/ jorge

Check this out:

The TypedDataSet includes:

table1
---------------- 8< -----------------------
<xs:element name="PolicyID" msdata:AutoIncrement="true"
msdata:AutoIncrementStep="-1" msdata:AutoIncrementSeed="-1" type="xs:int" />
---------------- 8< -----------------------

table2
---------------- 8< -----------------------
<xs:element name="PolicyID" type="xs:int" />
---------------- 8< -----------------------

relations and contraints:
---------------- 8< -----------------------
<xs:unique name="PricePolicyDataSetKey1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Policy" />
<xs:field xpath="mstns:PolicyID" />
</xs:unique>
---------------- 8< -----------------------
<xs:keyref name="PolicyHasPeriods" refer="PricePolicyDataSetKey1">
<xs:selector xpath=".//mstns:Period" />
<xs:field xpath="mstns:PolicyID" />
</xs:keyref>
---------------- 8< -----------------------

Code:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT PolicyID, --- 8< ---
FROM pasPolicy", transaction.Connection);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
builder.RefreshSchema();
adapter.SelectCommand.Transaction = transaction;
adapter.InsertCommand = builder.GetInsertCommand();
adapter.InsertCommand.CommandText += "; SELECT SCOPE_IDENTITY() AS
PolicyID";
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
adapter.Update(policy.Policy);
policy.Policy.AcceptChanges();
 
I haven't used this approach b/c I was under the impression that the
CommandBuilder couldn't handle this, but I'm not 100% sure. Anyway,
typically you need to use a Select statement in the in such as way that the
identity field = ScopeIdentity. As such, once the select is fired, that
value is now set the the new ScopeIDentity and it transfers into that
columns variable. What I mean is if MyID was the first value and the value
of the autoincrement column, you'd do something like select MyId from
Tablename where MyId = Scope_Identity. I've always use Bill Vaughn's method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp
as a template (or you can look at the code the configuration wizard writes
as a guide) and have never had a problem. In this scenario, if
Scope_Identity is 2 , I can't see how that's mapping back to PolicyID.
Output param are another option that works well but I'm guessing you don't
want to change this much. You are naling UpdatedRowSource and you do have
an additional select statement so it looks like you definitely understand
the underlying concepts so I may be talking out of class here, but my guess
is that it's either the lack of field being mapped to that Scope_Identity or
that the commandbuilder can't be used. I can definitely get you through
this though without a commandbuilder if you are interested and Mr Vaughn's
article describes everything I'd do (since I use it) in depth.

HTH,

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
 
Hi Cor:

I think the acceptChanges listed there will cause more problems b/c he's
setting the Autoincrement value to -1. If it's not coming back from the DB
(or is but not getting mapped to the column correctly) then I don't think it
will do anything b/c the numbers there will be negative.Update calls
AcceptChanges so i'm not sure if that will do much here. Refilling w/ the
Adapter at the end should do it but you'd have to clear the table first so
you didn't have double instances of the row/and or violate the integrity
constriants. Plus if you are doing a select each time to get the value,
calling Fill would definitely refresh everything, but it might be
unnecessary overhead.

I've never seen the commandbuilder used in this capacity but there are a lot
of things I've never seen. Have you ever seen the CB used to retrieve the
newly added values?

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
 
Thank you Bill,

I'll study the article and see if I can fix it, I changed my code now so
that it does a insert per row, takes longer time, but it works and I need to
get this baby out tomorrow.

In case I have time I will change it, but I will definetly read the article
Thanks,
/ jorge
 
Hi William,

There is a page where is described what I told, however I could not find it,
I will try to find it tomorrow.

(The only thing is that I left that acceptchanges from the OP, I was in
doubt if that was good)

Cor
 
Hi Bill,

As promished

See the text on this pages (link beneath it).

The Update method will resolve your changes back to the data source, however
other clients may have modified data at the data source since the last time
you filled the DataSet. To refresh your DataSet with current data, use the
DataAdapter and Fill the DataSet again. New rows will be added to the table,
and updated information will be incorporated into existing rows. The Fill
method determines whether a new row will be added or an existing row will be
updated by examining the primary key values of the rows in the DataSet and
the rows returned by the SelectCommand. If the Fill method encounters a
primary key value for a row in the DataSet that matches a primary key value
from a row in the results returned by the SelectCommand, it updates the
existing row with the information from the row returned by the SelectCommand
and sets the RowState of the existing row to Unchanged. If a row returned by
the SelectCommand has a primary key value that does not match any of the
primary key values of the rows in the DataSet, the Fill method adds a new
row with a RowState of Unchanged.

http://msdn.microsoft.com/library/d...conupdatingdatabasewithdataadapterdataset.asp


Cor
 
Yep, I understand all of that but do you think that applies in this
instance considering the sequence of events?

This is what you suggested that confused me:

adapter.Update(policy.Policy);
policy.Policy.AcceptChanges();
adapter.Fill(policy.Policy)

Update calls acceptchanges as soon as it exectues, so calling Acceptchanges
right after update effectively does nothing b/c there are no changes to
accept. Moreover if he's autoincremented the values to -1 and were to call
Acceptchanges beforehand, this would cause a total disaster. I totally
agree with everything in the article, I just don't see how that applies so
maybe we're looking at different examples. He calling a select after update
so it's a different approach isn't it?



--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
Back
Top