locked
c# - How to update access database from datagridview RRS feed

  • Question

  • this ng change
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.OleDb;
    
    namespace Client_Control
    {
        public partial class Customer_Details : Form
        {
            private DataTable detailTable;
            private string query;
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + "\\MainClientDataFile.mdb;Persist Security Info=False");
            
            public Customer_Details(string tabName)
            {
                InitializeComponent();
                this.query = string.Format("SELECT ContactName , ContactNumber , Device , Manufacturer, Model," + " Type, PartNo, SerialNo" + " FROM {0}", tabName);
                this.Text = tabName;
                conn.Open();
                detailTable = new DataTable();
                string tableName = tabName;
                OleDbDataAdapter detailAdapter = new OleDbDataAdapter(query, conn);
                if (detailAdapter != null)
                {
                    detailAdapter.Fill(detailTable);
                }
                dataGridView1.DataSource = detailTable;
                conn.Close();
            }
            private void editClick(object sender, EventArgs e)
            {
                dataGridView1.ReadOnly = false;
            }
            private void saveClick(object sender, EventArgs e)
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = new OleDbCommand(query, conn);
                OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
                adapter.Update(detailTable);
            }
    
            private void addClick(object sender, EventArgs e)
            {
                detailTable.Rows.Add();
                dataGridView1.DataSource = detailTable;
            }
    
            private void removeClick(object sender, EventArgs e)
            {
                try
                {
                    dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
                }
                catch (ArgumentOutOfRangeException)
                {
                    MessageBox.Show("  Click the arrow beside the row you want to delete to highlight it, then click Edit --> Remove Row to delete a row  ");
                }
            }
        }
    }
    

    avis  the code i am running but it keeps giving me errors when i try s

    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    • Moved by CoolDadTx Tuesday, July 23, 2013 2:04 PM Winforms related
    Sunday, July 21, 2013 4:18 PM

Answers

  • thank you guys and lady so much, my problem has been solved

    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be


    http://social.msdn.microsoft.com/Forums/en-US/26bf838b-10ee-41ff-97d7-7da55ec6188b/c-dataadapter-update-from-datagridview-throwing-errors-oledb
    • Edited by BolorunduroWB Saturday, July 27, 2013 4:27 PM more info
    • Marked as answer by BolorunduroWB Saturday, July 27, 2013 4:27 PM
    Saturday, July 27, 2013 4:25 PM

