locked
Working with the BindingNavigator RRS feed

  • Question

  • Did so:
    namespace ADOnet
    {
      public partial class Form1 : Form
      {
    
        private DataSet ds;
        private OleDbDataAdapter da;
        OleDbCommand cmd;
        OleDbConnection con;
        OleDbParameter p;
        BindingSource bs;
    
        public Form1()
        {
          InitializeComponent();
        }
    
        private void Form1_Load(object sender, EventArgs e)
        {
          openDb();
          ds = new DataSet();
          ds.Tables.Add("tbl1ds");
          da = new OleDbDataAdapter();
          cmd = con.CreateCommand();
          cmd.CommandText = "SELECT *FROM tbl1";
          da.SelectCommand = cmd;
          da.FillSchema(ds, SchemaType.Source, "tbl1ds");
          da.Fill(ds, "tbl1ds");
          cmd.CommandText = "SELECT *FROM tbl2";
          da.SelectCommand = cmd;
          da.FillSchema(ds, SchemaType.Source, "tbl2ds");
          bs = new BindingSource();
          bs.DataSource = ds.Tables[0];
          bsnav.BindingSource = bs;
          dgv1.DataSource = bs;
          daInsert();
          daUpdate();
          daDelete();
        }
    
        private void daInsert()
        {
          string strIns = "INSERT INTO tbl1(id, txt) VALUES (?, ?)";
          OleDbCommand insertCmd = new OleDbCommand(strIns, con);
          da.InsertCommand = insertCmd;
          p = new OleDbParameter("@id", OleDbType.Integer);
          p.SourceColumn = "id";
          p.SourceVersion = DataRowVersion.Original;
          insertCmd.Parameters.Add(p);
          p = new OleDbParameter("@txt", OleDbType.VarChar);
          p.SourceColumn = "txt";
          p.SourceVersion = DataRowVersion.Original;
          insertCmd.Parameters.Add(p);
        }
    
        private void daUpdate()
        {
          string strUpd = "UPDATE tbl1 SET txt = ? WHERE id = ?"; 
          OleDbCommand updateCmd = new OleDbCommand(strUpd, con);
          da.UpdateCommand = updateCmd;
          p = new OleDbParameter("@txt", OleDbType.VarChar);
          p.SourceColumn = "txt";
          p.SourceVersion = DataRowVersion.Current;
          updateCmd.Parameters.Add(p);
          p = new OleDbParameter("@id", OleDbType.Integer);
          p.SourceColumn = "id";
          p.SourceVersion = DataRowVersion.Original;
          updateCmd.Parameters.Add(p);
        }
    
        private void daDelete()
        {
          string strDel = "DELETE FROM tbl1 WHERE id = ? AND txt = ?";
          OleDbCommand deleteCmd = new OleDbCommand(strDel, con);
          da.DeleteCommand = deleteCmd;
          p = new OleDbParameter("@id", OleDbType.Integer);
          p.SourceColumn = "id";
          p.SourceVersion = DataRowVersion.Current;
          deleteCmd.Parameters.Add(p);
          p = new OleDbParameter("@txt", OleDbType.VarChar);
          p.SourceColumn = "txt";
          p.SourceVersion = DataRowVersion.Current;
          deleteCmd.Parameters.Add(p);
        }
    
        private void openDb()
        {
          con = new OleDbConnection();
          con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\\Adonet.mdb";
          try
          {
            con.Open();
          }
          catch
          {
            MessageBox.Show("Ошибка соединения с базой данных AdoStudy.mdb");
            Application.Exit();
          }
        }
    
        private void bindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
          da.Update(ds.Tables[0]);
        }
    
        private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
        {
          DataRowView dr = (DataRowView) bs.Current;
          dr.Delete();
        }
    
        private void bindingNavigatorMoveNextItem_Click(object sender, EventArgs e)
        {
          bs.MoveNext();
        }
    
        private void bindingNavigatorMovePreviousItem_Click(object sender, EventArgs e)
        {
          bs.MovePrevious();
    
        }
    
        private void bindingNavigatorMoveFirstItem_Click(object sender, EventArgs e)
        {
          bs.MoveFirst();
        }
    
        private void bindingNavigatorMoveLastItem_Click(object sender, EventArgs e)
        {
          bs.MoveLast();
        }
    
        private void bindingNavigatorAddNewItem_Click(object sender, EventArgs e)
        {
          bs.AddNew();
        }
      }
    }
    Result:
    - When moving stands out not following line, and in one;
    - Removing the current is removed, and next to it is the line;
    - Correctly only add an entry and transition in the Start / End records.
    Tell me, please, what I did wrong. Code project on http://yadi.sk/d/u-PFPj2Q4PNrq

    Alex

    Sunday, April 28, 2013 8:32 AM

