SqlDataAdapter

Discussion in 'Microsoft ADO .NET' started by daveL, Oct 19, 2008.

  1. daveL

    daveL Guest

    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
     
    daveL, Oct 19, 2008
    #1
    1. Advertisements

  2. 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)
    ____________________________________________________________________________________________



    "daveL" <> wrote in message
    news:jzGKk.4124$...
    > 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\), Oct 20, 2008
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jan Nielsen

    SqlDataAdapter question

    Jan Nielsen, Jul 5, 2003, in forum: Microsoft ADO .NET
    Replies:
    2
    Views:
    1,837
    Bob Grommes
    Jul 7, 2003
  2. Programatix

    Question about SqlDataAdapter.RowUpdating Event

    Programatix, Jul 18, 2003, in forum: Microsoft ADO .NET
    Replies:
    7
    Views:
    612
    Programatix
    Jul 19, 2003
  3. Willy Esteban

    SQLDataAdapter filled from temporary table

    Willy Esteban, Jul 21, 2003, in forum: Microsoft ADO .NET
    Replies:
    7
    Views:
    5,529
    Willy Esteban
    Jul 22, 2003
  4. Kal
    Replies:
    2
    Views:
    7,054
  5. zdrakec
    Replies:
    1
    Views:
    1,151
    zdrakec
    Sep 5, 2012
Loading...

Share This Page