Can not add new row to MS-Access 2000 table from .NET C# Winforms

Question

I am trying to make a simple winform application in which i could read/update and insert data to MS Access db table

When i run the application it reads data from MS Access db, and I am able to add new data or edit existing data, but those changes are not sent back to DB.

Code in my Save button click event

    Validate();
    myBindingSource.EndEdit();

    //myTableAdapterManager.UpdateAll(myDataSet.myTable); //this line was in generated code
    myTableAdapter.Update(myDataSet.myTable); //replaced previous row with this one, but with no effect

When I press the "save" button

I do not receive any error message, in the DataGridView, new row contains ID with -1 value and new row is not added to database

What could be the problem? What am I missing?

When I opened mdb file from MS Access 2007 it is possible to add a new row to this table

This SO post seems to be about the same problem, but it does not help in my case

unable to add new row using datagridview using C# in Winforms

[EDIT]

I opened .xsd file and added Insert and Update Queries for myTable, but still that does not help - when i press Save button changes are not sent to the database

1) I have added Data source "Microsoft Access Databases file"(Provider=Microsoft.Jet.OLEDB.4.0) 2) in Form's load event i have myTableAdapter.Fill(myDataSet.myTable) — Prokurors
– Prokurors, Commented Sep 19, 2013 at 6:11
Are you sure that your dataset is updatable? You should check your connection's and dataset's parameters ... — Philippe Grondier
– Philippe Grondier, Commented Sep 19, 2013 at 6:27
I have 5 components below Winform - ..DataSet, ..BindingSource, ..TableAdapter, tableAdapterManager, ..BindingNavigator. This BindingSource component has parameter AllowNew==true. ..DataSet component has no parameters to make it Updatable or ReadOnly. Where can I find those parameters you ar talking about? — Prokurors
– Prokurors, Commented Sep 19, 2013 at 7:00
Did you define the query used in the TableAdapter or did you use the default (auto-generated) query? Also, does the underlying db table have a primary key? — tgolisch
– tgolisch, Commented Sep 19, 2013 at 12:06

Prokurors · Accepted Answer · 2013-09-25 14:26:35Z

I found the solution how to send data from Grid control to database, i hope it will help someone else as well (+added some usefull additional stuff)

Here is my code

//form level fields
        OleDbConnection conn = new OleDbConnection();
        OleDbDataAdapter adapter;// = new OleDbDataAdapter();
        DataTable table = new DataTable();
        BindingSource bSource = new BindingSource();

//choosing MS Access file 
        var ecgDbFile = new OpenFileDialog();
        ecgDbFile.InitialDirectory = "c:\\";
        ecgDbFile.Filter = @"MS Access files (*.mdb)|*.mdb";
        ecgDbFile.FilterIndex = 1;
        ecgDbFile.RestoreDirectory = true;

//creating connection
        conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ecgDbFile.FileName;
        conn.Open();

//A nice way how to get a list of Data base's tables
        var restrictions = new string[4];
        restrictions[3] = "Table";
        userTableList = conn.GetSchema("Tables", restrictions);
//then loop through and you can get table names => userTableList.Rows[i][2].ToString()

//reading the data (in the grid)
        adapter = new OleDbDataAdapter("Select * from "+TableName, conn);
        adapter.Fill(table);
        bSource.DataSource = table; //binding through bindingsource
        GridControl.DataSource = bSource; //actually it works fine if GridControl.DataSource is set to table directly as well...

//choose the appropriate trigger (some gridcontrol or button click event) to call for database updates
    private void GridControl_Leave(object sender, EventArgs e)
    {
        //because of this OleDbCommandBuilder TableAdapter knows how to insert/update database
        OleDbCommandBuilder command = new OleDbCommandBuilder(adapter);
        adapter.Update(table);
    }

Collectives™ on Stack Overflow

Can not add new row to MS-Access 2000 table from .NET C# Winforms

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related