none
Can't get VS 2005 to modify a Microsoft Access database RRS feed

  • Question

  • I am running some code where I add a row to a Microsoft Access Table.  If I set a breakpoint I can see that the Rows.Count went from two rows to three rows and if I use ds.WriteXML to write to a file the three rows are in it.  But when the application terminates I can open the mdb file and see that only two rows are in the mdb file.  So it appears that the DataSet has the data in it, but it never get written to the mdb file.  I checked and the mdb file and directory are not read only.
    Sunday, March 29, 2009 1:57 AM

All replies

  • Can you display your code on how you are inserting the rows?
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Sunday, March 29, 2009 3:37 PM
  • Sure, I have had a little progress.  Here is my code that works now, but if I add "deviceTable.AcceptChanges()" before the DeviceTableAdapter.Update then it does not update the mdb file, and I don't understand why.

                devicerow.BeginEdit();
                devicerow.DeviceID = deviceid;
                devicerow.CustID = custid;
                devicerow.CompanyID = companyid;
                devicerow.ProductID = productid;
                devicerow.SerialNum = serialnum;
                devicerow.EndEdit();
                if (newrow)
                     deviceTable.AddDeviceRow(devicerow);
                // deviceTable.AcceptChanges();  // causes data not to be saved.
                DataTableAdapter.Update(deviceTable);

    Maybe I am thinking of this wrong, but I wanted to:
    > get a new row or existing row from the Table
    > make changes to the row
    > check a row for errors
    > put the new row or modified row into the into the table.
    > update the dataset with the table changes.
    > update the mdb file.

    I guess my question is does:   "devicerow.EndEdit()" get the Table and DataSet completly up to date?
    Sunday, March 29, 2009 7:27 PM
  • The BeginEdit, CancelEdit, and EndEdit methods allow you to store or cancel a series of changes to the DataRow. For example, you might want to let the user modify the contents of a row and then display a dialog box that gives the user the chance to accept or reject those changes.

    Do you have a DataGridView where this data is displayed and are you are allowing the user make changes and then submitting the updates back to the Database? You indicated in your question, "I am running some code where I add a row to a Microsoft Access Table".

    Something as simple as adding a new row could be done with a method. I am not sure how you have your OleDbAdapter and DataSet set up. You can either submit a new row back to the Database by associating the Update method with an appropriate Update command, or simply like I said create a small method which will do that.

    //Example [Is CustID an autoNumber?]
    private void Add()
    {
         string sql = "INSERT INTO MyTable (DeviceID, CustID, CompanyID, ProductID, SerialNum) VALUES (?, ?, ?, ?, ?)";
         using (OleDbCommand cmd = new OleDbCommand(sql, conn)
         {
              OleDbParameter dID, cID, compID, pID, sNum;
              //DeviceID
              dID = new OleDBParameter("@DeviceID", OleDbType.Integer)
              dID.Value = deviceid;
              cmd.Parameters.Add(dID);

              //CustID
              cID = new OleDBParameter("@CustID", OleDbType.Integer)
              cID.Value = custid;
              cmd.Parameters.Add(cID);

               //CompanyID
              compID = new OleDBParameter("@CompID", OleDbType.Integer)
              compID.Value = companyid;
              cmd.Parameters.Add(compID);

              //ProductID
              pID = new OleDBParameter("@ProductID", OleDbType.Integer)
              pID.Value = productid;
              cmd.Parameters.Add(pID);

              //SerialNum
              sNum = new OleDBParameter("@ProductID", OleDbType.VarChar, 50)
              sNum.Value = serialnum;
              cmd.Parameters.Add(sNum);

              conn.Open();
              cmd.ExecuteNonQuery();
              conn.Close();

         }
    }

    Remember that in OleDB, the parameters go in the order of the sequence whereas in SQL Server, the parameter names match the parameter object. So in SQL Server, you would say @CustID and it would match the parameter object named "@CustID". It is my understanding that using OleDB, it merely goes in the order of the parameters. That is why you can have a ? instead of the @Name.

    Remember, if you use the above approach to rebind after the updation.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Monday, March 30, 2009 1:03 AM `
    Monday, March 30, 2009 12:57 AM
  • Actually the data for a new row comes form a XML message I receive over a network.

    So I had been adding a  row by getting new row out of a table, then populating the row with data, and then do a Table.AddRow.

    Anything wrong with that approach?

    I am using a TableAdapter.

    Thanks for all your help.



    Monday, March 30, 2009 1:27 AM
  • Nothing wrong with that at all. I usually stay away from TableAdapters and stick with DataAdapters, but that is my preference. Also, my preference when working with Access is creating my own CRUD methods rather then doing this so much with a DataSet as such. It seems I have more control that way.

    I agree with Bonnie Berent [C# MVP] who said in another thread the following:

    "TableAdapters suck, big time. It is Microsoft's attempt to "dumb down" data access with drag-and-drop stuff. Why the heck would anyone want to put database access functionality in their DataSet?!?!?  To me, a DataSet should be database agnostic ... it doesn't know, nor care, where it's data comes from. A DataSet is simply a data transport mechanism.

    Definitely stay away from TableAdapters,  ... but DataAdapters are just fine. You have total control over what you want to bring in and send back to the database (well, unless you use the Update() method, which I don't ... I roll my own).
    "
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, March 30, 2009 1:37 AM
  • Very Interesting.  Great comments I will look at the DataAdapter.

    When you add a new data source to your project it automatically creates TableAdapters;  so I assumed that TableAdapters was the "prefered" Microsoft way.

    Does Bonnie mean that the update method on the DataAdapter does not work?

    I was going to move our project from VS2005 to VS2008 so I was going to take a quick look at Linq.  Any thoughts?  I bet the "purest" don't like it.



    Monday, March 30, 2009 6:20 PM
  • I do most everything in code and do not leverage many of the wizards. This gives me full control of what I want to do. Generally I use a config file which stores my connection string and then use the ConfigurationManager to get at it.

    What Bonnie meant was that she writes her own methods for Updating, Inserting, not relying on the DataSet's methods to do that for her, which is was what I prefer as well. My way, or Bonnie's was and is merely preference and some may love using the Update method with the DataSet, so don't take what I say as gospel truth for you.

    LINQ is great for for collections and things of that nature, and it is showing promise for things such as LINQ to SQL and LINQ to Xml, and also LINQ to DataSet operations, but it does not eliminate your need to understand current ADO.NET. You should have full understanding of how to use, leverage, manipulate DataSets, DataTables, DataRows, DataAdapters, Commands, etc....along with the knowledge of understanding relational theory and architecting competent databases.

    Happy coding
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, March 30, 2009 6:34 PM