Re: using dataadapter.update changes the insertcommand.commandtext

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Right before you call Update, set the CommandText for the InsertCommand and
just verify that it's the correct text being used. Something appears to be
changing the commandtext and from the code here, it's hard to tell where.

Another thing though ... Typically, if you use this approach, you're select
command afterward would look something like SELECT ID, SecondColumn,
ThirdColumn FROM WHateverTableName WHERE (ID = SCOPE_IDENTITY())
b/c the way you're doing it now, you're just selecting another value. It
doesn't look like you're using anything as an output paramater so I don't
see how it's getting mapped back to the datacolumn's value since the names
are different and all.

"jerrydy" <jerrydy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EA500385-0324-49D8-B18A-AFD20C41616F@xxxxxxxxxxxxxxxx
>I have a dataadapter where i have changed the insertcommand.commandtext to
> have the following insert and select:
>
> "INSERT INTO my_table (col1) values (@p1); select scope_identity() as pk"
>
> I modify the dataset and perform an update on the dataadapter so the
> changes
> are reflected in the database like so:
>
> da.update(ds.tables("my_table").select(nothing, nothing,
> DataViewRowState.Added))
>
> When I debug it, the dataset's identity column (pk) is not updated with
> what
> is in the database, and when I inspect the insertcommand.commandtext right
> after the update, I noticed that it has been truncated!!
>
> "INSERT INTO my_table (col1) values (@p1)"
>
> The select scope_identity portion is gone and hence my dataset's identity
> column is not updated. What's going on? Thanks!
>
> -Jerry


.

Relevant Pages