locked
How to Write from DataGridView to MS Access Table RRS feed

  • Question

  • I'm running the code below (I have the appropriate references, and Using statements, and stuff).  The code looks fine to me, and when I step through, I got right to the end with no errors, but the data from the DataGridView actually isn't written to the Access Table.  I'm not sure what's wrong here.  Can someone see what I can't see?

            private void button1_Click(object sender, EventArgs e)

             //remove the semicolon, and add brackets below after line
             {
             //create the connection string
                 string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Ryan\\Desktop\\Coding\\Microsoft Access\\Northwind_2012.mdb";

                 //create the database query
             string query = "SELECT * FROM [OrderDetailsTest]";

                 //create an OleDbDataAdapter to execute the query
             OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

                 //create a command builder
             OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

                 //create a DataTable to hold the query results
             DataTable dTable = new DataTable();

                 //fill the DataTable
             dAdapter.Fill(dTable);

                 //the DataGridView
             DataGridView dataGridView1 = new DataGridView();

                 //BindingSource to sync DataTable and DataGridView
             BindingSource bSource = new BindingSource();

                 //set the BindingSource DataSource
             bSource.DataSource = dTable;

                 //set the DataGridView DataSource
             dataGridView1.DataSource = bSource;

                 // An update function to get the changes back into the database.
             dAdapter.Update(dTable);
             }

                                                                                              

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, September 9, 2013 1:52 AM

Answers

  • I don't know.

    Well……in fact I think the codes are all done by you,why do you say "you don't know"?

    My solution and idea is:

    1) Please make sure that there's a dataGridView on the form.

    2) Then use OleDbDataApdater+OleDbCommandBuilder to generate automatically CRUD methods and Fill into a DataTable.

    3) Bind the dataTable to DataGridView, when you modify anything in the GridView, please directly call DataAdapter.Update(Your DataTable Instance)


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    • Marked as answer by Eason_H Monday, September 16, 2013 1:35 AM
    Thursday, September 12, 2013 10:01 AM

