SqlDataAdapter

[daveL]

Hello
I have a Windows Form with Controls
when the user clicks the save button
I place the Controls Values into a DataRow
this Row is then added To a DataTable with (dtTmp)
Row count of 1

I Set up DataAdapter with the update command and insertCommand
and it Inserts a new Row into the physical Table when its a Existing Row
with a identity Column
if I issue dtTmp.AcceptChanges() , then it does not insert, But it does not
do the Update

dtTmp is Cleared after each Write its used to insert or update depending
whether the user is adding or editing
so it always has a new row in it , with count of 1.
below is Code im Using

dtTmp.Clear();
dtTmp.Rows.Add(this.oRow)

SqlDataAdapter ad = new SqlDataAdapter();

ad.SelectCommand = new SqlCommand("select Top 0 * from users order by
userid");

ad.SelectCommand.Connection = this.SqlConn1.Conn;

SqlCommandBuilder cmd = new SqlCommandBuilder(ad);

ad.InsertCommand = cmd.GetInsertCommand();

ad.UpdateCommand = cmd.GetUpdateCommand();

ad.UpdateCommand.Connection = this.SqlConn1.Conn;

ad.InsertCommand.Connection = this.SqlConn1.Conn;

ad.UpdateCommand.Parameters.Add("@Userid", SqlDbType.Int);

ad.UpdateCommand.Parameters["@UserId"].SourceColumn = "UserId";



ad.update(dtTmp) // creates duplicate row if dttmp.acceptchanges() not
issued this row does exist in physical table



DaveL

 

[William Vaughn \(MVP\)]

Stop. Unless you're implementing your own routines to handle update
operations (you aren't), you should not use AcceptChanges. It tells ADO.NET
to reset the data changed state on the client dataset.
Next, consider that the CommandBuilder is designed for use behind the scenes
by the VS code-generators, not by application code. See any of the dozens of
articles that discuss this issue.


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________