All replies

  • It would help us to help you if we know what error you are getting and at what line it is occurring.

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, July 21, 2013 4:26 PM
  • this is the error i get

    Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Sunday, July 21, 2013 5:20 PM
  • Hi,

    if you use the CommandBuilder to update a table it must include a primary key. The DataAdapter create this only if MissingSchemaAction.AddWithKey is set, see Adding Existing Constraints to a DataSet

    As is it simpler to reuse the DataAdapter, store it as member variable:

    OleDbDataAdapter detailAdapter;
    OleDbCommandBuilder detailbuilder;
    DataTable detailTable;
    
    public void Fill(string tabName)
    {
        // StartupPath is safer the CurrentDirectory (can change)
        string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=" 
            +    System.IO.Path.Combine(Application.StartupPath, "MainClientDataFile.mdb");
                    var connection = new OleDbConnection(connectionString);
    
        // the query must include primary key column(s)
        string query = string.Format("SELECT ContactName , ContactNumber, Device, Manufacturer, Model," 
                + " Type, PartNo, SerialNo" + " FROM [{0}];", tabName);
      
        detailAdapter = new OleDbDataAdapter(query, connection);
        detailAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        detailAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
    
        detailbuilder = new OleDbCommandBuilder(detailAdapter);
    
        detailTable = new DataTable(tabName);
        detailAdapter.Fill(detailTable);
    }

    And the Update is just a detailAdapter.Update(detailTable);

    Regards, Elmar


    • Edited by Elmar Boye Tuesday, July 23, 2013 12:39 PM code fix
    Sunday, July 21, 2013 6:33 PM
  • I find some links related to your question, I hope below threads can help you.

    http://social.msdn.microsoft.com/Forums/en-US/f0305119-3ace-4972-8103-46270d78fe55/update-access-database-from-datagridview-vb-2010

    http://social.msdn.microsoft.com/Forums/vstudio/en-US/a75f1783-31ac-4865-807a-168cd61a94a3/how-to-update-an-access-database-c


    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. My sample

    Monday, July 22, 2013 3:14 AM
  • @Learning -- I don't see how those two links really address the OP's problem. I believe that the reply from @Elmar actually addresses the error that @Bolorunduro was getting. Sorry.

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, July 22, 2013 4:30 AM
  • thank you for the reply, I tried your example code with the addition of InitializeComponent(); and it ran but when i tried to save using the click handler of the menu bar i kept getting an error.

    private void saveClick(object sender, EventArgs e)
            {
                detailAdapter.Update(detailTable);
            }

    this is the error

    An unhandled exception of type 'System.NullReferenceException' occurred in Client Control.exe
    Additional information: Object reference not set to an instance of an object.


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Monday, July 22, 2013 1:50 PM
  • Hi,

    did you declare the detailAdapter and detailTable as instance variables of the Customer_Details form? Both should have the value from the Fill method assigned (and not null).

    Regards, Elmar

    Monday, July 22, 2013 3:05 PM
  • i've declared both of them in the form and i think they were assigned in the fill block but maybe i should use

    this.

    when assigning the values to detailTable and detailAdapter


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Tuesday, July 23, 2013 9:07 AM
  • Hi,

    you're right, and my initial code had a flaw:

    // wrong
    var detailAdapter = new OleDbDataAdapter(query, connection);

    must be:

    // right
    detailAdapter = new OleDbDataAdapter(query, connection);
    (an additional this. is optional in this case).

    Regards, Elmar

    Tuesday, July 23, 2013 12:43 PM
  • implemented the change, and the error has stopped appearing but it still doesn't save the changes to the database. this is my save code in case there is something wrong with it

     private void saveClick(object sender, EventArgs e)
            {
                detailAdapter.Update(detailTable);
            }
    

    thank you so much for your help so far. i think the solution is in sight now.


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Tuesday, July 23, 2013 1:27 PM
  • Hi,

    as I don't know anything about the rest,  please check if there are any changes in the DataTable:

    private void saveClick(object sender, EventArgs e)
    {
      // Debug: Display the modified rows
      foreach (DataRow row in detailTable.GetChanges().Rows)
          Console.WriteLine("{0} => {1}", row[0], row.RowState);
    
       detailAdapter.Update(detailTable);
    }

    If the foreach gives no output, there is nothing todo.

    Regards, Elmar


    • Edited by Elmar Boye Tuesday, July 23, 2013 2:59 PM code format
    Tuesday, July 23, 2013 2:52 PM
  • it gives this error

    An unhandled exception of type 'System.NullReferenceException' occurred in Client Control.exe
    Additional information: Object reference not set to an instance of an object.

    this is the full code

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.OleDb;
    
    namespace Client_Control
    {
        public partial class Customer_Details : Form
        {
            Image image;
            OleDbDataAdapter detailAdapter;
            OleDbCommandBuilder detailbuilder;
            DataTable detailTable;
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + "\\MainClientDataFile.mdb;Persist Security Info=False");
            
            public Customer_Details(string tabName)
            {
                InitializeComponent();
                // StartupPath is safer the CurrentDirectory (can change)
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source="
                    + System.IO.Path.Combine(Application.StartupPath, "MainClientDataFile.mdb");
                var connection = new OleDbConnection(connectionString);
    
                // the query must include primary key column(s)
                string query = string.Format("SELECT ContactName , ContactNumber, Device, Manufacturer, Model,"
                        + " Type, PartNo, SerialNo" + " FROM [{0}];", tabName);
    
                detailAdapter = new OleDbDataAdapter(query, connection);
                detailAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                detailAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
    
                detailbuilder = new OleDbCommandBuilder(detailAdapter);
    
                this.detailTable = new DataTable(tabName);
                if (detailAdapter != null)
                {
                    detailAdapter.Fill(detailTable);
                }
                dataGridView1.DataSource = detailTable;
                conn.Close();
            }
    
            private void editClick(object sender, EventArgs e)
            {
                dataGridView1.ReadOnly = false;
            }
    
            private void saveClick(object sender, EventArgs e)
            {
                // Debug: Display the modified rows
                foreach (DataRow row in detailTable.GetChanges().Rows)
                    Console.WriteLine("{0} => {1}", row[0], row.RowState);
    
                detailAdapter.Update(detailTable);
            }
    
            private void addClick(object sender, EventArgs e)
            {
                detailTable.Rows.Add();
                dataGridView1.DataSource = detailTable;
            }
    
            private void removeClick(object sender, EventArgs e)
            {
                try
                {
                    dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
                }
                catch (ArgumentOutOfRangeException)
                {
                    MessageBox.Show("  Click the arrow beside the row you want to delete to highlight it, then click Edit --> Remove Row to delete a row  ");
                }
            }
                         
            private void Customer_Details_Load(object sender, EventArgs e)
            {
                image = Image.FromFile(@"C:\Users\BolorunduroWB\Documents\Visual Studio 2012\Projects\Client Control\Client Control\bin\Debug\logo.png");
            }
    
            private void Paint(object sender, PaintEventArgs e)
            {
                e.Graphics.DrawImageUnscaled(image, new Point(10, 30));
            }
        }
    }
    


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Tuesday, July 23, 2013 4:33 PM
  • Hi,

    there some problems how you are adding and deleting rows.
    The RemoveAt will never work, as it deletes the row from the DataTable - correct would DataRow.Delete() - as it marks the rows as delete but keeps it available for the DataAdapter.

    To avoid that problems, it is safer to use a BindingSource and use that to modify the underlying datasource.

    As that requires some changes, I added a comment were a change is necessary:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.OleDb;
    
    namespace Client_Control
    {
        public partial class Customer_Details : Form
        {
            Image image;
            OleDbDataAdapter detailAdapter;
            OleDbCommandBuilder detailbuilder;
            DataTable detailTable;
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + "\\MainClientDataFile.mdb;Persist Security Info=False");
            
    // add
            BindingSource detailBindingSource;
            
            public Customer_Details(string tabName)
            {
                InitializeComponent();
                // StartupPath is safer the CurrentDirectory (can change)
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source="
                    + System.IO.Path.Combine(Application.StartupPath, "MainClientDataFile.mdb");
                var connection = new OleDbConnection(connectionString);
    
                // the query must include primary key column(s)
                string query = string.Format("SELECT ContactName , ContactNumber, Device, Manufacturer, Model,"
                        + " Type, PartNo, SerialNo" + " FROM [{0}];", tabName);
    
                detailAdapter = new OleDbDataAdapter(query, connection);
                detailAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                detailAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
    
                detailbuilder = new OleDbCommandBuilder(detailAdapter);
    
                this.detailTable = new DataTable(tabName);
                if (detailAdapter != null)
                {
                    detailAdapter.Fill(detailTable);
                }
                
    // Add
                this.detailBindingSource = new BindingSource(detailTable, "");
    // Modify
                dataGridView1.DataSource = this.detailBindingSource;
                conn.Close();
            }
    
            private void editClick(object sender, EventArgs e)
            {
                dataGridView1.ReadOnly = false;
            }
    
            private void saveClick(object sender, EventArgs e)
            {
    // Add        
                this.detailBindingSource.EndEdit();    // ends editing
                
                // Debug: Display the modified rows
    // Modified (avoid NullReference if no changes)
                var table = detailTable.GetChanges();
                if (table != null)
                {
                    foreach (DataRow row in table.Rows)
                        Console.WriteLine("{0} => {1}", row[0], row.RowState);
                }
                detailAdapter.Update(detailTable);
            }
    
            private void addClick(object sender, EventArgs e)
            {
    // Remove        
                // dataGridView1.DataSource = detailTable;
                // detailTable.Rows.Add();
    // Add            
                this.detailBindingSource.AddNew();    // Adds a new row
            }
    
            private void removeClick(object sender, EventArgs e)
            {
    // Add
                if (this.detailBindingSource.Position >= 0)
                    this.detailBindingSource.RemoveCurrent(); // deletes current row
    /* Remove
                try
                {
                    dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
                }
                catch (ArgumentOutOfRangeException)
                {
                    MessageBox.Show("  Click the arrow beside the row you want to delete to highlight it, then click Edit --> Remove Row to delete a row  ");
                }
    */
            }
                         
            private void Customer_Details_Load(object sender, EventArgs e)
            {
                image = Image.FromFile(@"C:\Users\BolorunduroWB\Documents\Visual Studio 2012\Projects\Client Control\Client Control\bin\Debug\logo.png");
            }
    
            private void Paint(object sender, PaintEventArgs e)
            {
                e.Graphics.DrawImageUnscaled(image, new Point(10, 30));
            }
        }
    }
    Regards, Elmar

    • Edited by Elmar Boye Tuesday, July 23, 2013 6:11 PM code fix
    Tuesday, July 23, 2013 5:01 PM
  • thank you but i keep getting this error and i dont know what modifications to make

    >C:\Users\BolorunduroWB\Documents\Visual Studio 2012\Projects\Client Control\Client Control\Customer Details.cs(50,40,50,70): error CS1502: The best overloaded method match for 'System.Windows.Forms.BindingSource.BindingSource(System.ComponentModel.IContainer)' has some invalid arguments
    1>C:\Users\BolorunduroWB\Documents\Visual Studio 2012\Projects\Client Control\Client Control\Customer Details.cs(50,58,50,69): error CS1503: Argument 1: cannot convert from 'System.Data.DataTable' to 'System.ComponentModel.IContainer'


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Tuesday, July 23, 2013 5:46 PM
  • Hi,

    seems, the weather is to warm here in Germany for me ;(

    I left out a "":

     this.detailBindingSource = new BindingSource(detailTable, "");
    
    Regards, Elmar
    Tuesday, July 23, 2013 6:13 PM
  • thank you for the correction but the database isn't still updating even though it isn't giving any exceptions

    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Wednesday, July 24, 2013 8:55 AM
  • Hi,

    are there some changes and output from the debug code I provided for saveClick?

    Regards, Elmar

    Wednesday, July 24, 2013 10:04 AM
  • yes, i added some data in the datagridview when running and clicked the save buttong then closed the window and reopened it the changes earlier made didn't reflect. am i answering your question?

    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Wednesday, July 24, 2013 2:50 PM
  • Hi,,

    did the loop gave some output:

           // Debug: Display the modified rows
    // Modified (avoid NullReference if no changes)
          var table = detailTable.GetChanges();
          if (table != null)
          {
              foreach (DataRow row in table.Rows)
                 Console.WriteLine("{0} => {1}", row[0], row.RowState);
          }

    If in doubt: Place a breakpoint, and look if the foreach is executed.

    Regards, Elmar

    Wednesday, July 24, 2013 3:20 PM
  • it gives this error now

    An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
    Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

    this is on the update line

    detailAdapter.Update(detailTable);


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be


    • Edited by BolorunduroWB Wednesday, July 24, 2013 7:21 PM more details
    Wednesday, July 24, 2013 7:20 PM
  • Hi,

    are you remembering my other answer:

    The table must have a primary key to be updatable (for example CustomerName). See Fundamental Microsoft Jet SQL for Access 2000  for syntax examples.

    If the table has no primary (or unique) key the command builder cannot generate the commands, see Generating Commands with CommandBuilders

    Regards, Elmar

    Wednesday, July 24, 2013 9:38 PM
  • thanks. will work on it

    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Wednesday, July 24, 2013 10:08 PM
  • PLEASE I WANT TO ADD A COLUMN WHICH WILL AUTO INCREMENT AND BE MY PRIMARY KEY I TRIED THIS AND IT KEEPS GIVING SQL SYNTAX ERROR
    "CREATE TABLE [" + customerNameBox.Text + "] ( ColNo INT(4) AUTO_INCREMENT PRIMARY KEY NOT NULL , CustomerName CHAR(30) , MaintenanceCategory CHAR(30) , DateCommencing VARCHAR(20) , DateExpiring VARCHAR(20) , VisitFreq CHAR(10) , ContactName VARCHAR(40) , CustomerNumber VARCHAR(10) , ContactMail VARCHAR(30) , ContactNumber VARCHAR(14) , Device CHAR(20) , Manufacturer VARCHAR(17) , Model VARCHAR(20) )"

    CODE

    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Thursday, July 25, 2013 11:01 AM
  • Hi,

    for increment column the keyword must be AUTOINCREMENT or IDENTITY or COUNTER, see Intermediate Microsoft Jet SQL for Access 2000.

                string tableName = "A Table Name";  // just for testing: a name with space in it
                string createTableSQL = "CREATE TABLE [" + tableName +  "] ("
                    + "ColNo INTEGER NOT NULL IDENTITY PRIMARY KEY,"
                    + "CustomerName VARCHAR(30), "
                    + "MaintenanceCategory VARCHAR(30), "
                    + "DateCommencing VARCHAR(20), "
                    + "DateExpiring VARCHAR(20), "
                    + "VisitFreq VARCHAR(10), "
                    + "ContactName VARCHAR(40), "
                    + "CustomerNumber VARCHAR(10), "
                    + "ContactMail VARCHAR(30), " 
                    + "ContactNumber VARCHAR(14), "
                    + "Device VARCHAR(20), "
                    + "Manufacturer VARCHAR(17), "
                    + "Model VARCHAR(20));";

    I aligned the text datatypes too, as Jet treats all as a TEXT / VARCHAR data type but other as SQL Server does not.

    Regards, Elmar


    • Edited by Elmar Boye Thursday, July 25, 2013 12:21 PM typo
    Thursday, July 25, 2013 12:21 PM
  • thank you, following your advice, i have added a column called ColNo which is auto-incrementing and is not null which i've also used as primary key. how do i go about updating now?

    i tried updating and got this error

    An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
    Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Thursday, July 25, 2013 12:57 PM
  • Hi,

    please check, that you use the same database and table name. To avoid any unintentional errors, I suggest that you start with fixed a table name.

    To verify that the key is recognized: After the initial Fill is executed - do it even for a new created table - the detailTable PrimaryKey property should point at ColNo.

    Regards, Elmar


    • Edited by Elmar Boye Thursday, July 25, 2013 1:12 PM typo
    Thursday, July 25, 2013 1:11 PM
  • i did ask you advised with the following code

                detailTable.PrimaryKey = new DataColumn[] {detailTable.Columns["ColNo"]};


    just after

    if (detailAdapter != null)
                {
                    detailAdapter.Fill(detailTable);
                }

    and it still gives this error

    An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
    Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.


    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Thursday, July 25, 2013 1:48 PM
  • I really should let Elmar reply to this, since he's been doing such a good job helping you. But, since I'm already here, I'll try to help also.

    Your detailTable should already have that PrimaryKey column set before you query your database, not after. And, just as the error message indicates, you must have that column ColNo as part of your SELECT, which you have not done.


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, July 25, 2013 3:46 PM
  • thank you for also helping out, took your advice and selected colno from the database and tried to set the datatable primary key before querying the database but threw an exception

    An unhandled exception of type 'System.NullReferenceException' occurred in Client Control.exe
    Additional information: Object reference not set to an instance of an object.

     so i modified it to this state but it still throws an exception

    An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
    Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.OleDb;
    
    namespace Client_Control
    {
        public partial class Customer_Details : Form
        {
            Image image;
            OleDbDataAdapter detailAdapter;
            OleDbCommandBuilder detailbuilder;
            DataTable detailTable;
            static string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source="
                + System.IO.Path.Combine(Application.StartupPath, "ClientDataFile.accdb");
            OleDbConnection connection = new OleDbConnection(connectionString);
    
            // add
            BindingSource detailBindingSource;
    
            public Customer_Details(string tabName)
            {
                InitializeComponent();
                // the query must include primary key column(s)
                string query = string.Format("SELECT ColNo, ContactName , ContactNumber, Device, Manufacturer, Model,"
                        + " Type, PartNo, SerialNo, VisitFreq" + " FROM [{0}];", tabName);
                detailAdapter = new OleDbDataAdapter(query, connection);
                detailAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                detailAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
                detailbuilder = new OleDbCommandBuilder(detailAdapter);
    
                this.detailTable = new DataTable(tabName);
                if (detailAdapter != null)
                {
                    detailAdapter.Fill(detailTable);
                }
    
                detailTable.PrimaryKey = new DataColumn[] { detailTable.Columns["ColNo"] };
                // Add
                this.detailBindingSource = new BindingSource(detailTable,"");
                // Modify
                dataGridView1.DataSource = this.detailBindingSource;
                connection.Close();
            }
    
            private void editClick(object sender, EventArgs e)
            {
                dataGridView1.ReadOnly = false;
            }
    
            private void saveClick(object sender, EventArgs e)
            {
                // Add        
                this.detailBindingSource.EndEdit();    // ends editing
    
                // Debug: Display the modified rows
                // Modified (avoid NullReference if no changes)
                var table = detailTable.GetChanges();
                if (table != null)
                {
                    foreach (DataRow row in table.Rows)
                        Console.WriteLine("{0} => {1}", row[0], row.RowState);
                }
                detailAdapter.Update(detailTable);
            }
    
            private void addClick(object sender, EventArgs e)
            {         
                this.detailBindingSource.AddNew();    // Adds a new row
            }
    
            private void removeClick(object sender, EventArgs e)
            {
                if (this.detailBindingSource.Position >= 0)
                    this.detailBindingSource.RemoveCurrent(); // deletes current row            
            }
    
            private void Customer_Details_Load(object sender, EventArgs e)
            {
                image = Image.FromFile(@"C:\Users\BolorunduroWB\Documents\Visual Studio 2012\Projects\Client Control\Client Control\bin\Debug\logo.png");
            }
    
            private void Paint(object sender, PaintEventArgs e)
            {
                e.Graphics.DrawImageUnscaled(image, new Point(10, 30));
            }
        }
    }




    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Thursday, July 25, 2013 4:11 PM
  • Hi Bonnie,

    any support is appreciated ;)
    May be you can explain it better or just in a different wording.

    Regards, Elmar

    Thursday, July 25, 2013 4:43 PM
  • As I said, you need to have the ColNo added to the DataTable before you Fill it!

    this.detailTable = new DataTable(tabName);
    detailTable.PrimaryKey = new DataColumn[] { detailTable.Columns["ColNo"] };
    
    if (detailAdapter != null)
    {
        detailAdapter.Fill(detailTable);
    }
    


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, July 25, 2013 5:12 PM
  • changed it, no difference still giving me that error

    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Thursday, July 25, 2013 7:12 PM
  • changed it, no difference still giving me that error

    I assume the error you mean is this one:

    An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
    Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

    Are you sure that you defined the ColNo in the database like this, as Elmar suggested:

    ColNo INTEGER NOT NULL IDENTITY PRIMARY KEY


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, July 25, 2013 7:46 PM
  • yes, i wrote it exactly that way

    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be


    Thursday, July 25, 2013 11:59 PM
  • i have modified my code and it now updates but the add row and remove row dont work again
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.OleDb;
    
    namespace Client_Control
    {
        public partial class Customer_Details : Form
        {
            Image image;
            static string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\ClientDataFile.accdb";
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbDataAdapter myDA;
            DataSet myDataSet;
            BindingSource detailBindingSource;
            static string tableName;
    
            public Customer_Details(string tabName)
            {
                InitializeComponent();
                // the query must include primary key column(s)
                tableName = tabName;
                OleDbCommand Comm = new OleDbCommand("SELECT ColNo, ContactName , ContactNumber, Device, Manufacturer, Model, Type, PartNo, SerialNo, VisitFreq FROM "+ tabName + ";",connection);
                connection.Open();
                myDA = new OleDbDataAdapter(Comm);
                OleDbCommandBuilder builder = new OleDbCommandBuilder(myDA);
    
                myDataSet = new DataSet();
    
                myDA.Fill(myDataSet, tabName);
    
                dataGridView1.DataSource = myDataSet.Tables[tabName].DefaultView;
    
                connection.Close();
    
                connection = null;
            }
    
            private void editClick(object sender, EventArgs e)
            {
                dataGridView1.ReadOnly = false;
            }
    
            private void saveClick(object sender, EventArgs e)
            {
                this.Validate();
    
                this.myDA.Update(this.myDataSet.Tables[tableName]);
    
                this.myDataSet.AcceptChanges();
            }
    
            private void addClick(object sender, EventArgs e)
            {         
                this.detailBindingSource.AddNew();    // Adds a new row
            }
    
            private void removeClick(object sender, EventArgs e)
            {
                if (this.detailBindingSource.Position >= 0)
                    this.detailBindingSource.RemoveCurrent(); // deletes current row            
            }
    
            private void Customer_Details_Load(object sender, EventArgs e)
            {
                image = Image.FromFile(@"C:\Users\BolorunduroWB\Documents\Visual Studio 2012\Projects\Client Control\Client Control\bin\Debug\logo.png");
            }
    
            private void Paint(object sender, PaintEventArgs e)
            {
                e.Graphics.DrawImageUnscaled(image, new Point(10, 30));
            }
        }
    }



    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be

    Friday, July 26, 2013 5:51 PM
  • i have modified my code and it now updates but the add row and remove row dont work again
    What happens? Are you getting errors?

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, July 27, 2013 2:34 PM
  • Hi,

    just replacing code with some other snippet you found elsewhere doesn't help. You should try to understand what is happening behind the scenes.

    To make some progress,  I created a sample form. The form contains a DataGridView and a BindingNavigator plus two buttons for Load and Save:

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Windows.Forms;
    
    namespace ElmarBoye.Samples.Data
    {
        public partial class DetailForm : Form
        {
            // Default table name (to simplify testing)
            const string DefaultTableName = "Customer Table";   
    
            OleDbDataAdapter detailAdapter;
            OleDbCommandBuilder detailbuilder;
            BindingSource detailBindingSource;
            DataTable detailTable;
    
            public DetailForm()
            {
                InitializeComponent();
                // Prepare the DataAdapter for the default (test) table name.
                PrepareAdapter(DefaultTableName);
            }
    
            /// <summary>
            /// Form OnLoad: Creates test table if needed, fills table and binds to DataGrid and BindingNavigator
            /// </summary>
            protected override void OnLoad(EventArgs e)
            {
                // Create Test Table
                this.CreateTable(DefaultTableName);
    
                // Fill the DataTable
                this.FillTable();
    
                // Create a BindingSource and assign it to a DataGridView and a BindingNavigator
                this.detailBindingSource = new BindingSource(detailTable, "");
                this.detailDataGridView.DataSource = this.detailBindingSource;
                this.detailBindingNavigator.BindingSource = this.detailBindingSource;
            }
    
            /// <summary>Load Button on a BindingNavigator</summary>
            private void loadToolStripButton_Click(object sender, EventArgs e)
            {
                var table = this.detailTable.GetChanges();
                if (table != null && table.Rows.Count > 0)
                {
                    if (MessageBox.Show(this, "Discard changes?", this.Text,
                            MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.Yes)
                        return;
                }
                FillTable();
            }
    
            /// <summary>Save Button on a BindingNavigator</summary>
            private void bindingNavigatorSave_Click(object sender, EventArgs e)
            {
                this.UpdateTable();
            }
    
            /// <summary>Clears and Fills the table.</summary>
            private void FillTable()
            {
                try
                {
                    this.detailTable.Clear();
                    int count = detailAdapter.Fill(this.detailTable);
                    System.Diagnostics.Debug.WriteLine("Filled {0} rows.", count);
                }
                catch (Exception ex)
                {
                    ShowException(ex);
                }
            }
    
            /// <summary>Updates the table.</summary>
            private void UpdateTable()
            {
                try
                {
                    this.Validate();
                    this.detailBindingSource.EndEdit();
                    int count = detailAdapter.Update(this.detailTable);
                    System.Diagnostics.Debug.WriteLine("Updated {0} rows.", count);
                }
                catch (Exception ex)
                {
                    ShowException(ex);
                }
            }
    
            /// <summary>ConnectionString, use Properties.Settings</summary>
            private string ConnectionString
            {
                get
                {
                    return @"Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source="
                      + @"C:\TEMP\Test.mdb;";
                    //  + System.IO.Path.Combine(Application.StartupPath, "MainClientDataFile.mdb"
                }
            }
    
            private void PrepareAdapter(string tableName)
            {
                var connection = new OleDbConnection(ConnectionString);
    
                // Prepare DataAdapter
                string query = string.Format("SELECT ColNo, ContactName , ContactNumber, Device, Manufacturer, Model,"
                        + " Type, PartNo, SerialNo" + " FROM [{0}];", tableName);
                this.detailAdapter = new OleDbDataAdapter(query, connection)
                {
                    MissingSchemaAction = MissingSchemaAction.AddWithKey,
                    MissingMappingAction = MissingMappingAction.Passthrough,
                };
    
                // use a CommandBuilder, QuotePrefix + Suffix required if other then regular identifiers 
                this.detailbuilder = new OleDbCommandBuilder(detailAdapter);
                this.detailbuilder.QuotePrefix = "[";
                this.detailbuilder.QuoteSuffix = "]";
    
                // Adding a row handler for IDENTITY Primary Key
                detailAdapter.RowUpdated += (object sender, OleDbRowUpdatedEventArgs e) =>
                {
                    if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
                    {
                        using (var command = new OleDbCommand("SELECT @@IDENTITY;", e.Command.Connection))
                        {
                            // or hardwired using ColNo 
                            var primarykey = e.Row.Table.PrimaryKey[0];
                            e.Row[primarykey] = command.ExecuteScalar();
                        }
                    }
                };
    
                // empty table for now
                this.detailTable = new DataTable(tableName);
            }
    
            /// <summary>Creates a Test Table and some sample data.</summary>
            /// <param name="tableName">the table name.</param>
            private void CreateTable(string tableName)
            {
                using (var connection = new OleDbConnection(ConnectionString))
                {
                    connection.Open();
    
                    // if table does not exists, create it
                    var tables = connection.GetSchema("Tables");
                    if (tables.Select(string.Format("TABLE_NAME='{0}'", tableName)).Length == 0)
                    {
                        string createTableSQL = string.Format("CREATE TABLE [{0}] ("
                          + "ColNo INTEGER IDENTITY NOT NULL PRIMARY KEY,"
                          + "CustomerName VARCHAR(30) NULL, "
                          + "MaintenanceCategory VARCHAR(30) NULL, "
                          + "DateCommencing VARCHAR(20) NULL, "
                          + "DateExpiring VARCHAR(20) NULL, "
                          + "VisitFreq VARCHAR(10) NULL, "
                          + "ContactName VARCHAR(40) NULL, "
                          + "CustomerNumber VARCHAR(10) NULL, "
                          + "ContactMail VARCHAR(30) NULL, "
                          + "ContactNumber VARCHAR(14) NULL, "
                          + "Device VARCHAR(20) NULL, "
                          + "Manufacturer VARCHAR(17) NULL, "
                          + "Model VARCHAR(20) NULL, "
                            // additional columns in query
                          + "Type VARCHAR(20) NULL, "
                          + "PartNo VARCHAR(20) NULL, "
                          + "SerialNo VARCHAR(20) NULL);",
                          tableName);
                        var createCommand = new OleDbCommand(createTableSQL, connection);
                        createCommand.ExecuteNonQuery();
                    }
    
                    // If no rows create some sample data
                    if ((int)(new OleDbCommand(string.Format("SELECT COUNT(*) FROM [{0}];", tableName), connection)
                        .ExecuteScalar()) == 0)
                    {
                        var insertCommand = new OleDbCommand(string.Format(
                            "INSERT INTO [" + tableName + "] (CustomerName, ContactName) VALUES(@CustomerName, @ContactName);"), connection);
                        insertCommand.Parameters.Add("@CustomerName", OleDbType.VarWChar, 30);
                        insertCommand.Parameters.Add("@ContactName", OleDbType.VarWChar, 30);
    
                        
                        for (int index = 0; index < 10; index++)
                        {
                            insertCommand.Parameters["@CustomerName"].Value = "Customer " + index.ToString();
                            insertCommand.Parameters["@ContactName"].Value = "Contact  " + index.ToString();
                            insertCommand.ExecuteNonQuery();
                        }
                    }
                }
            }
    
            /// <summary>MessageBox handler for exceptions.</summary>
            private void ShowException(Exception ex)
            {
                MessageBox.Show(this, ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
    

    To get a table and some test data I added a CreteTable method, that creates a table with the required columns. I had to add some additional columns to get the column the query is using. So it works also for an empty access database file.

    As the previous posts had no handling for the IDENTITY column, I added it to the PrepareAdapter method.

    Regards, Elmar

    Saturday, July 27, 2013 3:50 PM
  • thank you guys and lady so much, my problem has been solved

    If my Question or Comment Helped in anyway, markup as helpful or as answer as the case may be


    http://social.msdn.microsoft.com/Forums/en-US/26bf838b-10ee-41ff-97d7-7da55ec6188b/c-dataadapter-update-from-datagridview-throwing-errors-oledb
    • Edited by BolorunduroWB Saturday, July 27, 2013 4:27 PM more info
    • Marked as answer by BolorunduroWB Saturday, July 27, 2013 4:27 PM
    Saturday, July 27, 2013 4:25 PM