Answers

  • Well, sorry Alex ... I'm an idiot. I don't use the BindingNavigator much, but I just realized that you don't have to have code for  bindingNavigatorDeleteItem_Click, it deletes a row for you automatically without you having to do it. That's why you ended up having two rows deleted ... once by the Navigator and once by you!

     


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by tumanovalex Saturday, May 4, 2013 9:04 AM
    Thursday, May 2, 2013 2:40 PM
  • Hi Alex,

    Is the "id" column your PK (Primary Key) in your database, or is it at least a unique value? Or, is the combination of "id" and "txt" unique? If not, that is most likely what is causing your problems. If "id" is a PK, then your delete command really should not include the "txt" column too. Try changing your deleteCmd to:

    string strDel = "DELETE FROM tbl1 WHERE id = ? ";

    And remove the code for the "txt" parameters.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by tumanovalex Saturday, May 4, 2013 6:31 PM
    Saturday, May 4, 2013 4:18 PM

All replies

  • To be honest, Alex ... I have no idea what you mean by this:

    Result:
    - When moving stands out not following line, and in one;
    - Removing the current is removed, and next to it is the line;
    - Correctly only add an entry and transition in the Start / End records

    The code that you posted looks ok (I did not download the proect), but I am not understanding what you're trying to say about the problems.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, April 28, 2013 4:21 PM
  • Instead of one removed two rows, in the table pointer moves two rows up and top instead of one.

    Alex

    Tuesday, April 30, 2013 4:09 PM
  • So, let's take the first one. You're saying that when you click the Navigator Delete button that two rows get deleted? How do you know that this is what's happening? Are you in the debugger? Looking at the grid? Saving and looking at the database? Obviously only one row should be getting deleted, but I don't know how you're determining what is happening. Could you post more details?

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, May 1, 2013 4:31 AM
  •  The screenshots (after delete and before delete) shows what happens, when I click Delete(X) on Bindingnavigator. Instead of 18 row are deleted lines18 and 19.The lines are removed from Datagridviev. If I click on the Save button (Floppy), the changes are recorded in mdb.


    Alex


    Wednesday, May 1, 2013 8:38 AM
  • Well, sorry Alex ... I'm an idiot. I don't use the BindingNavigator much, but I just realized that you don't have to have code for  bindingNavigatorDeleteItem_Click, it deletes a row for you automatically without you having to do it. That's why you ended up having two rows deleted ... once by the Navigator and once by you!

     


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by tumanovalex Saturday, May 4, 2013 9:04 AM
    Thursday, May 2, 2013 2:40 PM
  • Thanks for the tip. I did not think, the code
         bs = new BindingSource();
         bs.DataSource = ds.Tables[0];
         bsnav.BindingSource = bs;
         dgv1.DataSource = bs;
    provides the key BindingNavigator. Removed the handle button on BindingNavigator, duplication disappeared. However, a new problem: when you delete multiple records and try to save changes I get a message: "Violation of parallelism: DeleteCommand affected 0 of the expected 1 records." and stopping the application on line:
    da.Update (ds.Tables [0])
    in the code
    namespace ADOnet
    {
      public partial class Form1 : Form
      {
    
        private DataSet ds;
        private OleDbDataAdapter da;
        OleDbCommand cmd;
        OleDbConnection con;
        OleDbParameter p;
        BindingSource bs;
    
        public Form1()
        {
          InitializeComponent();
        }
    
        private void Form1_Load(object sender, EventArgs e)
        {
          openDb();
          ds = new DataSet();
          ds.Tables.Add("tbl1ds");
          da = new OleDbDataAdapter();
          cmd = con.CreateCommand();
          cmd.CommandText = "SELECT *FROM tbl1";
          da.SelectCommand = cmd;
          da.FillSchema(ds, SchemaType.Source, "tbl1ds");
          da.Fill(ds, "tbl1ds");
          cmd.CommandText = "SELECT *FROM tbl2";
          da.SelectCommand = cmd;
          da.FillSchema(ds, SchemaType.Source, "tbl2ds");
          bs = new BindingSource();
          bs.DataSource = ds.Tables[0];
          bsnav.BindingSource = bs;
          dgv1.DataSource = bs;
          daInsert();
          daUpdate();
          daDelete();
        }
    
        private void daInsert()
        {
          string strIns = "INSERT INTO tbl1(id, txt) VALUES (?, ?)";
          OleDbCommand insertCmd = new OleDbCommand(strIns, con);
          da.InsertCommand = insertCmd;
          p = new OleDbParameter("@id", OleDbType.Integer);
          p.SourceColumn = "id";
          p.SourceVersion = DataRowVersion.Original;
          insertCmd.Parameters.Add(p);
          p = new OleDbParameter("@txt", OleDbType.VarChar);
          p.SourceColumn = "txt";
          p.SourceVersion = DataRowVersion.Original;
          insertCmd.Parameters.Add(p);
        }
    
        private void daUpdate()
        {
          string strUpd = "UPDATE tbl1 SET txt = ? WHERE id = ?"; 
          OleDbCommand updateCmd = new OleDbCommand(strUpd, con);
          da.UpdateCommand = updateCmd;
          p = new OleDbParameter("@txt", OleDbType.VarChar);
          p.SourceColumn = "txt";
          p.SourceVersion = DataRowVersion.Current;
          updateCmd.Parameters.Add(p);
          p = new OleDbParameter("@id", OleDbType.Integer);
          p.SourceColumn = "id";
          p.SourceVersion = DataRowVersion.Original;
          updateCmd.Parameters.Add(p);
        }
    
        private void daDelete()
        {
          string strDel = "DELETE FROM tbl1 WHERE id = ? AND txt = ?";
          OleDbCommand deleteCmd = new OleDbCommand(strDel, con);
          da.DeleteCommand = deleteCmd;
          p = new OleDbParameter("@id", OleDbType.Integer);
          p.SourceColumn = "id";
          p.SourceVersion = DataRowVersion.Original;
          deleteCmd.Parameters.Add(p);
          p = new OleDbParameter("@txt", OleDbType.VarChar);
          p.SourceColumn = "txt";
          p.SourceVersion = DataRowVersion.Original;
          deleteCmd.Parameters.Add(p);
        }
    
        private void openDb()
        {
          con = new OleDbConnection();
          con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\\Adonet.mdb";
          try
          {
            con.Open();
          }
          catch
          {
            MessageBox.Show("Ошибка соединения с базой данных AdoStudy.mdb");
            Application.Exit();
          }
        }
    
        private void bindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
          da.Update(ds.Tables[0]);
        }
    
        private void bindingNavigatorAddNewItem_Click(object sender, EventArgs e)
        {
          bs.AddNew();
        }
      }
    }
    Can you please tell how to fix this error.

    Alex

    Saturday, May 4, 2013 9:09 AM
  • Hi Alex,

    Is the "id" column your PK (Primary Key) in your database, or is it at least a unique value? Or, is the combination of "id" and "txt" unique? If not, that is most likely what is causing your problems. If "id" is a PK, then your delete command really should not include the "txt" column too. Try changing your deleteCmd to:

    string strDel = "DELETE FROM tbl1 WHERE id = ? ";

    And remove the code for the "txt" parameters.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by tumanovalex Saturday, May 4, 2013 6:31 PM
    Saturday, May 4, 2013 4:18 PM
  • Thank you very much! Removed txt and it worked out!

    Alex

    Saturday, May 4, 2013 6:30 PM
  • You're welcome! Glad I could help, Alex! =0)

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, May 5, 2013 4:30 PM