none
Writing data to the database mdb using DataAdapter and DataSet RRS feed

  • Question

  • Update tried to make a table of contacts:
        private void Form1_Load(object sender, EventArgs e)
        {
          OleDbConnection connection = new OleDbConnection();
          connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\\AdoStudy.mdb";
          try
          {
            connection.Open();
          }
          catch
          {
            MessageBox.Show("Ошибка соединения с базой данных AdoStudy.mdb");
            Application.Exit();
          }
          cmd = connection.CreateCommand();
          cmd.CommandText = "SELECT *FROM Contacts";
          da.SelectCommand = cmd;
          da.FillSchema(myData, SchemaType.Source, "Contacts");
          da.Fill(myData, "Contacts");
          cmd.CommandText = "SELECT *FROM Phone";
          da.SelectCommand = cmd;
          da.FillSchema(myData, SchemaType.Source, "Phone");
          da.Fill(myData, "Phone");
          DataRelation rl = new DataRelation("relat", myData.Tables["Contacts"].Columns["idContact"],
                            myData.Tables["Phone"].Columns["idContact"], true);
          myData.Relations.Add(rl);
          bsParent.DataSource = myData;
          bsParent.DataMember = "Contacts";
          bsChild.DataSource = bsParent;
          bsChild.DataMember = "relat";
          dgvContacts.DataSource = bsParent;
          dgvPhone.DataSource = bsChild; 
          daInsert();
          daUpdate();
          daDelete();
         }
    
        private void testBaseBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
           da.Update(myData.Tables["Contacts"]);
        }
    
        private void daInsert()
        {
          cmd.CommandText = "INSERT INTO Contacts " +
                            " (idContact, Person, Birthday, YearBirthday) VALUES " + 
                            " (@idContact, @Person, @Birthday, @YearBirthday)";
          da.InsertCommand = cmd;
          pc = da.InsertCommand.Parameters;
          pc.Add("@idContact", OleDbType.Integer, 0, "idContact");
          pc.Add("@Person", OleDbType.VarChar, 0, "Person");
          pc.Add("@Birthday", OleDbType.DBDate, 0, "Birthday");
          pc.Add("@YearBirthday", OleDbType.Integer, 0, "YearBirthday");
        }
    
        private void daUpdate()
        {
          cmd.CommandText = "UPDATE Contacts " +
                            " SET idContact = @idContact_New, Person = @Person_New, " + 
                            " Birthday = @Birthday_New, YearBirthday = @YearBirthday_New " + 
                            " WHERE idContact = @idContact_Old AND Person = @Person_Old AND " + 
                                   " Birthday = @Birthday_Old AND YearBirthday = @YearBirthday_Old";
          da.UpdateCommand = cmd;
          pc = da.UpdateCommand.Parameters;
          pc.Add("@idContact_New", OleDbType.Integer, 0, "idContact");
          pc.Add("@Person_New", OleDbType.VarChar, 0, "Person");
          pc.Add("@Birthday_New", OleDbType.DBDate, 0, "Birthday");
          pc.Add("@YearBirthday_New", OleDbType.Integer, 0, "YearBirthday");
          p = pc.Add("@idContact_Old", OleDbType.Integer, 0, "idContact");
          p.SourceVersion = DataRowVersion.Original;
          p = pc.Add("@Person_Old", OleDbType.VarChar, 0, "Person");
          p.SourceVersion = DataRowVersion.Original;
          p = pc.Add("@Birthday_Old", OleDbType.DBDate, 0, "Birthday");
          p.SourceVersion = DataRowVersion.Original;
          p = pc.Add("@YearBirthday_Old", OleDbType.Integer, 0, "YearBirthday");
        }
    
        private void daDelete()
        {
          cmd.CommandText = "DELETE FROM Contacts " +
                            " WHERE idContact = @idContact AND Person = @Person AND " +
                                   " Birthday = @Birthday AND YearBirthday = @YearBirthday";
          da.DeleteCommand = cmd;
          pc = da.DeleteCommand.Parameters;
          p = pc.Add("@idContact", OleDbType.Integer, 0, "idContact");
          p.SourceVersion = DataRowVersion.Original;
          p = pc.Add("@Person", OleDbType.VarChar, 0, "Person");
          p.SourceVersion = DataRowVersion.Original;
          p = pc.Add("@Birthday", OleDbType.DBDate, 0, "Birthday");
          p.SourceVersion = DataRowVersion.Original;
          p = pc.Add("@YearBirthday", OleDbType.Integer, 0, "YearBirthday");
          p.SourceVersion = DataRowVersion.Original;
        }
    
    If you try to save the data error message does not appear, but the data into the mdb are not made. Can you please tell what went wrong. Project on http://yadi.sk/d/Ov27sbzL49YL0

    Alex

    Thursday, April 18, 2013 3:02 PM

Answers

  • Hi Alex,

    You should try to avoid using the same OledbCommand. That's because OledbCommand is a reference type. If you have changed OledbCommand cmd, and then set da.SelectCommand, da.InsertCommand, da.UpdateCommand and da.DeleteCommand to the same cmd. All the command will be the same with DeleteCommand.

    Try to create a new instance of OledbCommand like:

    da.DeleteCommand = new OledbCommand(...);

    Best regards,


    Chester Hong
    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.

    • Proposed as answer by JohnGrove Tuesday, April 23, 2013 4:39 PM
    • Marked as answer by tumanovalex Wednesday, April 24, 2013 4:06 AM
    Monday, April 22, 2013 9:14 AM
    Moderator

All replies

  • Hi Alex,

    You should try to avoid using the same OledbCommand. That's because OledbCommand is a reference type. If you have changed OledbCommand cmd, and then set da.SelectCommand, da.InsertCommand, da.UpdateCommand and da.DeleteCommand to the same cmd. All the command will be the same with DeleteCommand.

    Try to create a new instance of OledbCommand like:

    da.DeleteCommand = new OledbCommand(...);

    Best regards,


    Chester Hong
    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.

    • Proposed as answer by JohnGrove Tuesday, April 23, 2013 4:39 PM
    • Marked as answer by tumanovalex Wednesday, April 24, 2013 4:06 AM
    Monday, April 22, 2013 9:14 AM
    Moderator
  • There are tools to do this. Be aware your solution will fail. You have a Parent/Child relation in your tables so you have to keep track of that.

    If you use framework version 4 or newer then use the TableAdapterManager.

    http://msdn.microsoft.com/en-us/library/bb384426.aspx

    If not then create at least the SQL commands using a wizard. This leads to a lot of time. 

    If you want to do it complete from scratch I hope Bonnie has a sample for you (I've written a lot about it in past but don't know anymore where that is, be aware you need the rowstate for that).


    Success
    Cor

    • Proposed as answer by JohnGrove Tuesday, April 23, 2013 4:39 PM
    Monday, April 22, 2013 9:51 AM
  • Thank you very much for your answers.

    Alex

    Wednesday, April 24, 2013 4:07 AM