Help with updating access database using Update(dataTable)

Question

I have looked all over the place but I cant seem to get this to work.

I am trying to update an Access database from a DataGridView. The loading of the database to the grid works fine. I used the instructions described in this site.

However, to update the database based on changes made to the DataGridView, I used the command dataAdapter.Update(datatable);, but depending on the placement (next to this code) the code runs but the database does not update. If I put it in a button it throws an exception "syntax error in insert into statement".

Other question: Should I close the connection variable after loading the DataGridView? If so, should I reopen it to perform the update and then reclose it? How does that work?.

Any help would be greatly appreciated. EDIT: Putted whole class as Tim asked.

 public partial class Pantalla_Proyecto : Form
{
    private Inicio Inicio;
    private List Logueado;
    private OleDbConnection conn;
    private OleDbDataAdapter Adaptador;
    private DataTable Tabla;
    private BindingSource Bsource;
    private OleDbCommandBuilder Builder;

    public Pantalla_Proyecto(Inicio Inicio, List Logueado)
    {
        this.Inicio =Inicio;
        this.Logueado = Logueado;
        InitializeComponent();
    }
    private void Pantalla_Proyecto_Load(object sender, EventArgs e)
    {
    }

     private void importarCDPToolStripMenuItem_Click(object sender, EventArgs e)
    {
        Importar_CDP Importar_CDP = new Importar_CDP();
        Importar_CDP.Show();
    }

    private void importarListasToolStripMenuItem_Click(object sender, EventArgs e)
    {
        WindowsFormsApplication1.Formularios.Lista_Lazos.Importar_Listas1 Importar_Listas = new WindowsFormsApplication1.Formularios.Lista_Lazos.Importar_Listas1();
        Importar_Listas.Show();
    }

    private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {

    }

    private void button1_Click(object sender, EventArgs e)
    {

    }

    private void flowLayoutPanel2_Paint(object sender, PaintEventArgs e)
    {

    }

    private void menuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
    {

    }

    private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string sql = "Select *  From ["+TABLE (THIS IS A STRING I GET FROM PREVIOUS FORM)+"]";
        conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=proyectos\" + Location(this is a string i get on the previous form) + ".mdb;User Id=admin;Password=;");
        conn.Open();

        //  Extraemos info de mi database y la meto en un datatable
        Adaptador = new OleDbDataAdapter(sql, conn);
        Builder = new OleDbCommandBuilder(Adaptador);
        Tabla = new DataTable();
        Adaptador.Fill(Tabla);

        // LLENO EL DATA GRID VIEW
        Bsource = new BindingSource();
        Bsource.DataSource = Tabla;
        dataGridView1.DataSource = Bsource;
        dataGridView1.Dock = DockStyle.Fill;
        Adaptador.Update(Tabla);//if i put it here nothing happens
        conn.Close();


    }

    private void dataGridView1_Validating(object sender, CancelEventArgs e)
    {

    }

    private void button1_Click_1(object sender, EventArgs e)
    {
        conn.Open();
        Adaptador.Update(Tabla);//IF i put it here i get an exception
        conn.Close();
    }

Tim · Accepted Answer

To elaborate, as per OP's request, on the using statement, let's take the comboBox1_SelectedIndexChanged method:

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    string sql = "Select *  From ["+TABLE (THIS IS A STRING I GET FROM PREVIOUS FORM)+"]";

    using (conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=proyectos\" + Location(this is a string i get on the previous form) + ".mdb;User Id=admin;Password=;"))
    {
        conn.Open();

        //  Extraemos info de mi database y la meto en un datatable
        Adaptador = new OleDbDataAdapter(sql, conn);
        //  Remove the OleDbCommandBuilder
        Tabla = new DataTable();
        Adaptador.Fill(Tabla);

        // LLENO EL DATA GRID VIEW
        Bsource = new BindingSource();
        Bsource.DataSource = Tabla;
        dataGridView1.DataSource = Bsource;
        dataGridView1.Dock = DockStyle.Fill;
        // ** NOTE:
        // At this point, there's nothing to update - all that
        // has happened is that you've bound the DataTable
        // to the DataGridView.
        Adaptador.Update(Tabla);//if i put it here nothing happens

    } // Your connection will be closed at this point when the using
      // statement goes out of scope.
}

UPDATE

MSDN says "When you create a new instance OleDbCommandBuilder, any existing OleDbCommandBuilder associated with this OleDbDataAdapter is released."

However, if you want to get away from the OleDbCommandBuilder completely, you can do so (I updated my code above to do just that). Since you believe you're having issues with special characters, it's probably worthwhile to do it this way.

Something like this should help - you'll have to modify the update command based on what your table columns are:

private void button1_Click_1(object sender, EventArgs e)
{

    conn.Open();        
    Adaptador.UpdateCommand = ""  // I.e., UPDATE [TABLE] SET....
    try
    {
        Adaptador.Update((DataTable)Bsource.DataSource);
    }
    catch (Exception ex)
    {
        // Do something with the exception
    }
}

This code is a slightly modified version of what MSDN has:

How to: Bind Data to the Windows Forms DataGridView Control

Note that they use a SqlDbCommandBuilder in the example, but the overall principle remains the same.

Based on David-W-Fenton's comment, you might want to do your connection and dataadapter initializations in the Form_Load event, and then close the connection on Form_Closing event.

Help with updating access database using Update(dataTable)

Answers (2)

Related Questions