none
OledbDataAdapter.Update(dataset) returns 1 but no update made to access database RRS feed

  • Question

  • FIRST I AM ADDING A ROW WITH THIS CODE

    EffortList.CollectionChanged += (o, e) =>
                    {
                        //upon change of observable collection properties datatable is updated is updated
                        #region SynchData
                        if (e.NewItems != null)
                        {
                            foreach (Effort ef in e.NewItems)
                            {
                               
                                EffortTrackerDataSet.EFFORTRow efrow = ApplicationDataSet.EFFORT.NewEFFORTRow();
                                MessageBox.Show("MessageTriggered");
                                efrow.EFRT_APP_NM = ef.BusinessApplicationName;
                                efrow.EFRT_CMNT = ef.Comments;
                                efrow.EFRT_DESC = ef.Description;
                                efrow.EFRT_DT = ef.Date.Value.Date;
                                MemoryStream ms=SmazSerializer.EmpIhHrsListToMemoryStream(ef.EmployeeIDHoursList);
                                byte[] bin = ms.ToArray();
                                efrow.EFRT_EMP_IDHRS_BIN = bin;
                                efrow.EFRT_GLBL_ID = (new Random()).Next(0, 1000000);
                                efrow.EFRT_INCDNT_ID = ef.IncidentID;
                                efrow.EFRT_IS_TRKBL = ef.IsTrackableEffort;
                                efrow.EFRT_TOT_TM = ef.TotalTime;
                                efrow.EFRT_TYP = ef.Type.ToString();
                                MessageBox.Show("Going to add row");
                                ApplicationDataSet.EFFORT.AddEFFORTRow(efrow);
                                MessageBox.Show("Already added");
                            }
                        }



    //////// THEN in data service I have this

    OleDbCommand cmd1 = new OleDbCommand("SELECT EFFORT.EFRT_GLBL_ID, EFFORT.EFRT_TYP, EFFORT.EFRT_INCDNT_ID, EFFORT.EFRT_APP_NM, EFFORT.EFRT_IS_TRKBL, EFFORT.EFRT_DESC, EFFORT.EFRT_CMNT, EFFORT.EFRT_TOT_TM, EFFORT.EFRT_DT, EFFORT.EFRT_EMP_IDHRS_BIN FROM EFFORT INNER JOIN APPLICATION_ACCESS ON EFFORT.EFRT_APP_NM = APPLICATION_ACCESS.APP_NM WHERE (((APPLICATION_ACCESS.USER_ID)=?));");
                        cmd1.Parameters.Add(new OleDbParameter("?", OleDbType.BSTR));
                        cmd1.Parameters["?"].Value = GlobalData.CurrentUser.UserName;
                        CustomDataAdapter.EffortAdapter.Adapter.SelectCommand = cmd1;
                        CustomDataAdapter.EffortAdapter.Adapter.SelectCommand.Connection = new OleDbConnection(CustomDataAdapter.EffortAdapter.Connection.ConnectionString);
                       // CustomDataAdapter.EffortAdapter.Adapter.UpdateCommand.Ad
                        CustomDataAdapter.EffortAdapter.Adapter.SelectCommand.Connection.Open();
                        CustomDataAdapter.EffortAdapter.Fill(efrtdts.EFFORT);
                        CustomDataAdapter.EffortAdapter.Adapter.SelectCommand.Connection.Close();
                        OleDbCommandBuilder cmdbld1 = new OleDbCommandBuilder(CustomDataAdapter.EffortAdapter.Adapter);
                        CustomDataAdapter.EffortAdapter.Adapter.UpdateCommand = cmdbld1.GetUpdateCommand();
                        CustomDataAdapter.EffortAdapter.Adapter.InsertCommand = cmdbld1.GetInsertCommand();
                        CustomDataAdapter.EffortAdapter.Adapter.DeleteCommand=cmdbld1.GetDeleteCommand();


    /// AND FINALLY

    private void EFFORT_RowChanged(object sender, System.Data.DataRowChangeEventArgs e)
            {
               
                if (e.Action == System.Data.DataRowAction.Add)
                {
                    MessageBox.Show("Add event");
                    try
                    {
                        EffortTrackerDataSet.EFFORTDataTable efdt = sender as EffortTrackerDataSet.EFFORTDataTable;
                        MessageBox.Show(e.Row.RowState + "");
                        CustomDataAdapter.EffortAdapter.Adapter.UpdateCommand.Connection.Open();
                        System.Diagnostics.Debug.WriteLine(CustomDataAdapter.EffortAdapter.Adapter.InsertCommand.CommandText);
                        CustomDataAdapter.EffortAdapter.Adapter.Update(efdt);
                        efdt.AcceptChanges();
                        CustomDataAdapter.EffortAdapter.Adapter.UpdateCommand.Connection.Close();
                       
                    }
                    catch (Exception E)
                    {
                        MessageBox.Show(E.Message);
                    }
                }
            }

    NOW ALL HANDLERS ARE REGISTERED....

    Problem is when Effort is added to observable collection EffortList EffortDataSet is Added with a new row. Row changed even triggers, and in it the DataAdapter.Update() is called (which as documentation says pushes data to database with appropriate update,insert,delete statements).

    I have added all Commands to dataadapter SELECTCommand, INSERTCommand, UpdateCommand and DeleteCommand. Interestingly dataadapter.Update(dataset) returns 1 (which means 1 row is affected but I DONT SEE ANY DATA WRITTEN TO DATABASE. I am using access database .accdb . Can anyone pls help

    Sunday, February 10, 2013 4:10 PM

Answers

All replies

  • UPDATE:

    I followed simple steps from a tutorial to create a dataset in vs 2012 designer. I have added no code myself at all and still there is a problem. I have simply set update insert and select commands using the query designer of xsd designer in vs2012. Now after all commands get generated I try to execute insert query. I pass parameters and it shows a message box that row is inserted but i dont find it inserted in the database!!!! Please help.

    (I actually wrote my own code for dataadapters as shown above and found same problem using the dataadapter.update(dataset) which returned 1 but no update was made , so I tried using the designer but no help either!)

    Sunday, February 10, 2013 7:24 PM
  • Hi Smaz,

    Welcome to the MSDN Forum.

    Please try to update the datatable in a separate button click event.

    CustomDataAdapter.EffortAdapter.Adapter.UpdateCommand.Connection.Open();

    You have mentioned you need to insert a row in database, would you like to call the InsertCommand, rather than UpdateCommand?

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 11, 2013 8:26 AM
    Moderator
  • Hi,

    Is the Access file part of your project ? If you are 100% sure the update takes place, a classic catch for beginners is to check the wrong file (for example if the Access file is part of your project, it might be copied to the output directory and this copy could then be updated). Is the data file part of your project ? Have you checked you don't have a copy in your debug or release directory ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by Alexander Sun Thursday, February 28, 2013 5:04 AM
    Monday, February 11, 2013 4:41 PM
  • See the below link. Item #2 seems to cause a lot of headaches:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/ad837d14-31ea-445f-b830-1a8682b69e68


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Alexander Sun Thursday, February 28, 2013 5:05 AM
    Tuesday, February 19, 2013 1:58 PM