none
Update Db does not work RRS feed

  • Question

  • Hi  i am updating a table using a dataset. Nothing crashes but the db tabke does not have the dataset entries

    Here s my code. thanks for yr Help

    I ve debugt the Application end the dataset has all the entries i need. But the db table not.

    Code Block

                OleDbConnection CdoConn = new OleDbConnection(ConnString);

                CdoConn.Open();



                string sqlaction = "SELECT *FROM TBL_OverlapGUI";

                OleDbDataAdapter adap_ = new OleDbDataAdapter(sqlaction, CdoConn);

                OleDbCommandBuilder custDB_ = new OleDbCommandBuilder(adap_);

                DataSet temp2 = new DataSet();

               adap_.Fill(temp2, "TBL_OverlapGUI");

               

                for (int i = 0; i < temp.Rows.Count;i++ )

                {



                    DataRow row = temp2.Tables["TBL_OverlapGUI"].NewRow();

                    row = temp.Rows[i];

                    temp2.Tables["TBL_OverlapGUI"].ImportRow(row);

                }

         

              

                adap_.Update(temp2.Tables["TBL_OverlapGUI"]);


    Wednesday, November 7, 2007 7:58 PM

Answers

  • You need to call row.SetAdded(), see code example below that I verified works.

    Reason for this is you are grabbing the row from some datatable and the row state is set to

    DataRowState.Unchanged, then you stick this in another DataTable the state is not set to added.

     

    OleDbConnection CdoConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\dbdemo.accdb;");

    CdoConn.Open();

    /*

    using (OleDbCommand cmd = CdoConn.CreateCommand())

    {

    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "create table TBL_OverlapGUI(id int, f1 text)";

    cmd.ExecuteNonQuery();

    cmd.CommandText = "insert into TBL_OverlapGUI(id,f1) values (1,'hello')";

    cmd.ExecuteNonQuery();

    }

    */

    string sqlaction = "SELECT * FROM TBL_OverlapGUI";

    OleDbDataAdapter adap_ = new OleDbDataAdapter(sqlaction, CdoConn);

    OleDbCommandBuilder custDB_ = new OleDbCommandBuilder(adap_);

    DataSet temp2 = new DataSet();

    adap_.Fill(temp2, "TBL_OverlapGUI");

    DataTable temp = temp2.Tables["TBL_OverlapGUI"].Copy();

    for (int i = 0; i < temp.Rows.Count; i++)

    {

    //DataRow row = temp2.Tables["TBL_OverlapGUI"].NewRow();

    DataRow row = temp.RowsIdea;

    row.SetAdded();

    temp2.Tables["TBL_OverlapGUI"].ImportRow(row);

    }

    MessageBox.Show("temp2.Tables[\"TBL_OverlapGUI\"].Rows.Count=" + temp2.Tables["TBL_OverlapGUI"].Rows.Count);

    adap_.Update(temp2.Tables["TBL_OverlapGUI"]);

    using (OleDbCommand cmd = CdoConn.CreateCommand())

    {

    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "select count(*) from TBL_OverlapGUI";

    int count = (int) cmd.ExecuteScalar();

    MessageBox.Show("count=" + count);

    }

     

    Friday, November 9, 2007 7:27 PM

All replies

  • hi,alkatal

      Add the following two lines,and see whether it works.

    Code Block
                    custDB_.QuotePrefix = "[";
                    custDB_.QuoteSuffix = "]";

     

     

     

    Friday, November 9, 2007 6:30 AM
  • You need to call row.SetAdded(), see code example below that I verified works.

    Reason for this is you are grabbing the row from some datatable and the row state is set to

    DataRowState.Unchanged, then you stick this in another DataTable the state is not set to added.

     

    OleDbConnection CdoConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\dbdemo.accdb;");

    CdoConn.Open();

    /*

    using (OleDbCommand cmd = CdoConn.CreateCommand())

    {

    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "create table TBL_OverlapGUI(id int, f1 text)";

    cmd.ExecuteNonQuery();

    cmd.CommandText = "insert into TBL_OverlapGUI(id,f1) values (1,'hello')";

    cmd.ExecuteNonQuery();

    }

    */

    string sqlaction = "SELECT * FROM TBL_OverlapGUI";

    OleDbDataAdapter adap_ = new OleDbDataAdapter(sqlaction, CdoConn);

    OleDbCommandBuilder custDB_ = new OleDbCommandBuilder(adap_);

    DataSet temp2 = new DataSet();

    adap_.Fill(temp2, "TBL_OverlapGUI");

    DataTable temp = temp2.Tables["TBL_OverlapGUI"].Copy();

    for (int i = 0; i < temp.Rows.Count; i++)

    {

    //DataRow row = temp2.Tables["TBL_OverlapGUI"].NewRow();

    DataRow row = temp.RowsIdea;

    row.SetAdded();

    temp2.Tables["TBL_OverlapGUI"].ImportRow(row);

    }

    MessageBox.Show("temp2.Tables[\"TBL_OverlapGUI\"].Rows.Count=" + temp2.Tables["TBL_OverlapGUI"].Rows.Count);

    adap_.Update(temp2.Tables["TBL_OverlapGUI"]);

    using (OleDbCommand cmd = CdoConn.CreateCommand())

    {

    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "select count(*) from TBL_OverlapGUI";

    int count = (int) cmd.ExecuteScalar();

    MessageBox.Show("count=" + count);

    }

     

    Friday, November 9, 2007 7:27 PM
  • Also note in your code example the following bugs:

     

                    DataRow row = temp2.Tables["TBL_OverlapGUI"].NewRow();

                    row = temp.RowsIdea;

     

    This is same as saying:

     

     

                    // DataRow row = temp2.Tables["TBL_OverlapGUI"].NewRow();

                    DataRow row = temp.RowsIdea;

     

    Also your select statement does not have a space between * and table name, you need to fix this.

    Friday, November 9, 2007 7:28 PM