All replies

  • The codes look nice……Please make sure:

    1) No error or exceptions.

    2) You have modified something in the DataGridView.


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    Monday, September 9, 2013 1:55 AM
  • #1)  There are no errors or exceptions.

    #2)  I did modify something in the DataGridView this time (last time I did not make any changes).  Still, when I run the code, nothing gets written to the DB.

    Did you actually try the code that I posted ThankfulHeart?  If so, what results did you get?


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, September 10, 2013 2:21 AM
  • #1)  There are no errors or exceptions.

    #2)  I did modify something in the DataGridView this time (last time I did not make any changes).  Still, when I run the code, nothing gets written to the DB.

    Did you actually try the code that I posted ThankfulHeart?  If so, what results did you get?


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    To "#2":

    Have you saved it by calling "sqlDataAdapter.Update(Your DataTable Instance)" after changing?


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    Tuesday, September 10, 2013 2:29 AM
  • Sorry, but what does this mean?

    Have you saved it by calling "sqlDataAdapter.Update(Your DataTable Instance)" after changing?

    I don't have any code like that in my entire project.  Here's all my code.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    using System.Globalization;
    using System.Configuration;
    using System.Data.OleDb;

    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

             public void button1_Click(object sender, EventArgs e)
             {
             string delimiter = ",";
             string tablename = "medTable";
             DataSet dataset = new DataSet();
             OpenFileDialog openFileDialog1 = new OpenFileDialog();
             openFileDialog1.Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*";
             openFileDialog1.FilterIndex = 1;
             if (openFileDialog1.ShowDialog() == DialogResult.OK)
             {
             if (MessageBox.Show("Are you sure you want to import the data from \n " + openFileDialog1.FileName + "?", "Are you sure?", MessageBoxButtons.YesNo) == DialogResult.Yes)
             {
             filename = openFileDialog1.FileName;
             StreamReader sr = new StreamReader(filename);
             string csv = File.ReadAllText(openFileDialog1.FileName);
             dataset.Tables.Add(tablename);
             dataset.Tables[tablename].Columns.Add("Order ID");
             dataset.Tables[tablename].Columns.Add("Product");
             dataset.Tables[tablename].Columns.Add("Unit Price");
             dataset.Tables[tablename].Columns.Add("Quantity");
             dataset.Tables[tablename].Columns.Add("Discount");

             string allData = sr.ReadToEnd();
             string[] rows = allData.Split("\r".ToCharArray());

             foreach (string r in rows)
             {
             string[] items = r.Split(delimiter.ToCharArray());
             dataset.Tables[tablename].Rows.Add(items);
             }
             this.dataGridView1.DataSource = dataset.Tables[0].DefaultView;
             MessageBox.Show(filename + " was successfully imported. \n Please review all data before sending it to the database.", "Success!", MessageBoxButtons.OK);
             }
             else
             {
             this.Close();
             }
             }
             }

             public string filename { get; set; }


             private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
             {

             }

             private void Import_Load(object sender, EventArgs e)
             {

             }

             private void button4_Click(object sender, EventArgs e)
             {
             Application.Exit();

             }

             private void button2_Click(object sender, EventArgs e)
             {
             this.Close();
             }

             private void button3_Click(object sender, EventArgs e)

             //remove the semicolon, and add brackets below after line
             {
             //create the connection string
                 string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Ryan\\Desktop\\Coding\\Microsoft Access\\Northwind_2012.mdb";
             
                 //create the database query
             string query = "SELECT * FROM [OrderDetailsTest]";
             
                 //create an OleDbDataAdapter to execute the query
             OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
             
                 //create a command builder
             OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
             
                 //create a DataTable to hold the query results
             DataTable dTable = new DataTable();
             
                 //fill the DataTable
             dAdapter.Fill(dTable);
             
                 //the DataGridView
             DataGridView dataGridView1 = new DataGridView();
             
                 //BindingSource to sync DataTable and DataGridView
             BindingSource bSource = new BindingSource();
             
                 //set the BindingSource DataSource
             bSource.DataSource = dTable;
             
                 //set the DataGridView DataSource
             dataGridView1.DataSource = bSource;
             
                 // An update function to get the changes back into the database.
             dAdapter.Update(dTable);
             }
        }
     }

    I'm loading data from a CSV, into a DataGridView, and then sending the data from the DataGridView to the MS Access Table (which is setup and formatted appropriately).

    I don't know where the 'sqlDataAdapter.Update' comes into play.  If I need this in my code, what will it look like?

    Thanks!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, September 11, 2013 4:52 AM
  • There's a question in front of you:

      //the DataGridView
             DataGridView dataGridView1 = new DataGridView();
             
                 //BindingSource to sync DataTable and DataGridView
             BindingSource bSource = new BindingSource();
             
                 //set the BindingSource DataSource
             bSource.DataSource = dTable;
             
                 //set the DataGridView DataSource
             dataGridView1.DataSource = bSource;

    Your dataGridView1 is dynamically created, it hasn't been accessed directly through the UI, so how can you do modifictions with that?


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    Wednesday, September 11, 2013 5:11 AM
  • I don't know.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, September 12, 2013 2:40 AM
  • I don't know.

    Well……in fact I think the codes are all done by you,why do you say "you don't know"?

    My solution and idea is:

    1) Please make sure that there's a dataGridView on the form.

    2) Then use OleDbDataApdater+OleDbCommandBuilder to generate automatically CRUD methods and Fill into a DataTable.

    3) Bind the dataTable to DataGridView, when you modify anything in the GridView, please directly call DataAdapter.Update(Your DataTable Instance)


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    • Marked as answer by Eason_H Monday, September 16, 2013 1:35 AM
    Thursday, September 12, 2013 10:01 AM
  • Tried your solution, but added  following to fill and manage the data

      this.dataGridView1.DataSource = bSource;
      this.bindingNavigator1.BindingSource = bSource;

    After changing some data in the datagrid the changes are not updated in the MDM source table

    what am I doing wrong?
    Please help me out

    Tried several solutions, non working

    Saturday, December 5, 2020 2:43 PM